Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help need urgently for oracle cursors in k shell scripting

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help need urgently for oracle cursors in k shell scripting
# 1  
Old 08-08-2009
Help need urgently for oracle cursors in k shell scripting
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
Last edited by DukeNuke2; 08-08-2009 at 06:07 AM.. Reason: added code tags
# 2  
Old 08-08-2009
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 [code] and [/code] 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.

Thank You.

The UNIX and Linux Forums
# 3  
Old 08-08-2009
Hi.

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.

Code:
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...
Code:
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

# 4  
Old 08-08-2009
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.

Code:
p_test(42, ln_desc);  -- I made up a job id

# 5  
Old 08-11-2009
Quote:
Originally Posted by jim mcnamara
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.

Code:
p_test(42, ln_desc);  -- I made up a job id

You mean
ln_desc:=P_TEST ('1', ln_desc);

I tried the same but not worked.

Can you please post the code or any link if any.

---------- 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.

Please post me the code if any or any link.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Forum Support Area for Unregistered Users & Account Problems

Creation of Oracle table through shell scripting

Hi, I am trying to create a table through shell scripting . I used a command sqlplus -s to connect to sqlplus and user as sysdba. but there is a problem in it. can anyone please solve this . #!/bin/bash $(`sqlplus -s / as sysdba <<eof create table sample (id,int); insert into sample... (1 Reply)
Discussion started by: Unregistered
1 Replies

2. UNIX for Dummies Questions & Answers

Oracle cursors in UNIX shell scripting

Hello, I need to get all the members information from an oracle table whose flag value is enabled. Later on i need to perform several computation based upon the flag value and other columns. For example, Member ID Flag Frequency date 1 ... (2 Replies)
Discussion started by: Krishraj
2 Replies

3. Shell Programming and Scripting

How to update a Oracle table through shell scripting?

My Code is get_week_date() { `sqlplus -s ${DQM_SQL_LOGON}@${DQM_SID} << EOF SET ECHO OFF SET FEEDBACK OFF SET PAGES 0 SET SERVEROUTPUT ON SET VERIFY OFF SET TRIMSPOOL ON (update file_level_qc fq set FQ.DATA_FILE_NAME='Hyvee_Pharmacy_Solutions_201304_v1.txt'... (2 Replies)
Discussion started by: karthick.cho
2 Replies

4. Shell Programming and Scripting

Help needed for shell scripting for oracle.

Hi, Please see contains both files created for automating the data from oracle through shell. 1)a_p.ksh #!/bin/ksh LOG=/home/A_P.log MESSAGE=/home/MESSAGE_A_P.txt mail_list=/home/AP_MAIL_LIST.txt data=/home/spooled_A_P.log echo "`date` Starting execution for A_P COUNT" > $LOG ... (2 Replies)
Discussion started by: fidelis
2 Replies

5. UNIX for Advanced & Expert Users

Need Help for Using Oracle Cursors in Shell Script Korn

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)... (0 Replies)
Discussion started by: rajeshorpu
0 Replies

6. UNIX for Dummies Questions & Answers

Shell scripting+connect to oracle database+spooling

Hi! Everyone I am new to the shell scripting basically.I have been asked to create a shell script that connect to a oracle database to read data from a particular schema then spool it into a csv file then email to customer. Can anybody let me know how to go about that. I have create... (14 Replies)
Discussion started by: Mr Mo
14 Replies

7. Shell Programming and Scripting

bash shell scripting error need help urgently

#! /bin/sh abcd = "Hello world" if then echo $abcd fi i got error message that line3 : abcd: command not found line5 : [0: command not found line5 : [1: command not found i have no idea why i got this message. Can some one help me ??? (6 Replies)
Discussion started by: bonosungho
6 Replies

8. Shell Programming and Scripting

Can we write SQL cursors from shell script

Hi I connected to oracle database through shell script My requiremnt is to get more than one record from the sql query. How can i do in unix to get multiple records. (1 Reply)
Discussion started by: vyagh
1 Replies

9. Shell Programming and Scripting

UNIX shell scripting for retrieving from oracle

Hello folks, Please find the below code:(sample5.sh -> filename) echo "Selecting dat afrom Cause code" echo "set appinfo Causecode $preamble set serveroutput on size 10000 select * from RMI003_CAUSE_CODE /" | sqlplus -S $username@$hoststring/$password >> test2.dat When i tried executing... (5 Replies)
Discussion started by: sundar_ravi4
5 Replies

10. Shell Programming and Scripting

Shell Scripting with Oracle...

Hi guys, I have searched all the forums and searched the net as much as possible, but I carn't find any tutorials about shell scripting with Oracle. Has anyone come across any? Thanks for reading and in advance of posts, B14... (2 Replies)
Discussion started by: B14speedfreak
2 Replies
Login or Register to Ask a Question