Generate spool in based on condition in Oracle sql

Question

I need to write an oracle sql script based on some condition.

Like User has to type 'y' or 'n' if he types 'y' then i need to

accept gen prompt 'Enter y if you want to generate spool' default 'n'
spool c:\a.spl

    SELECT CUST_ID INTO NO 
       FROM ACC_MASTER 
       WHERE ACCOUNT_NO='&CRN' 
          AND BRANCH_NO='&BRN';


    select * from a where lid=no;
    select * from b where lid=no;
    select * from c where lid=no;

spool off;

If the user types 'N' Nothing needs to be selected.

Please advise

Have you tried writing a procedure with one parameter accepting your character `Y`? — Charlesliam, Jun 26 '14 at 07:42
Try downloading Oracle SQLDeveloper. http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html?ssSourceSiteId=otnpt than using sql plus command. — Charlesliam, Jun 26 '14 at 07:46
I cant use plsql because if use plsql I ave put the columns in select * from a into some vaiables. — user3737084, Jun 26 '14 at 07:48
do you want the spool to be conditional or do you want the query results to be conditional based on the prompt answer? — , Jun 26 '14 at 08:46

score 0 · Answer 1 · answered Jun 26 '14 at 07:50

0

If I were you I would use a bash statement ("if-fi") if you use Linux, or statement "if" for Windows to work around it.

answered Jun 26 '14 at 07:50

neshkeev

6,280
3
26
47

Could you please tell me how do you use if statement in SQL script.Because if i have write like this if (gen=='y') then do all the ops; end if; will not work beacuse selct col into i have to use – user3737084 Jun 26 '14 at 07:56
for example for linux for bash you would write `#!/bin/bash echo "Enter y if you want to generate spool, followed by [ENTER]:" read choice if [ "$choice" == "y" ]; then sqlplus / spool c:\a.spl ... spool off; fi` – neshkeev Jun 26 '14 at 08:15
I m running through sql developer in windows not unix/linux – user3737084 Jun 26 '14 at 10:27
Describe your scenario, I can't understand why you need this. I use the clause "spool" to make sure that my script has been run successfully on other servers by someone (not me). – neshkeev Jun 26 '14 at 11:46

score 0 · Answer 2 · answered Jun 26 '14 at 08:53

Create a noop file first

C:\>echo. > null.sql

script

set ver off

accept gen prompt 'Enter y if you want to generate spool ' default 'n'

set term off
column col noprint new_value scriptname
select decode(lower('&gen'),'y','myspoolscript','n', 'null') col from dual;
set term on

set ver on

@&scriptname

Generate spool in based on condition in Oracle sql

2 Answers2