PLSQL from unix shell

[Vogra]

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.

[Jean-Luc Oudart · Accepted solution]

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

[Indira Aramandla]

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

[Eric Antunes]

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

[Yogeeraj_1]

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