How to iterate over positional script parameters in sqlplus?

Question

In an Oracle sqlplus script, I can use single script parameters as

set serveroutput on
define arg = '&1'
begin
    dbms_output.putline('&arg.');
end;
/

And in a Unix shell, I can iterate over given script arguments and pass that to an sqlplus script with

for x in "$@"; do
    sqlplus user/passwd@sid @script.sql "$x"
done

Is there a similar way in Oracle sqlplus? Something like

for arg in &* loop
    dbms_output.putline('&arg.');
end loop;

where arg would take values of &1, &2, &3, ... This means, when I call

$ sqlplus user/passwd@sid @script.sql a b c

I would see as output

a
b
c

you can do this using PL/SQL inside sqlplus. Is it ok for you ?? — Roberto Hernandez
– Roberto Hernandez, Commented Aug 20, 2021 at 9:51
I don't understand, but if there's a way, I would be interested. — Olaf Dietsche
– Olaf Dietsche, Commented Aug 20, 2021 at 9:56
Just to be clear. The list of arguments might contain any number of variables , and they are split by a whitespace . Am I correct ? — Roberto Hernandez
– Roberto Hernandez, Commented Aug 20, 2021 at 9:58
@RobertoHernandez I updated the question, maybe it's clearer now. — Olaf Dietsche
– Olaf Dietsche, Commented Aug 20, 2021 at 11:20
I post an answer. Let me know if it covers what you are looking for — Roberto Hernandez
– Roberto Hernandez, Commented Aug 20, 2021 at 11:21

Roberto Hernandez · Accepted Answer · 2021-08-20 11:20:33Z

1

Let's see if this is what you are looking for.

We have an undetermined list of parameters
All of them are split by whitespace ( as it were a list on bash input parameters )
Do something with all of them, independently of the number of parameters
In order for sqlplus to deal with this, all of the parameters must be enclosed with double quotes, so that the code will interpret them as only one parameter.

Script

set serveroutput on size unlimited
declare
vstring  varchar2(4000) := '&1';
vcounter pls_integer;
vrecord  varchar2(20);
BEGIN
vcounter := regexp_count( vstring , ' ') + 1;
for var in 1..vcounter 
loop
    if var = 1 
    then
        vrecord := regexp_substr( vstring, '[^ ]+', 1 , 1 );
        dbms_output.put_line(vrecord);
    elsif var > 1 and var <= vcounter 
    then 
        vrecord := regexp_substr( vstring, '[^ ]+', 1 , var );
        dbms_output.put_line(vrecord); 
    end if;
end loop;   
end;
/

I have this script stored as iteration.sql. So, let's run it with a set of parameters

SQL>  @iteration.sql "2"
old   2: vstring  varchar2(4000) := '&1';
new   2: vstring  varchar2(4000) := '2';
2

PL/SQL procedure successfully completed.

SQL> @iteration.sql "2 m3 88 99 ajkd8 0 88 aa"
old   2: vstring  varchar2(4000) := '&1';
new   2: vstring  varchar2(4000) := '2 m3 88 99 ajkd8 0 88 aa';
2
m3
88
99
ajkd8
0
88
aa

PL/SQL procedure successfully completed.

answered Aug 20, 2021 at 11:20

Roberto Hernandez

8,5833 gold badges22 silver badges51 bronze badges

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

3 Comments

Olaf Dietsche Over a year ago

Ok, this is a manual approach, but not really what I expected. Sorry for being not clear enough upfront.

Roberto Hernandez Over a year ago

@"Olaf Dietsche" , can you put a real example ? It is not very clear to me what you are expecting. You have a set of variable input parameters. Where are they coming from ?? are they always 3 or can be 1 or 2 or n ? your script runs with three or more input parameters and you want a line for each one ?

Olaf Dietsche Over a year ago

Printing the parameters is just an example, this could be any function or procedure to call with this parameter. And this could be any number of parameters, not just three. Sometimes it's just one parameter, sometimes many more.

Collectives™ on Stack Overflow

How to iterate over positional script parameters in sqlplus?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related