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);
Answer to your second question: it seems you didn't enable output. Do it using SET SERVEROUTPUT ON SIZE 1000000 (which is maximum in Oracle 7; don't know for above versions. If you omit size, you'll run out of buffer if your script often uses dbms_output.).
Didn't quite understand the first question ... how to use cursors? A simple example would be
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;
Thanks for the answer...i'll try on to add the size parameter.i have not setting this value.
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:
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
cursor c1 is
select empno, ename
for c1r in c1 loop
update emp set
ename = 'Moustifay'
where empno = c1r.empno;
c1 catch the value: Fred, Jack, Bill
I want to return this values to my shell script so that i can manipulate them?