0

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.

0

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.
  • Well, it does completed without error, however select * from t1 returns 0 rows.. – Nir Nov 5 '18 at 16:17
  • @Nir I beg to differ: pastebin.com/Ny7gWh7g – Balazs Papp Nov 5 '18 at 16:49
  • 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 Nov 5 '18 at 16:58
  • Solution: change a assignment to exec :a := ${SH_NUM}; – Nir Nov 5 '18 at 17:09
  • 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; – miracle173 Nov 5 '18 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.