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.
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.
select * from t1 returns 0 rows..
– Nir
Nov 5 '18 at 16:17
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
Nov 5 '18 at 16:58
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;
– miracle173
Nov 5 '18 at 17:09
