UNIX for Advanced & Expert Users

Hi,

My Oracle Stored procedure returns sys_refcursor to shell script. I have to iterate through it in script and use those retrieved values further in my script. I am using K Shell Scrpting.

Stored Procedure is:

create or replace
PROCEDURE p_test(job_id IN VARCHAR2, c1 OUT SYS_REFCURSOR)
AS

job_nm PROXY_JOB_DESCRIPTIONS.JOB_NAME% TYPE;
job_ds PROXY_JOB_DESCRIPTIONS.JOB_DESC% TYPE;


BEGIN

OPEN c1 FOR SELECT JOB_NAME, JOB_DESC FROM proxy_job_descriptions WHERE job_id = job_id;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('* No records retrived *');


END p_test;


Script:

Code:
#!/bin/ksh

SPOOL_FILE=/usr/home/dfusr/backup/log/spool.txt

sqlplus -S $DB 1>>log/test.log 2>>log/test.err << EOF
spool $SPOOL_FILE
SET DEFINE OFF;
SET SERVEROUT ON;

Declare
ln_desc REFCURSOR;
jbname varchar2(128);
jbdesc varchar2(128);
BEGIN
P_TEST ('1', ln_desc);

OPEN ln_desc;
LOOP
FETCH ln_desc INTO jbname, jbdesc;
EXIT WHEN ln_desc%NOTFOUND;
END LOOP;
CLOSE ln_desc;
END;
/
EOF
Please help me how can i use cursor in script as i am new to unix......

Thanks-
Raj