Call sql script from UNIX shell script

Shell Programming and Scripting
1

I know this question is out there in many forums, but I tried all the combinations in vain.

I'm basically trying to call a sql script from a shell script.

Below is my sql script (plsql.sql)

DELCARE
v_empno NUMBER := '&empno';
BEGIN
select ename,sal from emp where empno = v_empno;
dbms_output.put_line('Inside the plsql file');
END;

This is my unix shell script - I'm caling the plsql.sql file with the parameter passed(97882). I don't get any output. at least I should be able to view the dbms output if not for the sql query inside the sql script.

#!/usr/bin/ksh
sqlplus -s sam/olo01 << HERE
@plsql.sql 97882;
HERE

What am I missing here ? It doesn't seem to work. I tried removing the <<HERE condition, but it when executing the shell script, it keeps prompting for an input(I am not sure why) and never returns the control to the bash screen(-bash-3.2-$)

2

Hello,

please use code tags when posting code or sample data.

There are several problems I see with your code:
The pl/sql-script does not know what &empno is. The arguments you pass are addressed like positional parameters in a shell script.
pl/sql is designed for background processing. Queries inside pl/sql-blocks are never displayed. Put the query outside the pl/sql-block to see its result.
You have to enable serveroutput to see the output of dbms_output.put_line.
So your plsql.sql script should look like this:

select ename,sal from emp where empno = &1;
set serveroutput on
BEGIN
   dbms_output.put_line('Inside the plsql file');
END;
3

the DBMS_OUTPUT pacakge requires:

set serveroutput on size 100000

in order to write to the terminal - the 100000 is the max number of bytes displayed.
This is arbitrary - but do not make it too small.
The output only appears after the sql has completed.

Note: DECLARE is misspelled in your example.

cero's code will work, I think. It has to be run using sqlplus from the command line. It cannot be used in a trigger because triggers and stored procedures do not have a controlling terminal when they are executing.

4

Can I also suggest that you take the credentials out of your sqlplus command line. Anyone running a simple ps will be able to see them whilst your database connection is active.

It might only be a short time, but if this account is a DBA (which I'm guessing that it is) then you are effectively shouting the out the number for a combination lock on your most secure safe. If no-one is listening, then you get away with it. If someone hears, it depends on their integrity if they do something with it.

Robin