Oracle – Bind Variable and Shell Scripting with SQLPlus

oraclescriptingsqlplus

I wish to execute DMLs using bind variable with shell scripts.
For example, something like:

#!/bin/bash

SH_NUM=10

sqlplus -S test_user/test_pass <<EOD
var a number;
a:=${SH_NUM}
insert into test_table values(a);
commit;
EOD

I'm not sure if this is possible, I can use this approch when using pl/sql but I wish to know if I can do it this method as well.

Best Answer

Sure it is possible:

[oracle@o71 ~]$ cat 1.sh
#!/bin/bash

SH_NUM=10

sqlplus -S bp/bp<<EOD
set lines 220 pages 5000
col plan_table_output format a50
var a number;
exec :a :=${SH_NUM};
insert into t1 values(:a);
commit;
select * from table(dbms_xplan.display_cursor(format=>'basic'));
exit
EOD

Execute it (I just put DBMS_XPLAN.DISPLAY_CURSOR in there to see the last statement executed):

[oracle@o71 ~]$ ./1.sh

1 row created.


Commit complete.


PLAN_TABLE_OUTPUT
--------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
insert into t1 values(:a)


-----------------------------------------
| Id  | Operation                | Name |
-----------------------------------------
|   0 | INSERT STATEMENT         |      |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |
-----------------------------------------


12 rows selected.
Related Question