bind variable and shell scripting with sqlplus

Question

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.

Answer 1

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.

• Nir: Well, it does completed without error, however select * from t1 returns 0 rows..
• Balazs Papp: @Nir I beg to differ: pastebin.com/Ny7gWh7g
• Nir: Can you select * and not count(*) as values are sent empty. I double checked and row count does increasing but values are empty string (or null).
• Nir: Solution: change a assignment to exec :a := ${SH_NUM};
• miracle173: I think a=${SH_NUM} will not do what you expect because a is the append command of the sqlplus editor. The correct command is exec :a:=3 orbegin :a:=3; end;