Login or Register to Ask a Question and Join Our 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
Login or Register to Ask a Question

Previous Thread | Next Thread

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
Login or Register to Ask a Question
DBIx::Class::Storage::DBI::Oracle::WhereJoins(3)	User Contributed Perl Documentation	  DBIx::Class::Storage::DBI::Oracle::WhereJoins(3)

NAME
DBIx::Class::Storage::DBI::Oracle::WhereJoins - Oracle joins in WHERE syntax support (instead of ANSI). PURPOSE
This module is used with Oracle < 9.0 due to lack of support for standard ANSI join syntax. SYNOPSIS
DBIx::Class should automagically detect Oracle and use this module with no work from you. DESCRIPTION
This class implements Oracle's WhereJoin support. Instead of: SELECT x FROM y JOIN z ON y.id = z.id It will write: SELECT x FROM y, z WHERE y.id = z.id It should properly support left joins, and right joins. Full outer joins are not possible due to the fact that Oracle requires the entire query be written to union the results of a left and right join, and by the time this module is called to create the where query and table definition part of the SQL query, it's already too late. METHODS
See DBIx::Class::SQLMaker::OracleJoins for implementation details. BUGS
Does not support full outer joins. Probably lots more. SEE ALSO
DBIx::Class::SQLMaker DBIx::Class::SQLMaker::OracleJoins DBIx::Class::Storage::DBI::Oracle::Generic DBIx::Class AUTHOR
Justin Wheeler "<jwheeler@datademons.com>" CONTRIBUTORS
David Jack Olrik "<djo@cpan.org>" LICENSE
This module is licensed under the same terms as Perl itself. perl v5.18.2 2013-07-12 DBIx::Class::Storage::DBI::Oracle::WhereJoins(3)

Featured Tech Videos