Link to home
Start Free TrialLog in
Avatar of Cosine_Consultants
Cosine_Consultants

asked on

External Table Problem - Oracle 9i

I'm creating successfully this external table but when i'm trying to select it i get the error below.
CREATE TABLE temp2
(
  temp1  NUMBER,
  TEMP3 NUMBER
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DEST_DIR
     ACCESS PARAMETERS
       (RECORDS DELIMITED BY NEWLINE
          FIELDS DELIMITED BY "," )
     LOCATION (DEST_DIR:'TEMP1.DAT')
  )
REJECT LIMIT 0
NOLOGGING
NOCACHE
NOPARALLEL
/


ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "delimited": expecting one of: "enclosed, exit, (, ltrim, lrtrim, ldrtrim, missing, notrim, optionally, rtrim, reject, terminated"
KUP-01007: at line 2 column 13
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1


Avatar of geotiger
geotiger


Could you change the "REJECT LIMIT 0" to "REJECT LIMIT unlimited" and run your selection again? If you did not get error, then it means that you get bad data in the file.
Look at this web page, and tell me if you did every step.
http://www.adp-gmbh.ch/ora/misc/ext_table.html

Will continue from there.
Is it a fixed length file ?
If so you have to put the position (1:4)  in

create table OTIF_OWNER.external_FO_FG_19
(
  REGION                   VARCHAR2(4 BYTE),
  END_MARKET               VARCHAR2(6 BYTE),
  ORIGINAL_REQUEST_DATE    DATE,
  REASON_TEXT              VARCHAR2(1 BYTE)
)

ORGANIZATION EXTERNAL
     (
       type oracle_loader
         default directory extract_dir
         access parameters
                    (
                  fields terminated by ';'
                 missing field values are null
                             (
                               REGION position(1: 4),
                               END_MARKET position(6: 11),
                                 ORIGINAL_REQUEST_DATE position(13: 22) date 'dd/mm/yyyy' ,
                                REASON_TEXT position(24: 24)
                             )
                 )
location ('test2.dat')
)

ASKER CERTIFIED SOLUTION
Avatar of Nievergelt
Nievergelt
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just change DELIMITED to TERMINATED for your "FIELDS" clause. It's FIELDS TERMINATED BY and RECORDS DELIMITED BY.
Hope that helps!