How to pass variable in PL/SQL block statement inside the UNIX shell script

Question

How to pass variable in PL/SQL block inside the UNIX shell script. Here is the ex-

#!/usr/bin/ksh
declare -a arr=("01", "03", "05", "07", "09", "11")

for i in "${arr[@]}"
do

$ORACLE_HOME/bin/sqlplus -s <<EOF
scott/tiger
set serveroutput on
declare
  id   = &1
  var    .........;
begin
 for i in (select .... from ..... where id = &id);
 loop
  select * from ..........;
  <pkg>.<sp>(var);    -- calling to execute job then process will do
 insert into ....... values(&id, ........);
 end loop;
end;

In the &id at the time of run this anonymous PL/SQL block statement this value will be vary like 1, 3, 5, 7, 9, 10, 11 like this. It means for every ID this PL/SQL block will run automatically using this Shell script.

For ex, if i run separate PL/SQL block in the SQL developer then for id = 01

set serveroutput on
declare
  var    .........;
begin
 for i in (select .... from ..... where id = 01);
 loop
  select * from ..........;
  <pkg>.<sp>(var);    -- calling to execute job then process will do
 insert into ....... values(01, ........);
 end loop;
end;

Then same for rest of the ID this shell script should be run automatically.

not clear exactly what you are asking, but do you mean have shell code id="01" set before the first line $ORACLE_HOME/... .... <<EOF and then later you can have where id = "$id" and later still values ($id,....) ? Just do that and it should work. (I don't see the required closing EOF in your first sample code). Good luck. — shellter
– shellter, Commented Aug 23, 2019 at 14:17

Max · Accepted Answer · 2019-08-26 13:54:53Z

3

You can even separate code for better organize your code base:

run.sh

#!/usr/bin/ksh
for id in 01 03 05 07 09 10 11
do
$ORACLE_HOME/bin/sqlplus -s SCOTT/TIGER @proc.sql $id
done

proc.sql

set serveroutput on
declare
  l_id  number := &1;
  var    .........;
begin
 for i in (select .... from ..... where id = l_id);
 loop
  select * from ..........;
  <pkg>.<sp>(var);    -- calling to execute job then process will do
 insert into ....... values(l_id, ........);
 end loop;
end;
/
exit -- exit SQL*Plus

All arguments passed to a SQL script can be accessed by their position using &1, &2, &3, etc...

Notice & is for substitution variables, so if you are passing varchar2 types, you must enclose them in quotes like '&1' '&2' '&3'.

l_id varchar2(100) := '&1';

Notice also if the string you are passing contains a single quote, this can breake your syntax, so you can use this notation to avoid such issue.

l_id varchar2(100) := q'(&1)';

edited Aug 26, 2019 at 13:54

answered Aug 24, 2019 at 14:32

Max

2,5623 gold badges29 silver badges45 bronze badges

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

2 Comments

user8487380 Over a year ago

Thanks Massimo for give me the answer but I've one doubt. How we pass the array variable inside the PL/SQL block statement because for every variable that mentioned above PL/SQL block will execute.

Max Over a year ago

I've just review the example according to your needs

user8487380 · Accepted Answer · 2019-08-26 12:31:16Z

1

Based solely on your example of id=01 (ie, 01 does not need to be quoted) ...

# for a single id value

id=01

$ORACLE_HOME/bin/sqlplus -s <<EOF
scott/tiger
set serveroutput on
declare
  var    .........;
begin
 for i in (select .... from ..... where id = ${id});
 loop
  select * from ..........;
  <pkg>.<sp>(var);
 insert into ....... values(${id}, ........);
 end loop;
end;
EOF

# for a series of id values

for id in 01 03 05 07 09 10 11
do

$ORACLE_HOME/bin/sqlplus -s <<EOF
scott/tiger
set serveroutput on
declare
  var    .........;
begin
 for i in (select .... from ..... where id = ${id});
 loop
  select * from ..........;
  <pkg>.<sp>(var);
 insert into ....... values(${id}, ........);
 end loop;
end;
EOF

done

What I'm not sure about is the purpose of the leading zero in 01; if the id column is a character datatype then we'll need to update the above examples and change $(id) to '${id}'.

edited Aug 26, 2019 at 12:31

user8487380

1566 silver badges18 bronze badges

answered Aug 23, 2019 at 17:19

markp-fuso

38.5k5 gold badges24 silver badges48 bronze badges

2 Comments

user8487380 Over a year ago

Thanks Mark to get the answer but instead of while `for loop will apply in the above code.

markp-fuso Over a year ago

arg ... yeah, of course; had started out with a while ... do ... done < <(get_list>) construct and then decided for easier for but forget to switch out while and for; thanks

Collectives™ on Stack Overflow

How to pass variable in PL/SQL block statement inside the UNIX shell script

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related