Hi there....
I'm having troubles using an External Table having a 300Mb Flat File (delimited by ';') as source.
I've tried use SQLLOADER, but it was taking too long to load the data into a stage table (3hours +).
So, I've tried to create a external table using ORACLE_DATAPUMP type, using the very same text file as source. Now, i got a KUP-01005 and KUP-01008("...the bad identifier was: RECORDS") error:
CREATE TABLE "DWCORP"."EXT_FREQ_HISTORICO"
( "DTREF" VARCHAR2(10 BYTE),
"RS" VARCHAR2(10 BYTE),
"PV" VARCHAR2(5 BYTE),
"UA" VARCHAR2(10 BYTE),
"SIT" NUMBER,
"MOTIV" VARCHAR2(12 BYTE),
"DTINI" VARCHAR2(10 BYTE),
"DTFIM" VARCHAR2(10 BYTE),
"DIAS" VARCHAR2(10 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DW_DATASOURCE"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
SKIP 2
FIELDS TERMINATED BY ';')
LOCATION
( "DW_DATASOURCE":'freq_hist_teste.txt'
)
)
REJECT LIMIT 2;
C:\Documents and Settings\Administrator>sqlplus dwcorp/*********@dwcorp
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 27 14:54:52 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from ext_freq_historico;
select * from ext_freq_historico
*
ERROR at line 1:
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 "identifier": expecting one of: "compression,
encryption, logfile, nologfile, version"
KUP-01008: the bad identifier was: RECORDS
KUP-01007: at line 1 column 1
SQL>
Here's a sample of the freq_hist_teste.txt file:
DTREF;RS;PV;UA;SIT;MOTIV;DTINI;DTFIM;DIAS;
200801;000000218;01;0005907;1;00000;020080101;020080131;00031;
200801;000000360;01;0073964;1;00000;020080101;020080131;00031;
200801;000000693;01;0005875;1;00000;020080101;020080116;00016;
200801;000000693;01;0005875;1;00047;020080117;020080215;00030;
200801;000000772;01;0067690;1;00000;020080101;020080131;00031;
200801;000002240;01;0007039;1;00000;020080101;020080101;00001;
200801;000002240;01;0007039;1;00047;020080102;020080131;00030;
200801;000002598;01;0085560;1;00000;020080101;020080101;00001;
200801;000002598;01;0085560;1;00047;020080102;020080131;00030;
Is it possible to have a External Table, using type ORACLE_DATAPUMP having a flat file as a source?
Do you guys noticed anything wrong in the create table statement that can lead me to this error?
TIA,
Marcos
Edited by: BI_Creation on Feb 27, 2009 3:00 PM