Hi,
I am facing the problem in loading data using external table. Please can anyone help me here. Your help is much appreciated.
Steps I followed as below:
1) created the directory and provided grants (777) with help of DBA.
2) created the table as below: and table got created without any error.
CREATE TABLE XXX_XML_TEST
(
GESLACHTSNAAM CHAR(105),
VOORNAAM CHAR(105),
NATIONALITEIT CHAR(105),
ORGANISATIE CHAR(105),
FUNCTIE CHAR(105),
PHOTO1 CLOB,
ZONEGETAL CHAR(105),
PHOTO12 CLOB,
DOCUMENTNR CHAR(105),
VERBLIJFSTATUS CHAR(105),
EXPIRATIEDATUM CHAR(105),
GEBOORTEDATUM CHAR(105),
GEBOORTEPLAATS CHAR(105),
GEBOORTELANDCODE CHAR(105),
BEPERKINGEN CHAR(105),
CONTROLEGETAL NUMBER,
GESLACHT CHAR(2),
ANUMBER CHAR(105)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY IMPORT_TST
ACCESS PARAMETERS
( RECORDS DELIMITED BY "</MKaart>"
BADFILE 'bad_%a_%p.bad'
LOGFILE 'log_%a_%p.log'
MISSING FIELD VALUES ARE NULL
FIELDS
(
dummy char(2000) TERMINATED BY "<MKaart>",
GESLACHTSNAAM CHAR(105) TERMINATED BY "<Geslachtsnaam>" AND "</Geslachtsnaam>",
VOORNAAM CHAR(105) TERMINATED BY "<Voornaam>" AND "</Voornaam>",
NATIONALITEIT CHAR(105) TERMINATED BY "<Nationaliteit>" AND "</Nationaliteit>",
ORGANISATIE CHAR(105) TERMINATED BY "<Organisatie>" AND "</Organisatie>",
FUNCTIE CHAR(105) TERMINATED BY "<Functie>" AND "</Functie>",
PHOTO1 CLOB TERMINATED BY "<PHOTO1>" AND "</PHOTO1>",
ZONEGETAL CHAR(105) TERMINATED BY "<Zonegetal>" AND "</Zonegetal>",
PHOTO2 CLOB TERMINATED BY "<PHOTO2>" AND "</PHOTO2>",
DOCUMENTNR CHAR(105) TERMINATED BY "<DocumentNr>" AND "</DocumentNr>",
VERBLIJFSTATUS CHAR(105) TERMINATED BY "<StatusCode>" AND "</StatusCode>",
EXPIRATIEDATUM CHAR(105) TERMINATED BY "<ExpiratieDatum>" AND "</ExpiratieDatum>",
GEBOORTEDATUM CHAR(105) TERMINATED BY "<GeboorteDatum>" AND "</GeboorteDatum>",
GEBOORTEPLAATS CHAR(105) TERMINATED BY "<GeboortePlaats>" AND "</GeboortePlaats>",
GEBOORTELANDCODE CHAR(105) TERMINATED BY "<GeboorteLandCode>" AND "</GeboorteLandCode>",
BEPERKINGEN CHAR(105) TERMINATED BY "<Beperkingen>" AND "</Beperkingen>",
CONTROLEGETAL NUMBER TERMINATED BY "<ControleGetal>" AND "</ControleGetal>",
GESLACHT CHAR(2) TERMINATED BY "<Geslacht>" AND "</Geslacht>",
ANUMBER CHAR(105) TERMINATED BY "<Anumber>" AND "</Anumber>"
)
)
LOCATION (IMPORT_TST: 'importprint.xml')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING
3) below piece of code used to call the external table
declare
p_filename VARCHAR2(30) DEFAULT 'importprint.xml';
v_stmt VARCHAR2(2000);
BEGIN
v_stmt := 'alter table XXX_XML_TEST location ( IMPORT_TST: '||''''||p_filename||''''||')';
EXECUTE IMMEDIATE v_stmt;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Error in execution :'||SQLERRM);
END;
4) Procedure completed successfully
5) selecting the table.
select * from XXX_XML_TEST
Now am getting the error as below:
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 "missing": expecting one of: "badfile, byteordermark, characterset, colon, column, data, delimited, discardfile, disable_directory_link_check, fields, fixed, io_options, load, logfile, language, nodiscardfile, nobadfile, nologfile, date_cache, preprocessor, readsize, string, skip, t*"
KUP-01007: at line 4 column 15
Please help me.
Thanks for your help