Home » SQL & PL/SQL » SQL & PL/SQL » create external table
icon5.gif  create external table [message #287445] Wed, 12 December 2007 04:39 Go to next message
sujata_ghatak
Messages: 3
Registered: December 2007
Location: Kolkata
Junior Member
hi all,
Can anyone tell me if it is possible to create an external table within a stored procedure????
Re: create external table [message #287448 is a reply to message #287445] Wed, 12 December 2007 04:51 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

Yes,you can using dynamic sql.but it would be a bad idea to do that.

regards,
Re: create external table [message #287464 is a reply to message #287445] Wed, 12 December 2007 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to create a (external) table in a procedure?
Create it before not during an operation.

Regards
Michel

[Updated on: Wed, 12 December 2007 05:30]

Report message to a moderator

Re: create external table [message #287486 is a reply to message #287464] Wed, 12 December 2007 06:29 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
As previously mentioned, not a good idea to do this dynamically.
Why would you not create it before executing your procedure?
Re: create external table [message #287920 is a reply to message #287464] Fri, 14 December 2007 01:12 Go to previous messageGo to next message
sujata_ghatak
Messages: 3
Registered: December 2007
Location: Kolkata
Junior Member
thanks to all of you,I've solved the problem. But now I'm facing another problem, can anyone tell me if I want to set a default value for a field in external table how is it possible?
Re: create external table [message #287926 is a reply to message #287920] Fri, 14 December 2007 01:20 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

you can't.External Tables are read-only.

regards,
Re: create external table [message #287927 is a reply to message #287920] Fri, 14 December 2007 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And external table is just a pointer to the file content.
You use SQL to access it and in your SELECT you can use NVL function to give a default value.

Regards
Michel
Re: create external table [message #287989 is a reply to message #287927] Fri, 14 December 2007 04:33 Go to previous messageGo to next message
sujata_ghatak
Messages: 3
Registered: December 2007
Location: Kolkata
Junior Member
hi,
I've found something which I think can help to set a default value that is MISSING FIELD VALUES .But I cannot use it. If someone have used it please help me out
Re: create external table [message #287990 is a reply to message #287989] Fri, 14 December 2007 04:36 Go to previous message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

.But I cannot use it.

Clarify.
Copy and paste what you did and what you got (use SQL*Plus).

Regards
Michel

[Updated on: Fri, 14 December 2007 04:37]

Report message to a moderator

Previous Topic: help for Data display
Next Topic: retrieve the missing numbers
Goto Forum:
  


Current Time: Wed Dec 07 06:37:04 CST 2016

Total time taken to generate the page: 0.11120 seconds