| Conditional Spool [message #336701] |
Mon, 28 July 2008 13:59  |
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 #336732 is a reply to message #336704] |
Mon, 28 July 2008 15:54  |
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 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 |
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
