Home » SQL & PL/SQL » Client Tools » Conditional Spool (Windows XP Oracle : 9.2.0.1.0)
Conditional Spool [message #336701] Mon, 28 July 2008 13:59 Go to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member

Hi guys,

I looking some on Conditional Spool.

Is this possible ?


BEGIN
   SELECT 1
     INTO v1
     FROM abc
    WHERE ID = &id;
END; 

IF V1 IS NOT NULL
THEN

SPOOL c:\test.txt
    
    SELECT * FROM ABC
    WHERE id = &id;

SPOOL OFF

ELSE
  NULL;
END;


Appreciated your insight!

Re: Conditional Spool [message #336704 is a reply to message #336701] Mon, 28 July 2008 14:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

PL/SQL <> SQL <> SQL*Plus

SPOOL is a valid sqlplus command & invalid within PL/SQL.

IF THEN is not valid SQL.

Please make up you mind which language you are (ab)using.
Re: Conditional Spool [message #336732 is a reply to message #336704] Mon, 28 July 2008 15:54 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member


Thank you for pointing that
but I understand the difference between PL/SQL and SQL very well.

Here my only intention is to explain it through pseudo code to achieve the same results from SQL*Plus.
if possible at all.

Is/are there any SQL*Plus commands
which can work as condition spool of data?


like if not data found then I do not want to spool zero kb files
else spool the files.
Re: Conditional Spool [message #336735 is a reply to message #336732] Mon, 28 July 2008 15:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is no if/then/else in SQL*Plus.
But if you want to spool or do nothing you can do something like:
WHENEVER SQLERROR EXIT;
DECLARE V1 NUMBER;
BEGIN
   SELECT 1
     INTO v1
     FROM abc
    WHERE ID = &id;
END; 
SPOOL c:\test.txt
    
    SELECT * FROM ABC
    WHERE id = &id;

SPOOL OFF

Of course, this does not guarantee that if you have a result in the first query, you also have one in the second one.

Regards
Michel

[Updated on: Mon, 28 July 2008 16:02]

Report message to a moderator

Re: Conditional Spool [message #336753 is a reply to message #336701] Mon, 28 July 2008 19:11 Go to previous messageGo to next message
Lynn T
Messages: 8
Registered: July 2008
Junior Member
There's a way to jerry-rig an if/then using the decode or case statement in SQL+. I have done a couple of variations, but I think this will give you the basic idea. I'm running the second query (X2) using '@' based on the value from the Accept statement. If yes, xspl is set to X2 (the conditional program to run that contains the query and the spool statement).

/*----------------------------------*/
/* x3.sql */
/*----------------------------------*/
Accept Spooler Prompt "Spool (N/Y): "

Column To_Spool_or_Not_to_Spool New_Value xSpl
Select Decode('&Spooler','Y','x2', 'N',' ') To_Spool_or_Not_to_Spool
From Dual;

host del x2.txt
@&xspl
undefine spooler, xspl

/*----------------------------------*/
/* x2.sql */
/*----------------------------------*/
spool x2.txt

select *
from emp;

spool off

Re: Conditional Spool [message #336754 is a reply to message #336753] Mon, 28 July 2008 20:45 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member

Great thanks to Michel & Lynn

In my case if first statement return rows
then second statement will also returns rows
or none of them should return any rows.

I think best is Michel solution for me.
but I will sure give try to Lynn's as well.


Again thank you guys for your insight!
Re: Conditional Spool [message #336792 is a reply to message #336754] Tue, 29 July 2008 01:05 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In my case if first statement return rows
then second statement will also returns rows

Not sure, if someone delete the rows between the 2 queries.
Only you know if this is possible.

Regards
Michel
Previous Topic: Table space
Next Topic: SQL*Plus Compatibility (OpenVMS Client/Linux RDBMS)
Goto Forum:
  


Current Time: Fri Dec 09 08:09:04 CST 2016

Total time taken to generate the page: 0.09017 seconds