Home » SQL & PL/SQL » SQL & PL/SQL » create external table..
create external table.. [message #260134] Fri, 17 August 2007 10:10 Go to next message
bbvic
Messages: 24
Registered: October 2006
Junior Member
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
ORA-29400
KUP-00554
KUP-01008
KUP-01007

---
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;
/
Re: create external table.. [message #260135 is a reply to message #260134] Fri, 17 August 2007 10:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The errors you posted are mostly "read the error in the error text", so not much to tell from the numbers alone there.

Is your data file really named ||test_schools_status.txt|| (with the pipes characters?

It also looks like there are spaces missing in the sql string that is concatenated together for the execute immediate, but it's impossible to tell since you decided not to format the SQL properly.

I also strongly suggest NOT to use the execute immediate, but to create the external table once.

[Updated on: Fri, 17 August 2007 10:26]

Report message to a moderator

Re: create external table.. [message #260140 is a reply to message #260134] Fri, 17 August 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

There is no error in your post.
It's OK.

Regards
Michel
Re: create external table.. [message #260180 is a reply to message #260134] Fri, 17 August 2007 11:57 Go to previous messageGo to next message
bbvic
Messages: 24
Registered: October 2006
Junior Member
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;
Re: create external table.. [message #260182 is a reply to message #260134] Fri, 17 August 2007 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>can you do me a favor how to fix?
Not until you do use a favor & post FORMATTED code!
Re: create external table.. [message #260185 is a reply to message #260180] Fri, 17 August 2007 12:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Save your creation script to a variable, then instead of executing it, use dbms_output.put_line to display it, so that you can see what it looks like and see where your errors are.
Re: create external table.. [message #260190 is a reply to message #260182] Fri, 17 August 2007 12:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

KUP-01008: the bad identifier was: NEWLINEBADFILE


Quote:

It also looks like there are spaces missing in the sql string that is concatenated together for the execute immediate, but it's impossible to tell since you decided not to format the SQL properly.



Re: create external table.. [message #260200 is a reply to message #260134] Fri, 17 August 2007 12:49 Go to previous messageGo to next message
bbvic
Messages: 24
Registered: October 2006
Junior Member
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;

[Updated on: Fri, 17 August 2007 12:50]

Report message to a moderator

Re: create external table.. [message #260202 is a reply to message #260200] Fri, 17 August 2007 12:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And now we are able to see where the missing spaces are :

In front of BADFILE, DISCARDFILE, LOGFILE, FIELDS at least.

So your

....
'RECORDS DELIMITED BY NEWLINE' || 
       'BADFILE ''test_schools.bad''' || 
       'DISCARDFILE ''test_schools.dis''' || 
       'LOGFILE ''test_schools.log''' || 
       'FIELDS ' || 
....


becomes

....RECORDS DELIMITED BY NEWLINEBADFILE 'test_schools.bad'DISCARDFILE ....


which of course is a wrong syntax.

Re: create external table.. [message #260721 is a reply to message #260202] Mon, 20 August 2007 15:11 Go to previous messageGo to next message
bbvic
Messages: 24
Registered: October 2006
Junior Member
I tried to fix it...but ..

I am not sure where i missed..
Re: create external table.. [message #260724 is a reply to message #260134] Mon, 20 August 2007 15:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I tried to fix it...but ..

Try more/harder/again/still.

>I am not sure where i missed..

Neither are we.
Re: create external table.. [message #260725 is a reply to message #260721] Mon, 20 August 2007 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Addspacestoseparatewords.

Regards
Michel
Re: create external table.. [message #260997 is a reply to message #260134] Tue, 21 August 2007 08:30 Go to previous messageGo to next message
pandya
Messages: 7
Registered: August 2007
Junior Member
Could you try typing the string in such a way that

all the text before evry pipe '||' appears in a single line.That means each line should end with a pipe character
Re: create external table.. [message #264191 is a reply to message #260200] Sat, 01 September 2007 12:33 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Hi, have you grants to create table?

This grants was given to you by role or by grant statement.

I have some problems when executing this kind of statements.

If you have grants to create table given by role,
you can create table by "create table" statements
but cant create tables using "execute immediate" statement.

Check this to.
Re: create external table.. [message #579930 is a reply to message #264191] Sun, 17 March 2013 22:36 Go to previous message
deshmukh.sachin
Messages: 1
Registered: March 2013
Location: INDIA
Junior Member
This is documentation Bug . There was an idea of passing additional parameter to script.
A wrapper script to be used since no parameter is allowed .
Previous Topic: SKIP INSERT ON WEEKDAYS - TRIGGER NOT WORKING
Next Topic: Implicit and Explicit CUrsor
Goto Forum:
  


Current Time: Sat Dec 03 07:52:50 CST 2016

Total time taken to generate the page: 0.14957 seconds