I'm having an issue with external tables, that I don't understand. Hoping somebody can help me out?
I have this data file:
/some/dir/schema.table.txt
123|||testing
234|||junk data
345|||read me
And I have this script:
-- logged into user with "DBA" role.
SET PAUSE OFF
CREATE USER user1 IDENTIFIED BY junk
PROFILE DEFAULT
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT create session TO user1;
CREATE DIRECTORY MY_DIR AS '/some/dir';
GRANT read,write ON DIRECTORY MY_DIR TO user1;
CREATE TABLE USER1.MY_TABLE
(
ID NUMBER(6),
NAME VARCHAR(1000)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY MY_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
CHARACTERSET WE8ISO8859P1
FIELDS TERMINATED BY '|||'
MISSING FIELD VALUES ARE NULL
(
ID DECIMAL EXTERNAL,
NAME CHAR
)
)
LOCATION
(
'schema.table.txt'
)
);
SELECT COUNT(*) FROM USER1.MY_TABLE WHERE ROWNUM = 1;
Now, when I run that in a 11.2.0.1.0 database, it runs fine.
However, when I then run it in a 11.2.0.2.0 database, it throws the following error on the final SELECT statement.
[Error] Execution (40: 28): ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file schema.table.txt in MY_DIR not found
I've tried to check as much as I can to verify everything is the same. The file privileges appear to be setup properly.
The following PL/SQL block works fine on both databases:
SET SERVEROUT ON
DECLARE
v_fh utl_file.file_type;
v_row VARCHAR2(4000);
BEGIN
v_fh := utl_file.fopen ( 'MY_DIR', 'schema.table.txt', 'r' );
utl_file.get_line ( v_fh, v_row );
utl_file.fclose(v_fh);
dbms_output.put_line ( v_row );
END;
/
Anyone have any ideas? I'm completely stumped! O_0