Toad World® Forums

External tables parameters parsing


#1

Hi!

Parsing of external tables parameters in edit table window not always is proper.

Try following statement:

create or replace directory TEST_A_DIR as ‘D:’;

CREATE TABLE test_external
(col1 varchar2(10))
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY TEST_A_DIR
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE CHARACTERSET EE8MSWIN1250
BADFILE d:‘TEST.bad’ NODISCARDFILE
LOGFILE d:‘TEST.log’ READSIZE 10000000
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘"’
LRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL
FIELDS)
LOCATION (
TEST_A_DIR:‘TEST’
)
)
REJECT LIMIT UNLIMITED;

then edit table and look at the badfile editbox, there is :
d:‘TEST.bad’ NODISCARDFILE
but should be d:‘TEST.bad’
It looks like that parser dont recognise NODISCARDFILE option?

Regards Piter


#2

Hi Piter,

Thanks for picking up this bug. It’s a bug in Table Editor and has nothing to do with parsing. Data displayed in the Table Editor is extracted from the system views like sys.all_tables and not by parsing the DDL. It’s too late to fix the problem for 5.5, we’ll consider it for the next release. So far, the “workaround” is to do nothing - the option works correctly (you can Extract DDL and see that it’s correct), it’s just not nice to represent it this way.

Regards,
Roman


#3

d:‘TEST.bad’ DISCARDFILE

and now definition is wrong!

So this error is not problem to me, but sometimes users could brake table using sqlnav.

This shows prameters for this test external table:

DECLARE
RESULT VARCHAR2(32000);
BEGIN

SELECT ACCESS_PARAMETERS INTO RESULT
FROM all_external_tables WHERE TABLE_NAME=‘TEST_EXTERNAL’ AND ROWNUM=1;

dbms_output.put_line(RESULT);
END;

Regards Piter

Hi Roman, I was talkin not about parsing ddls ;), but about Access parameters for external tables which are stored in ora clob datatype, as couple of strings, so i think you must parse it for each parameter. If i’m wrong please correct me. For te second, of course ddls are generated correctly but users could edit “bad” file and then post changes, and then definition of table could be wrong, for example name of bad file is:
d:‘TEST.bad’ NODISCARDFILE
user changes this :


#4

Hi Piter,

Thanks for correcting me - I didn’t write this part of SQL Navigator and didn’t know how external tables parameters are represented. As to your example, I didn’t check it but I think Oracle won’t allow you to create an invalid definition anyway. I’ll investigate this problem a little bit more; I think it should be attended in the next release. You are right, the parameters should be parsed and presented to the user in a more usable form.

Regards,
Roman


#5

Hi!
Some words about oracle external tables, unfortunetly oracle don’t check parameters on create and alter, parameters are accesed when users wants select this table, in this situation you can type any directory you want, and any file name and so on. Assuming : every words which are inside ACCESS PARAMETERS parenthesis , are not checked!

Regards Piter