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
To keep the forums high quality for all users, please take the time to format your posts correctly.
First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags
```text
and
```
by hand.)
Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.
Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.
I tried for ages, but couldn't get your example to work - especially regarding the ref cursor.
Assuming you're not asking about cursors, and that part is working for you, I would change the select statement to output the data in delimeted format. i.e.
Name 1|Desc 1
Name 2|Desc 2
Name 3|Desc 3
The script that you posted doesn't output the data (i.e. with dbms_output), so you need to add that to your loop, otherwise nothing will be spooled.
Then you can read the spool file and process the data with something like...
while read LINE; do
[ -z "$LINE" ] && break
NAME=$(echo $LINE | cut -d"|" -f 1)
DESC=$(echo $LINE | cut -d"|" -f 2)
echo "Name is $NAME Desc is $DESC"
done < spool.txt
Output:
Name is Name 1 Desc is Desc 1
Name is Name 2 Desc is Desc 2
Name is Name 3 Desc is Desc 3
REFCURSOR is a pointer to an open cursor in your current process.
You did nothing to open the cursor. You need to invoke the stored procedure, then "aim" your refcursor at the return value of the SP.
---------- Post updated 08-11-09 at 08:32 AM ---------- Previous update was 08-10-09 at 06:33 PM ----------
Hi ,
My question is on cursors only. your are write that we can read from the file. But first question arises how to write the records in the cursor to the file in the k shell script.