Drop the table (if exists)
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE EXT_TEST PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;
END;
Verify this does not already exist before running this
Create the "Directory" Object
BEGIN
CREATE OR REPLACE DIRECTORY FULL_SCHEMA_BACKUPS as '/mnt/dbbkup/Databkup/FULL_SCHEMA_BACKUPS'
END;
grant read,write on directory FULL_SCHEMA_BACKUPS to TEST_SUH;
Create the "External" table
3 TEST_Client VARCHAR2(30 BYTE)
4 ,REQUEST_ID VARCHAR2(20 BYTE)
5 ,Measure VARCHAR2(200 BYTE)
6 ,FirstDate VARCHAR2(25 BYTE)
7 ,LastDate VARCHAR2(25 BYTE)
8 ,CHANGED_FIELD VARCHAR2(10 BYTE)
9 ,OLD_VALUE VARCHAR2(100 BYTE)
10 ,NEW_VALUE VARCHAR2(100 BYTE)
11 )
12 ORGANIZATION EXTERNAL
13 (
14 TYPE ORACLE_LOADER
15 DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
16 ACCESS PARAMETERS
17 (
18 SKIP 1
19 FIELDS TERMINATED BY '|'
20 RECORDS DELIMITED BY '\r\n'
21 MISSING FIELD VALUES ARE NULL
22 NOBADFILE
23 NODISCARDFILE
24 NOLOGFILE
25 )
26 LOCATION
27 (
28 'EXT_TEST.txt'
) 29
) 30
31 REJECT LIMIT 0;
Table created.
SQL> select * from EXT_TEST;
select * from EXT_TEST
*
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 "skip": expecting one of: "column, fields,
records"
KUP-01007: at line 1 column 1
Replaced my code as below and it worked. I am able to query from EXT_TEST table.
CREATE TABLE EXT_TEST
(
HEDIS_Client VARCHAR2(30 BYTE)
,REQUEST_ID VARCHAR2(20 BYTE)
,Measure VARCHAR2(200 BYTE)
,FirstDate VARCHAR2(25 BYTE)
,LastDate VARCHAR2(25 BYTE)
,CHANGED_FIELD VARCHAR2(10 BYTE)
,OLD_VALUE VARCHAR2(100 BYTE)
,NEW_VALUE VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
LOGFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.log'
BADFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.bad'
SKIP 1
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION
(
'EXT_TEST.txt'
)
)
REJECT LIMIT 0;
and removed
NOBADFILE
NODISCARDFILE
NOLOGFILE
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE EXT_TEST PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;
END;
Verify this does not already exist before running this
Create the "Directory" Object
BEGIN
CREATE OR REPLACE DIRECTORY FULL_SCHEMA_BACKUPS as '/mnt/dbbkup/Databkup/FULL_SCHEMA_BACKUPS'
END;
grant read,write on directory FULL_SCHEMA_BACKUPS to TEST_SUH;
Create the "External" table
SQL> CREATE TABLE EXT_TEST
2 (3 TEST_Client VARCHAR2(30 BYTE)
4 ,REQUEST_ID VARCHAR2(20 BYTE)
5 ,Measure VARCHAR2(200 BYTE)
6 ,FirstDate VARCHAR2(25 BYTE)
7 ,LastDate VARCHAR2(25 BYTE)
8 ,CHANGED_FIELD VARCHAR2(10 BYTE)
9 ,OLD_VALUE VARCHAR2(100 BYTE)
10 ,NEW_VALUE VARCHAR2(100 BYTE)
11 )
12 ORGANIZATION EXTERNAL
13 (
14 TYPE ORACLE_LOADER
15 DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
16 ACCESS PARAMETERS
17 (
18 SKIP 1
19 FIELDS TERMINATED BY '|'
20 RECORDS DELIMITED BY '\r\n'
21 MISSING FIELD VALUES ARE NULL
22 NOBADFILE
23 NODISCARDFILE
24 NOLOGFILE
25 )
26 LOCATION
27 (
28 'EXT_TEST.txt'
) 29
) 30
31 REJECT LIMIT 0;
Table created.
SQL> select * from EXT_TEST;
select * from EXT_TEST
*
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 "skip": expecting one of: "column, fields,
records"
KUP-01007: at line 1 column 1
Replaced my code as below and it worked. I am able to query from EXT_TEST table.
CREATE TABLE EXT_TEST
(
HEDIS_Client VARCHAR2(30 BYTE)
,REQUEST_ID VARCHAR2(20 BYTE)
,Measure VARCHAR2(200 BYTE)
,FirstDate VARCHAR2(25 BYTE)
,LastDate VARCHAR2(25 BYTE)
,CHANGED_FIELD VARCHAR2(10 BYTE)
,OLD_VALUE VARCHAR2(100 BYTE)
,NEW_VALUE VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
LOGFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.log'
BADFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.bad'
SKIP 1
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION
(
'EXT_TEST.txt'
)
)
REJECT LIMIT 0;
and removed
NOBADFILE
NODISCARDFILE
NOLOGFILE
No comments:
Post a Comment