Viewed 10K+ times! This question is You Asked Hi, Tom.I am trying to use Oracle 9i's ability to treat ASCII files as Oracle tables and have run into a few problems.First off , here is the script I've written that creates a directory called "external_tables_dir" and then reads an ASCII file called "clntques.txt" whose fields are pipe delimited:declare ddl varchar2(2000); cnt integer;begin ddl := 'create or replace directory external_tables_dir as ''/home/athena_d/ora_ext_tbl'''; execute immediate ddl; select count(*) into cnt from user_tables where table_name = 'EXTERNAL_CLNTQUES'; if ( cnt = 1 ) then ddl := 'drop table athena.external_clntques'; execute immediate ddl; end if; ddl := 'create table athena.external_clntques(CLIENT_ID number(10), NAME varchar2(60), SHORT_NAME varchar2(15), QTEXT varchar2(4000), QUESTION_CATEGORY varchar2(60), CLI_QUESTION_CODE varchar2(60), IS_ACTIVE_CHK varchar2(1), IS_DELETED_CHK varchar2(1), CREATED_ON date, UPDATED_ON date, CREATED_BY_ID number(10), UPDATED_BY_ID number(10), ALT_KEY varchar2(60))organization external(type oracle_loader default directory athena_external_tables_dir access parameters (fields terminated by '''' missing field values are null (client_id, name, short_name, qtext, question_category, cli_question_code, is_active_chk, is_deleted_chk, created_on, updated_on char date_format date mask "dd-mon-yyyy hh24:mi:ss", created_by_id, updated_by_id, alt_key) ) location (''clntques.txt''))parallel 2reject limit unlimited';execute immediate ddl;end;/And here are the first few rows from the ASCII file:CLIENT_IDNAMESHORT_NAMEQTEXTQUESTION_CATEGORYCLI_QUESTION_CODEIS_ACTIVE_CHKIS_DELETED_CHKCREATED_ONUPDATED_ONCREATED_BY_IDUPDATED_BY_IDALT_KEY6Would you be interested in speaking with a Ryder Vehicle Sales manager to discuss your current needs?YNRYDER*005276Name of person completing the survey if different than addressed.YNRYDER*000496How would you classify your company?YNRYDER*000506Supply Chain Services:YNRYDER*003726Were you contacted by Ryder Vehicle Sales?YNRYDER*005236Are customers requesting 'Just in Time Delivery'?YNRYDER*00007=============================================================Question 1: Running the above script produces no errors. However, if I then run SELECT * FROM EXTERNAL_CLNTQUES, Oracle creates a file EXTERNAL_CLNTQUES_5466.bad containing 6 records that were rejected from my ASCII file. Oracle also creates the file EXTERNAL_CLNTQUES_5466.log that contains these error messages, one for each of the 6 rejected records: KUP-04021: field formatting error for field QTEXT KUP-04026: field too long for datatype KUP-04101: record 54 rejected in file /home/athena_d/ora_ext_tbl/clntques.txt This log file also defines the QTEXT field as: QTEXT CHAR (255) Terminated by "" Trim whitespace same as SQL Loader And so, Oracle thinks that the QTEXT field in the ASCII file is a CHAR(255) column. However, as specified in my sqlplus script, the column name QTEXT is defined as VARCHAR2(4000). How can I get my sqlplus script to recognize QTEXT as VARCHAR2(4000)?Question 2:The first row in ASCII file clntques.txt is a header record, simply giving the names of the fields making up the file. After running my script, somehow Oracle "knows" that this very first row is really not part of the file's data. I know this is true because doing a SELECT * FROM EXTERNAL_CLNTQUES starts showing data from the second row in the file. How does Oracle know that this file's first row is, in fact, just a header record? There is no "SKIP=1" clause that I can put into my sqlplus script, is there?Question 3:Do you know how I can specify "log" and "bad" directories inmy sqlplus script? I have tried to use the clauses: badfile external_tables_dir: ''bad_clntques'' logfile external_tables_dir: ''log_clntques''in the script but Oracle then complains with "Invalid statement".Question 4:Whenever I run any type of SELECT query against virtual table EXTERNAL_CLNTQUES, Oracle creates at least 2 log files in the defined directory. I've noticed that Oracle sometimes even creates 3 files: 2 log files and a "bad" file if errors occurred. Why is Oracle doing this? In a real life application, how can I tell if a query running against such a virtual table caused errors? In order to know that my query ran OK, it seems that I have to constantly look for any new "bad" files generated in the defined directory as opposed to simply getting an Oracle error either displayed on a terminal (as in a SQLPLUS session) or an error that I can "catch" in an exception handler (as in a plsql package).I would be interested to know your answers/comments to these questions.Thanks very much.Elie and Tom said...parallel is meaningful ONLY with fixed length (positional) files. We need to know that each record is say "100 bytes" in order to parallelize on it. Thats just an FYI...Also, don't you find it tedious to run ddl in plsql? Just use sqlplus (thats what I'm going to do)I see:create or replace directory external_tables_dir as but also:(type oracle_loader default directory athena_external_tables_dirso, perhaps (99.999% likely) you are looking at one file - but loading a totally different one? I say that because I ran this pretty much unchanged (except no plsql -- just a script)ops$tkyte@ORA920.US.ORACLE.COM> @testops$tkyte@ORA920.US.ORACLE.COM> drop table external_clntques;Table dropped.ops$tkyte@ORA920.US.ORACLE.COM>ops$tkyte@ORA920.US.ORACLE.COM> create or replace directory external_tables_dir as 2 '/tmp/' 3 /Directory created.ops$tkyte@ORA920.US.ORACLE.COM>ops$tkyte@ORA920.US.ORACLE.COM> create table external_clntques 2 (CLIENT_ID number(10), 3 NAME varchar2(60), 4 SHORT_NAME varchar2(15), 5 QTEXT varchar2(4000), 6 QUESTION_CATEGORY varchar2(60), 7 CLI_QUESTION_CODE varchar2(60), 8 IS_ACTIVE_CHK varchar2(1), 9 IS_DELETED_CHK varchar2(1), 10 CREATED_ON date, 11 UPDATED_ON date, 12 CREATED_BY_ID number(10), 13 UPDATED_BY_ID number(10), 14 ALT_KEY varchar2(60) 15 ) 16 organization external 17 (type oracle_loader 18 default directory external_tables_dir 19 access parameters 20 (fields terminated by '' 21 missing field values are null 22 (client_id, 23 name, 24 short_name, 25 qtext, 26 question_category, 27 cli_question_code, 28 is_active_chk, 29 is_deleted_chk, 30 created_on, 31 updated_on char date_format date mask "dd-mon-yyyy hh24:mi:ss", 32 created_by_id, 33 updated_by_id, 34 alt_key) 35 ) 36 location ('clntques.txt') 37 ) 38 parallel 2 39 reject limit unlimited;Table created.ops$tkyte@ORA920.US.ORACLE.COM>ops$tkyte@ORA920.US.ORACLE.COM> exec print_table( 'select * from external_clntques' )CLIENT_ID : 6NAME :SHORT_NAME :QTEXT : Would you be interested in speaking with a Ryder Vehicle Sales manager to discuss your currentneeds?QUESTION_CATEGORY :CLI_QUESTION_CODE :IS_ACTIVE_CHK : YIS_DELETED_CHK : NCREATED_ON :UPDATED_ON :CREATED_BY_ID :UPDATED_BY_ID :ALT_KEY : RYDER*00527-----------------CLIENT_ID : 6NAME :SHORT_NAME :QTEXT : Name of person completing the survey if different than addressed.QUESTION_CATEGORY :CLI_QUESTION_CODE :IS_ACTIVE_CHK : YIS_DELETED_CHK : NCREATED_ON :UPDATED_ON :CREATED_BY_ID :UPDATED_BY_ID :ALT_KEY : RYDER*00049-----------------CLIENT_ID : 6NAME :SHORT_NAME :QTEXT : How would you classify your company?QUESTION_CATEGORY :CLI_QUESTION_CODE :IS_ACTIVE_CHK : YIS_DELETED_CHK : NCREATED_ON :UPDATED_ON :CREATED_BY_ID :UPDATED_BY_ID :ALT_KEY : RYDER*00050-----------------CLIENT_ID : 6NAME :SHORT_NAME :QTEXT : Supply Chain Services:QUESTION_CATEGORY :CLI_QUESTION_CODE :IS_ACTIVE_CHK : YIS_DELETED_CHK : NCREATED_ON :UPDATED_ON :CREATED_BY_ID :UPDATED_BY_ID :ALT_KEY : RYDER*00372-----------------CLIENT_ID : 6NAME :SHORT_NAME :QTEXT : Were you contacted by Ryder Vehicle Sales?QUESTION_CATEGORY :CLI_QUESTION_CODE :IS_ACTIVE_CHK : YIS_DELETED_CHK : NCREATED_ON :UPDATED_ON :CREATED_BY_ID :UPDATED_BY_ID :ALT_KEY : RYDER*00523-----------------CLIENT_ID : 6NAME :SHORT_NAME :QTEXT : Are customers requesting 'Just in Time Delivery'?QUESTION_CATEGORY :CLI_QUESTION_CODE :IS_ACTIVE_CHK : YIS_DELETED_CHK : NCREATED_ON :UPDATED_ON :CREATED_BY_ID :UPDATED_BY_ID :ALT_KEY : RYDER*00007-----------------PL/SQL procedure successfully completed.and it worked immediately? As for the QTEXT question -- sure, your database column definition -- what the input data will be mapped to -- is a varchar2(4000) but the input type is defaulted -- you didn't specify an input type for QTEXT. Hence, it defaults to char(255). do this:... short_name, qtext CHAR(4000), question_category, cli_question_code,.....and your log will have: SHORT_NAME CHAR (255) Terminated by "" Trim whitespace same as SQL Loader QTEXT CHAR (4000) Terminated by "" Trim whitespace same as SQL Loader QUESTION_CATEGORY CHAR (255) Terminated by ""instead....2) I'm thoroughly confused now. Q1 was all about "help, I get nothing from my external table" but this one says "ok, when I select from it -- I only get the valid data -- it seems to skip the header?????So - can you or can you not select from this external table? Anyway - assuming you can, you'll find your header in the BAD file. Why? Well:create table athena.external_clntques(CLIENT_ID number(10),....the string client_id is not convertable to a number(10) so that record is quite simply rejected. You can skip this record via: default directory external_tables_dir access parameters ( records delimited by newline skip 1 fields terminated by '' missing field values are null(documented in _01/server.920/a96652/ch12.htm#1009462 )....q3) access parameters ( records delimited by newline badfile external_tables_dir:foobar skip 1will create /tmp/foobar.bad for bad records (given my example)... Since I don't know where you tried to stick this before, I cannot comment on why it did not work.q4) Using your original create that worked (no skip, no badfile) I do not get the same results you do:ops$ora920@ORA920.US.ORACLE.COM> !rm -f /tmp/*.bad /tmp/*.logops$ora920@ORA920.US.ORACLE.COM> !ls -l /tmp/*.bad /tmp/*.logls: /tmp/*.bad: No such file or directoryls: /tmp/*.log: No such file or directoryops$ora920@ORA920.US.ORACLE.COM> select count(*) from external_clntques; COUNT(*)---------- 6ops$ora920@ORA920.US.ORACLE.COM> !ls -l /tmp/*.bad /tmp/*.log-rw-r--r-- 1 ora920 ora920 155 Dec 8 10:50 /tmp/EXTERNAL_CLNTQUES_23676.bad-rw-r--r-- 1 ora920 ora920 1859 Dec 8 10:50 /tmp/EXTERNAL_CLNTQUES_23676.logops$ora920@ORA920.US.ORACLE.COM>So, I cannot comment on your "two" log files since I don't see two and you don't show us what they were....The very existence of a BAD file would indicate errors and you could do the following. Here I did use a BADFILE filename (foobar.bad) and created a "bad table"ops$ora920@ORA920.US.ORACLE.COM> create table external_clntques_bad 2 ( text1 varchar2(4000) , 3 text2 varchar2(4000) , 4 text3 varchar2(4000) 5 ) 6 organization external 7 (type oracle_loader 8 default directory external_tables_dir 9 access parameters 10 ( 11 records delimited by newline 12 fields 13 missing field values are null 14 ( text1 position(1:4000), 15 text2 position(4001:8000), 16 text3 position(8001:12000) 17 ) 18 ) 19 location ('foobar.bad') 20 ) 21 /Table created.ops$ora920@ORA920.US.ORACLE.COM>ops$ora920@ORA920.US.ORACLE.COM>ops$ora920@ORA920.US.ORACLE.COM> select count(*) from external_clntques_bad; COUNT(*)---------- 1It lets you see how many bad records there are (and even view if if you want)ops$ora920@ORA920.US.ORACLE.COM> select * from external_clntques_bad;TEXT1-----------------------------------------------------------------------------------------------------------------------------------TEXT2-----------------------------------------------------------------------------------------------------------------------------------TEXT3-----------------------------------------------------------------------------------------------------------------------------------CLIENT_IDNAMESHORT_NAMEQTEXTQUESTION_CATEGORYCLI_QUESTION_CODEIS_ACTIVE_CHKIS_DELETED_CHKCREATED_ONUPDATED_ONCREATED_BY_IDUPDATED_BY_IDALT_KEYThe entire concept of the "bad" file is to IGNORE records that would cause an error. If you got an error (as you seem to imply you want by the comment "it seems that I have to constantly look for any new "bad" files generated in the defined directory as opposed to simply getting an Oracle error either displayed on a terminal"...You surpressed the errors with reject limit unlimited. So yes, you would have to "constantly" look for bad files in your case. You can have an exception raised -- that is easy, just set your reject limit down. Rating (203 ratings)Is this answer out of date? If it is, please let us know via a Comment Comments Comment Questions About External FilesElie Grunhaus, December 08, 2002 - 4:17 pm UTC
Brilliant Database V9.42 Ultimate Crack Pipe
2ff7e9595c
コメント