CODEWITHSUNDEEP
oracle-databaseshellunixplsql
user8487380
user8487380

Reputation: 156

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

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.

Upvotes: 0

Views: 3646

Answers (2)

Max
Max

Reputation: 2552

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)';

Upvotes: 3

markp-fuso
markp-fuso

Reputation: 35461

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}'.

Upvotes: 1

Related Questions