Running Oracle script from bash

Question

My query basically is when I am trying to run multiple oracle scripts from bash how do I put comments in between the scripts. I have tried using a workaround by selecting a string from dual. But the output formatting isn't very good.

Can anyone please suggest me a better way.

My Code

#!/bin/bash
#Run Script
    echo "-------------------------------"
    echo "***Running Script1***"
    echo "-------------------------------"
sqlplus -S UID/PSW@DB << EOF
whenever sqlerror exit sql.sqlcode;
set echo off 
set heading off
@/my/path/Script1
Select '--------------' from dual;
select '***Running Script1***' from dual;
Select '--------------' from dual;
@/my/path/Script2
exit;
EOF

Output

-------------------------------
***Running Script1***
-------------------------------
SP2-0310: unable to open file "my/path/Script1.sql"

--------------


***Running Script2***    

--------------

SP2-0310: unable to open file "my/path/Script2.sql"

Expected Output

-------------------------------
***Running Script1***
-------------------------------
SP2-0310: unable to open file "my/path/Script1.sql"

--------------
***Running Script2***    
--------------    
SP2-0310: unable to open file "my/path/Script2.sql"

Marco Baldelli · Accepted Answer · 2013-06-27 06:28:40Z

3

Try using the PROMPT command of SQL*Plus:

$ cat tmp.sh
#!/bin/bash

sqlplus -S UID/PSW@DB << EOF
whenever sqlerror exit sql.sqlcode
set echo off
set heading off

prompt =======================
prompt *** Running Script1 ***
prompt =======================
@/my/path/Script1

prompt =======================
prompt *** Running Script2 ***
prompt =======================
@/my/path/Script2

exit
EOF

Output:

$ ./tmp.sh
=======================
*** Running Script1 ***
=======================
SP2-0310: unable to open file "/my/path/Script1.sql"
=======================
*** Running Script2 ***
=======================
SP2-0310: unable to open file "/my/path/Script2.sql"

answered Jun 27, 2013 at 6:28

Marco Baldelli

3,7381 gold badge24 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

misguided Over a year ago

Brilliant . Exactly what I was looking for.

GregHNZ · Accepted Answer · 2013-06-27 06:21:02Z

2

In the past, I've used dbms_output.put_line :

dbms_output.put_line('starting process at: '||to_char(sysdate,'HH24:MI:SS'));

This requires this line first, with your initial "set" statements:

set serveroutput on size 1000000;

And if you're doing stuff in a declare / begin / end block, you might want this:

dbms_output.enable;

Which may change your output.

edited Jun 27, 2013 at 6:21

answered Jun 27, 2013 at 6:15

GregHNZ

9,0581 gold badge32 silver badges32 bronze badges

Comments

Oleg Mikheev · Accepted Answer · 2013-06-27 06:21:20Z

2

How about

Select '--------------' || chr(10) || '***Running Script1***' || chr(10) || '--------------' from dual;

edited Jun 27, 2013 at 6:21

answered Jun 27, 2013 at 6:14

Oleg Mikheev

17.5k16 gold badges77 silver badges98 bronze badges

Collectives™ on Stack Overflow

Running Oracle script from bash

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest