cancel
Showing results for 
Search instead for 
Did you mean: 

PLSQL from unix shell

SOLVED
Go to solution
Vogra
Regular Advisor

PLSQL from unix shell

Hi all!
I need to execute a PLSQL from unix shell script. Like this:

/usr/bin/su - usuopt -c svrmgrl <connect usuaaa/passwd@bdbd
declare
p_resposta varchar2(100);
begin
carga_geral_reducao_carbono(p_resposta);
end;
EOF

I need to know the value of p_resposta after execution. How can I get it end print, or store in a variable ?

Thanx,
Lima.
We are spirits in the material world
4 REPLIES
Jean-Luc Oudart
Honored Contributor
Solution

Re: PLSQL from unix shell

Hi

you can display the value with dbms_output.put_line
e.g.
-- server output buffer resize :
dbms_output.enable(20000);
dbms_output.put_line(p_resposta = '||p_resposta );
Regards
Jean-Luc
fiat lux
Indira Aramandla
Honored Contributor

Re: PLSQL from unix shell

Hi Lima,

You would also need to turn on the serveroutput

SQL> set serverpoutput on.

To aid in the development of PL/SQL programs, stored procedures, and triggers, Oracle provides a public package named DBMS_OUTPUT for displaying debugging Information

In order to use the package with these two tools, you must set the 'serveroutput' before any info can be displayed to the screen. Use the following command in SQL*Plus or SQL*DBA:

SQL> set serveroutput on

Note that 'serveroutput' is one word. If serveroutput is not turned on, nothing will be displayed even if you call the DBMS_OUTPUT.ENABLE packaged procedure.

Indira A
Never give up, Keep Trying
Eric Antunes
Honored Contributor

Re: PLSQL from unix shell

Hi Lima,

To be sure your p_resposta variable always have a value (not null) you can declare p_resposta and initiate it at the same time:

p_resposta varchar2(100) := 'NULO';

The value of p_resposta is affected after you invoke carga_geral_reducao_carbono. So after that you can use the dbms_output package to know its value...

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Yogeeraj_1
Honored Contributor

Re: PLSQL from unix shell

hi

i would also add 2 things:
1. Use SQLPLUS instead of svrmgrl
2. you can also spool the result to a file and later verify it when you exit

hopw this helps too!
regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)