Passing values

[Mousstifay]

Hi!

I have in fact two questions...I am a beginner in the field an i want to know how to pass values return by a cursor in PL/SQL functions to my shell script and indeed threat them one by one ...
My second question is that i am not able to write comments during my shell script using this the dbms_output.put_line,why?
Here is how i use it:
dbms_output.put_line('numero de chargement :'||rec_chg_dmt.chg_num);

thanks for the help!

 

[jad]

second one might be due to the fact that you have to 'set serveroutput on' before you can output dbms_output statements.


to answer the first one you'll need to give us more info

 

[Mousstifay]

Thanks for the answer.
Regards with my first question, i'll explain it using your example:
I have already created the cursor.What i expect to do is the return all the value to my shell script:
For example:

I call this procedure from my shell this way
. tr_appel_sqlplus.sh "pkg_tr_chargement.main_chargemement_trav"

create or replace procedure my_example
is
cursor c1 is
select empno, ename
from emp;
begin
for c1r in c1 loop
dbms_output.put_line(c1r.ename);
update emp set
ename = 'Moustifay'
where empno = c1r.empno;
end loop;
end;

c1 catch the value: Fred, Jack, Bill
I want to return this values to my shell script so that i can manipulate them?

Hoping i've been clearest.

 

[sem]

What does tr_appel_sqlplus.sh do?

Regards, Dima

http://seminihin.narod.ru

 

[Mousstifay]

it just create a ".sql" file i launch using sqlplus.

it contains this lines:

echo "set serveroutput ON SIZE 1000000" > tr_appel_sqlplus.sql
echo "set echo off" >> tr_appel_sqlplus.sql
echo "set termout off" >> tr_appel_sqlplus.sql
echo "set feedback off" >> tr_appel_sqlplus.sql
echo "spool tr_retour_sqlplus.sh" >> tr_appel_sqlplus.sql
echo "declare" >> tr_appel_sqlplus.sql
echo " retour number;" >> tr_appel_sqlplus.sql
echo "begin" >> tr_appel_sqlplus.sql
echo " retour := "$1";" >> tr_appel_sqlplus.sql
echo " dbms_output.put_line('export RETOUR_SQLPLUS='||to_char(retour));" >> tr_appel_sqlplus.sql
echo "end;" >> tr_appel_sqlplus.sql
echo "/" >> tr_appel_sqlplus.sql
echo "spool off" >> tr_appel_sqlplus.sql
echo "exit" >> tr_appel_sqlplus.sql

sqlplus -s $CHAINE_CONNEXION @tr_appel_sqlplus.sql

 

[jad]

so basically you are doing:

set serveroutput ON SIZE 1000000
set echo off
set termout off
set feedback off
spool tr_retour_sqlplus.sh
declare
  retour number;
begin
  retour := pkg_tr_chargement.main_chargemement_trav;
  dbms_output.put_line('export RETOUR_SQLPLUS='||to_char(retour));
end;
/
spool off
exit

to produce a script (tr_retour_sqlplus.sh) which probably looks like:

export RETOUR_SQLPLUS=<Number>

where does:

create or replace procedure my_example is
    cursor c1 is
        select empno, ename
            from emp;
begin
    for c1r in c1 loop
        dbms_output.put_line(c1r.ename);
        update emp
            set ename = 'Moustifay'
            where empno = c1r.empno;
    end loop;
end my_example;

which will output all the current ename's of emp, and then overwrite them all with 'Moustifay'

where does this cursor come into it?