SQLPlus conditional execution with variable from query

Question

I have a batch file which has many steps in it that will get executed one by one.

However, to be able to make it more flexible, I want to include a condition check in SQLPlus.

Something like, to get the conditional variables' value from a query first and store is in say v_variable. Then use it for some checks like

IF v_variable = 'Y' THEN
--DO SOME DDL
ELSE
--DO OTHER DDL
END IF

I have to repeat this block in many places in the batch file and I can't do it through a PL/SQL somehow.

I am trying to use this COLUMN command in SQLPlus but somehow not getting the variable value to get saved.

COLUMN VARIABLE1 NEW_VALUE V_VARIABLE1

SELECT PARAM_VAL AS VARIABLE1 FROM TABLE_T WHERE PARAM_TYPE =  'XYZ'; 
-- This query will only throw one record.

DEFINE V_VARIABLE1

Is that absolutely wrong? What do we do to see if the V_VARIABLE1 is getting the value coming from the query?

And even after I get it right I am clueless on the IF-ELSE part. Can anybody help here? I am interested in solution that works for SQLPlus.

Kirill Leontev · Accepted Answer · 2014-12-19 02:26:24Z

6

sql*plus doesn't support flow control natively, so anything you can do here would fall somewhere between "workaround" and "hack". Some of possible options are:

Use PL/SQL block and run your DDL as dynamic SQL via execute immediate or dbms_utility.execute_ddl_statement. Full access to PL/SQL features, so most flexible in terms of flow control and statement building, however harder to manage if you're deploying something large.
Write a script file per if/else branch, get its name with something like column/query trick you provided in your post, run it with something like @&scriptname.
Use substitution variables that, when used properly, will comment out some parts of your script. You can use Tanel Poder's snapper utility script as example; notice &_IF_ substitution variables there.
You can embed child script into parent script's pl/sql block. Like this:

--

21:21:23 SQL> ho cat run.sql
begin
case '&1.' when 'A' then
@script_a
when 'B' then
@script_b
else
null;
end case;
end;
/

21:21:26 SQL> ho cat script_a.sql
dbms_output.put_line('this is a');

21:21:30 SQL> ho cat script_b.sql
dbms_output.put_line('this is b');

21:21:34 SQL> @run
Enter value for 1: A
this is a

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
21:21:37 SQL> @run B
this is b

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

answered Dec 19, 2014 at 2:26

Kirill Leontev

11k8 gold badges46 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Neels Over a year ago

Thanks for the explanations. Now even I feel that SQLPlus is not gonna suffice. I have moved my code to a PL/SQL block.

Kirill Leontev Over a year ago

@Neels just FYI - you might want to take a look at conditional compilation either docs.oracle.com/cd/B19306_01/appdev.102/b14261/…

RGuggisberg · Accepted Answer · 2014-12-19 01:57:56Z

I don't have any experience with SQLPlus. I am going to assume that you can redirect the output of your select command to a temp file. Assuming you only selected one column in 1 record you can then do something like this:

FOR /F "tokens=*" %%A IN ('FINDSTR /v "(" YourTempFile.txt) DO SET YourVariable=%%A

"tokens=*" is optional. It will remove leading spaces and save the rest of the string including spaces. May not be relevant depending on your data.

You may not need the 'FINDSTR /V "(" and the trailing ' either. I am assuming the output of SQLPlus will be similar to SQL and this would exclude the number of rows processed that gets reported by SQL. Anything is possible. If you can't make this work, post the contents of the temp file and we can make the necessary modifications.

Collectives™ on Stack Overflow

SQLPlus conditional execution with variable from query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related