How to pass value from plsql script to unix bash?

Shell Programming and Scripting
1

Hi
This is my bash script.i am calling validation.sql and passing a value to it using ${flds[1]}.
i want the cnt variable in plsql script to be passed to unix.

LOADREC=`sqlplus -s $ORACLE_USR <<-EOF
spool $ORACLE_LOG_FILE;
echo "barani"
@validation.sql #calling the plsql script
${flds[1]} #passing value to the script
 
spool off;
EOF`
 
 
this is my validation.sql script
 
 
 
SET SERVEROUTPUT ON ;
DECLARE 
 
job_name varchar(10) :='&fedexjobname';
 
cnt number; # the value of the variabel should be pass to unix 
 
BEGIN 
 
 
dbms_output.put_line(job_name);
 
 
select count(*) into cnt from TB_AUDIT
where Batchnumber=job_name ; 
 
IF cnt = 0
THEN 
dbms_output.put_line('job was not processed earlier');
ELSE
dbms_output.put_line('job was processed earlier');
END IF; 
EXCEPTION
WHEN OTHERS THEN 
raise_application_error(-20000, 'validation.sql - Unknown Exception Raised: '||SQLCODE||' '||SQLERRM);
ROLLBACK;
END;
/

Thanks.

2

IMake use of a procedure or a function in the plsql script. Return the value of count from the function as a return value or an OUT parameter for a procedure. Try it out and let us know what you come up with.

cheers,
Devaraj Takhellambam

3

If you want just the value of the cnt variable,

  1. you can use a stored procedure with OUT parameter.
  2. A Function which returns its value.

If you dont want to use the above 2 methods. There one more method.
Where in you can direct the output of the SQL via dbms_output.put_line statement to a file and then you can read from that file.

Let me know your feasibility, then i will give you more details.

4

Hi karthik,

if i use a function then how to call the function from bash.

the fuction is a parametised.

and how can get return value of the function in bash.

Thanks.

5

Try to print the value of cnt in the QL block and see if you get that value from the unix variable LOADREC.

SET SERVEROUTPUT ON ;
DECLARE
job_name varchar(10) :='&fedexjobname';
cnt number; # the value of the variabel should be pass to unix
BEGIN
select count(*) into cnt from TB_AUDIT
where Batchnumber=job_name ;
dbms_output.put_line(cnt);

IF cnt = 0
THEN
dbms_output.put_line('job was not processed earlier');
ELSE
dbms_output.put_line('job was processed earlier');
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'validation.sql - Unknown Exception Raised: '||SQLCODE||' '||SQLERRM);
ROLLBACK;
END;

echo $LOADREC
6

Barani,

i was thinking to declare the parameter that you want as output as OUT parameter and calling the function. But yeah... once you log into SQL, taking the parameter from SQL session to UNIX session is not possible.

OK... Leave that.

Lets try the other one.

Direct the output of the SQL session, to a file throught DBMS_OUTPUT.PUT_LINE statements.
and then read the variable from that file.

What do you think abt this one ?

7

Barani,

Oracles exit statment lets you return a value to the calling shell.
Your validation.sql commandfile could be rewritten like this:

SET SERVEROUTPUT ON ;
VARIABLE ret number;
DECLARE 
   job_name varchar(10) :='&fedexjobname';
   cnt number; # the value of the variabel should be pass to unix 
BEGIN 
  dbms_output.put_line(job_name);
  select count(*) into cnt from TB_AUDIT where Batchnumber=job_name ; 
  :ret := cnt;
  IF cnt = 0
  THEN 
    dbms_output.put_line('job was not processed earlier');
  ELSE
    dbms_output.put_line('job was processed earlier');
  END IF; 
EXCEPTION
  WHEN OTHERS THEN 
    raise_application_error(-20000, 'validation.sql - Unknown Exception Raised: '||SQLCODE||' '||SQLERRM);
    ROLLBACK;
END;
/
exit :ret