troubleshooting Question

create external table...

Avatar of bbvic
bbvic asked on
Oracle Database
2 Comments1 Solution1391 ViewsLast Modified:
when i just execute

CREATE TABLE ext_schoolof
....
....
REJECT LIMIT UNLIMITED;

, then it gets " select * from ext_schoolof "

BUT when i use procedure, it creates external table but when I try to get
" select * from ext_schoolof ", then I get errors

The error numbers are

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: "double-quoted-string, hexprefix, newline, single-quoted-string"
KUP-01008: the bad identifier was: NEWLINEBADFILE
KUP-01007: at line 1 column 22
ORA-06512: at "SYS.ORACLE_LOADER", line 19

---

can you do me a favor how to fix?


CREATE OR REPLACE PROCEDURE test_external
IS
BEGIN
EXECUTE IMMEDIATE '
CREATE TABLE ext_schoolof'
|| '
( student_id VARCHAR2(7),
department_id VARCHAR2(1),
level_id VARCHAR2(1),
gender VARCHAR2(3),
major_id VARCHAR2(1),
advisor_id VARCHAR2(30)
)'
|| 'ORGANIZATION EXTERNAL'
|| '
(
TYPE oracle_loader
DEFAULT DIRECTORY test_schools
ACCESS PARAMETERS '
|| '
('
|| 'RECORDS DELIMITED BY NEWLINE'
|| 'BADFILE ''test_schools.bad'''
|| 'DISCARDFILE ''test_schools.dis'''
|| 'LOGFILE ''test_schools.log'''
|| 'FIELDS '
|| '
(
student_id (1:7) VARCHAR2(7),
department_id (8:1) CHAR(1),
level_id (9:1) CHAR(1),
gender (10:3) CHAR(3),
major_id (13:1) CHAR(1),
advisor_id (14:30) VARCHAR2(30)
)'
|| ')'
|| 'LOCATION (''test_schools_status.txt'')'
|| ')'
|| 'REJECT LIMIT UNLIMITED';
END test_external;
ASKER CERTIFIED SOLUTION
dqmq

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros