Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Help need urgently for oracle cursors in k shell scripting
Top Forums Shell Programming and Scripting Help need urgently for oracle cursors in k shell scripting Post 302342243 by rajeshorpu on Saturday 8th of August 2009 04:13:26 AM
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
 

10 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

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

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

LEARN ABOUT OSF1

log

log(8)							      System Manager's Manual							    log(8)

NAME

       log - Records input and output from a program

SYNOPSIS

       /usr/sbin/log <logfile> <command>

OPERANDS

       The file in which to record the interaction being logged.  The command to execute.

DESCRIPTION

       The  log  program  runs <command> and logs the input to and output from <command> to the <logfile> file.  Input and output are logged until
       <command> exits, the log program exits, and the exit status of <command> is returned.

       The log program is used by the system  installation  procedure  and  the  it(8)	command  to  create  the  /var/adm/smlogs/install.log  and
       /var/adm/smlogs/it.log installation log files.

RESTRICTIONS

       Because	the  log  program is used in the installation standalone environment, program size was the greatest concern in its implementation.
       The log program does not search for the PATH variable to locate <command> and error messages are terse.

       The log program causes <command> to take standard input from and write standard output and standard error to  UNIX  pipes.   Some  commands
       will  not  be able to operate in this environment; therefore, it is suggested that you use the script(1) command instead.  UNIX shells will
       not issue prompts when run from log unless the shell is started with an explicit interactive switch (-i for most shells).  For example, log
       foo.tmp /sbin/sh -i

       In the previous example, foo.tmp is the name of <logfile>.

       The  log  program  intercepts  end-of-file (usually Ctrl/d).  Therefore programs which normally receive end-of-file as an exit command must
       exit by some other means.

ERRORS

       Log open error

   Explanation:
       The log program was unable to open <logfile>. Verify that the directory exists and that ownerships and permissions are set correctly.

       Exec Error

   Explanation:
       The log program was unable to execute <command>. Verify that you specified a full pathname for <command> and  that  <command>  is  an  exe-
       cutable file.

       Fork Error

   Explanation:
       The log program was unable to create one of the processes it requires to log data.

SEE ALSO

       Commands: it(8), script(1)

																	    log(8)
All times are GMT -4. The time now is 09:59 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy