Home » SQL & PL/SQL » SQL & PL/SQL » creating external table problem
creating external table problem [message #248385] Thu, 28 June 2007 16:06 Go to next message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
can we dynamically generate file name while using external table, like this

declare
stmt varchar2(1000);
str varchar2(50);
begin
str:= 'dk'||to_char(sysdate, 'yyyymmddhhmiss')||'.dat';
stmt:=
'create table test
( f1 varchar2(20),
f2 varchar2(20)
)
organization external
(
type oracle_loader
default directory dic
access parameters (
records delimited by newline
fields
(
f1 position(01:08) char(10),
f2 position(09:16) char(10)
))
location(' || str|| ')
)';
execute immediate stmt;
end;

the above code is running good when i am using the direct file name in the "location" but when i am giving the indirectly i am getting the run time error
Re: creating external table problem [message #248386 is a reply to message #248385] Thu, 28 June 2007 16:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Too bad for you that you did NOT read & FOLLOW the posting guidelines in the #1 STICKY post at the top of this forum.

Essentially what you said is "My car won't go. Tell me how to make my car go."

Error? What error? I don't see any error.

Please do NOT describe what you see is happening.
Use CUT & PASTE so we can see for ourselves.

[Updated on: Thu, 28 June 2007 16:21] by Moderator

Report message to a moderator

Re: creating external table problem [message #248387 is a reply to message #248385] Thu, 28 June 2007 17:58 Go to previous messageGo to next message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
here is the entire program and the error

create or replace procedure p2
is
stmt varchar2(1000);
str varchar(50);
begin
STR:= 'dK'||TO_CHAR(SYSDATE, 'YYYYMMDDHHMISS')||'.txt';
stmt:=
'create table temp_table
(
field1 varchar2(10),
field2 varchar2(10)
)
organization external
(
type ORACLE_LOADER
default directory direc
access parameters (
records delimited by newline
fields(
field1 position(01:08) char(10),
field2 position(09:16) char(10)
))
location ( ' ||STR|| ')
)' ;
execute immediate stmt;
end;


this is the runtime error

ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "SYSTEM.P2", line 25
ORA-06512: at line 1
Re: creating external table problem [message #248389 is a reply to message #248385] Thu, 28 June 2007 18:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Since you are incapable or unwilling to read & follow seemingly simple & straight forward directions, You're On Your Own (YOYO)!

I suspect your problem involves single quote marks.
Re: creating external table problem [message #248394 is a reply to message #248385] Thu, 28 June 2007 20:28 Go to previous messageGo to next message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
i did not understand what rule i break, i have posted the entire program with error

regards
bharat
Re: creating external table problem [message #248396 is a reply to message #248385] Thu, 28 June 2007 20:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How is any reader of the forum supposed to know which is LINE 25?

From http://www.orafaq.com/forum/t/42428/74940/
Within the section (in BOLD type) Posting Guidelines
4. If you want to post error codes or SQL*Plus output, just copy everything that is on your screen when the error occurred, for instance:
5. Post Database and OS version. By doing so, you avoid reactions using features you can't use.
6. Post any code with the formatting tags:

So which the THREE above did you do?

[Updated on: Thu, 28 June 2007 20:55] by Moderator

Report message to a moderator

Re: creating external table problem [message #248416 is a reply to message #248385] Thu, 28 June 2007 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to DYNAMICALLY create the table?
Why not PREVIOUSLY create it?
You can change the location with an ALTER TABLE.

Regards
Michel
Re: creating external table problem [message #248532 is a reply to message #248385] Fri, 29 June 2007 07:30 Go to previous message
bharat0201
Messages: 15
Registered: June 2007
Junior Member
ok i will do that, i didnt know we can change location with alter table
Previous Topic: Dynamic SQL (Merged topics)
Next Topic: Working with Dates in Oracle
Goto Forum:
  


Current Time: Sat Dec 10 01:06:28 CST 2016

Total time taken to generate the page: 0.09315 seconds