Visit Our UNIX and Linux User Community


Need Help for Using Oracle Cursors in Shell Script Korn


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Need Help for Using Oracle Cursors in Shell Script Korn
# 1  
Old 08-10-2009
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)
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

Previous Thread | Next Thread
Test Your Knowledge in Computers #835
Difficulty: Medium
SOAPjr is a protocol specification for exchanging structured information in the implementation of web services in computer networks.
True or False?

10 More Discussions You Might Find Interesting

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

2. Programming

pass value from Oracle sql to Korn shell

Hi All , I am trying to pass a value from sqlplus to korn shell . There is a table tab1 in Oracle that has a column userdate. I need to pass the userdate to the korn shell . This is what I am doing . VALUE=`sqlplus -silent username/password << END set pagesize 0 feedback off verify off... (14 Replies)
Discussion started by: megha2525
14 Replies

3. Shell Programming and Scripting

Korn Script to connect and query oracle database

I've been sent the following script to finish. It's supposed to connect to an oracle database, query it, and send an email if the query result value is one or more. Currently it isn't connecting properly, just giving the following error: ERROR: ORA-01017: invalid username/password; logon denied... (2 Replies)
Discussion started by: jackmorgan2007
2 Replies

4. Shell Programming and Scripting

Korn shell program to parse CSV text file and insert values into Oracle database

Enclosed is comma separated text file. I need to write a korn shell program that will parse the text file and insert the values into Oracle database. I need to write the korn shell program on Red Hat Enterprise Linux server. Oracle database is 10g. (15 Replies)
Discussion started by: shellguy
15 Replies

5. Shell Programming and Scripting

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

6. Shell Programming and Scripting

Korn Shell and Oracle

Hi Guys, I haven't worked on oracle much but I have a situation where I have to do bdf in all the servers and insert that information into oracle table. I have already created table which have 7 columns, I can insert manually but I dont know how to insert that using Korn shell. SERVER_ID ... (9 Replies)
Discussion started by: pareshan
9 Replies

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

8. UNIX Desktop Questions & Answers

korn shell script

hi all i am writing the korn shell script. i have a SQL script which gives me the folowing output DSA.WLG.20050713211544.20051025.20050713211544 28991 1130198400 DSA.WLG.20050713211544.20051025.20050713211544 25881 1130198400 DSA.WLG.20050711210100.20051025.20050711210100 25881 ... (3 Replies)
Discussion started by: pavan_test
3 Replies

9. UNIX for Dummies Questions & Answers

Executing Oracle Stored Procs from Korn Shell

Can someone tell me how to execute an Oracle Stored Procedure from a Korn Shell Script. Previously, I'm able to execute standard sql using the following:- The_Output=`sqlplus................. << EOF select count(*) from abc / ... (3 Replies)
Discussion started by: Vinny_Mitchell
3 Replies

10. UNIX for Advanced & Expert Users

Oracle To Korn Shell Array

I'm attempting to populate an array in ksh using the following command: set -A $(SELECT_INVOICE | sed '/^$/d') SELECT_INVOICE is a function that executes the SQL query. Problem: Some of the invoice numbers have alpha characters with spaces(example: OVEN MICRO). The Korn shell is treating... (1 Reply)
Discussion started by: kdst
1 Replies
CPANPLUS::Shell(3perl)					 Perl Programmers Reference Guide				    CPANPLUS::Shell(3perl)

NAME
CPANPLUS::Shell - base class for CPANPLUS shells SYNOPSIS
use CPANPLUS::Shell; # load the shell indicated by your # config -- defaults to # CPANPLUS::Shell::Default use CPANPLUS::Shell qw[Classic] # load CPANPLUS::Shell::Classic; my $ui = CPANPLUS::Shell->new(); my $name = $ui->which; # Find out what shell you loaded $ui->shell; # run the ui shell DESCRIPTION
This module is the generic loading (and base class) for all "CPANPLUS" shells. Through this module you can load any installed "CPANPLUS" shell. Just about all the functionality is provided by the shell that you have loaded, and not by this class (which merely functions as a generic loading class), so please consult the documentation of your shell of choice. BUG REPORTS
Please report bugs or other issues to <bug-cpanplus@rt.cpan.org<gt>. AUTHOR
This module by Jos Boumans <kane@cpan.org>. COPYRIGHT
The CPAN++ interface (of which this module is a part of) is copyright (c) 2001 - 2007, Jos Boumans <kane@cpan.org>. All rights reserved. This library is free software; you may redistribute and/or modify it under the same terms as Perl itself. SEE ALSO
CPANPLUS::Shell::Default, CPANPLUS::Shell::Classic, cpanp perl v5.14.2 2014-09-29 CPANPLUS::Shell(3perl)

Featured Tech Videos