Home » SQL & PL/SQL » SQL & PL/SQL » Effective spooling method (oracle 10g)
Effective spooling method [message #395786] Thu, 02 April 2009 10:23 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi Friends,
I have a procedure, which do some calculations and put the out put in to a temporary table.Then

spool abc.txt
select * from tab_name;
spool off;


Insted of putting the value in the temp tebale, I can put the value in the dbms_output . In the following manner

spool abc.txt
exec proc_name ;
spool off;

I need your help friends.
- which is the best approch
- how can I use the extended table here
Re: Effective spooling method [message #395790 is a reply to message #395786] Thu, 02 April 2009 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 25034
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

>- which is the best approch
Best based upon which metric & what value?

>- how can I use the extended table here
"extended table"?

I do not see any extended table.
Why would you use "extended table" instead of actual table?
Re: Effective spooling method [message #395792 is a reply to message #395790] Thu, 02 April 2009 10:31 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
we can not add any extra table in production environment . it works fine in development environment as we have sufficient privilege.

So we thought of not creating any extra table how to fix the issue.
Re: Effective spooling method [message #395795 is a reply to message #395786] Thu, 02 April 2009 10:35 Go to previous messageGo to next message
BlackSwan
Messages: 25034
Registered: January 2009
Location: SoCal
Senior Member
I do not see any extended table.
Why would you use "extended table" instead of actual table?

>So we thought of not creating any extra table how to fix the issue.

ISSUE? What issue? I do not see any issue.

What is difference between "extra" table & "extended" table?

In vast majority of cases with Oracle to "temporary" table is required.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Effective spooling method [message #395796 is a reply to message #395786] Thu, 02 April 2009 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ First one
2/ What is an "extended table"?

Regards
Michel
Re: Effective spooling method [message #395957 is a reply to message #395792] Fri, 03 April 2009 05:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can't add a table in development, then you don't really have a choice - it's got to be DBMS_OUTPUT.
There will be a performance hit using Dbms_Output - instead of simply selecting the data from an SQL statement, you'll have to step through the data one row at a time and call DBMS_OUTPUT for each row, which will be slower.

Additionally, in 9i there are quite strict limits to the amount of data you can write out using DBMS_OUTPUT - no more than 255 chrs per line.

If you've got no choice though, you'll just have to use it.
Re: Effective spooling method [message #395986 is a reply to message #395957] Fri, 03 April 2009 07:32 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
Why not by doing something like this...?

Create or replace procedure ( RC OUT SYS_REFCURSOR)
IS
BEGIN
OPEN RC FOR
[SELECT QUERY]
..


2. Spool file name
3. Execute proc
4 spool off


[Updated on: Fri, 03 April 2009 07:34]

Report message to a moderator

Re: Effective spooling method [message #397064 is a reply to message #395986] Wed, 08 April 2009 14:33 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Can we use external table ?
Re: Effective spooling method [message #397068 is a reply to message #395786] Wed, 08 April 2009 14:45 Go to previous messageGo to next message
BlackSwan
Messages: 25034
Registered: January 2009
Location: SoCal
Senior Member
>Can we use external table ?
I give up. Can you?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Re: Effective spooling method [message #397071 is a reply to message #397064] Wed, 08 April 2009 14:52 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
External Table...
Do you want to spool your procedure output and store it in a txt file and create an external table on that txt file?
You may need to modify the file a bit before selecting that from external table.
Your procedure should be intelligent enough to put the delimiter between the columns.

By
Vamsi
Re: Effective spooling method [message #397073 is a reply to message #397064] Wed, 08 April 2009 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bond007 wrote on Wed, 08 April 2009 21:33
Can we use external table ?

For what?
What is an "external table" for you?

Regards
Michel

Re: Effective spooling method [message #397074 is a reply to message #395786] Wed, 08 April 2009 14:57 Go to previous message
BlackSwan
Messages: 25034
Registered: January 2009
Location: SoCal
Senior Member
>Can we use external table ?
Realize that Oracle external tables are READ ONLY data structures.
Previous Topic: SQL help
Next Topic: Record Generation
Goto Forum:
  


Current Time: Sat Dec 03 13:59:40 CST 2016

Total time taken to generate the page: 0.08146 seconds