passing variable to oracle procedure


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting passing variable to oracle procedure
# 1  
Old 04-21-2009
passing variable to oracle procedure

using the script below I want to pass a parameters thorugh my sql
call(@/unixsxxx/xxxx/helpenv.sql emptab ) as input into an oracle procedure xxxx_package.proc1(%1,emptab);. I tried %1 but it does not work. Any suggestions.

#!bin/ksh
set -x
#
export ORACLE_HOME=/xxxx/apps/oracle/dist/10.2.0
export ORACLE_SID=xxxxx
export ORA_USERNAME=xxxxxx
export ORA_PASSWORD=xxxxxxxx
export logfileTMP=/xxxxxxx/xxx/xxxxx/lewis.log
export TNS_ADMIN=/usr/local/bin/oracle
export PATH=$PATH:$ORACLE_HOME/bin
export emptab=abcde
sqlplus -S ${ORA_USERNAME}\/${ORA_PASSWORD}@${ORACLE_SID} <<-!2>/dev/null 1>>$logfileTMP
@/unixsxxx/xxxx/helpenv.sql emptab
exit
/

helpenv.sql

set serveroutput onsize1000000
SET ECHO on;
declare
type rcursor isrefcursor;
emptab rcursor
;
emprec
PS_xxxx_ENV_VARS%rowtype;
begin
xxxx_package.proc1(%1,emptab);
loop
fetch emptab into emprec;
exitwhen emptab%notfound;
IF emprec.COL_WIDTH =2then
dbms_output.put_line('export' || emprec.LABEL_ID || '= ' || emprec.STRING_TEXT );
else
dbms_output.put_line('export' || emprec.LABEL_ID || '= ' || emprec.STRING_TEXT || emprec.STRING_ID );
endif;
endloop;

end;
/
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Passing output parameter(Oracle) to variable in ksh Script

Hello, I have researched and tried many way to pass OUT parameter to be stored in variable in KSH Script.Still not success, please help. Here is my Store Procedure. create procedure testout3(v_in varchar2,v_out OUT integer) as begin v_out := 1; end; Here is my testvout.ksh #!/bin/ksh... (1 Reply)
Discussion started by: palita2601
1 Replies

2. Shell Programming and Scripting

Passing variable from file to Oracle

cat a1 scott robert tom test script : #!/usr/bin/ksh for NAME in `cat a1` do VALUE=`sqlplus -silent "nobody/bobody01@testq" <<END set pagesize 0 feedback off verify off heading off echo off select username from dba_users where username=upper('$NAME'); END` if ; then echo... (3 Replies)
Discussion started by: jhonnyrip
3 Replies

3. Shell Programming and Scripting

Procedure not passing variables

Hi all, I just started some basic Perl programming and have been experimenting with some basic commands and bumped into and issue. Basically, I have a procedure which retrieves a list of names from a file named file. Then the program asks the user to enter a name and compares it to the list... (2 Replies)
Discussion started by: dsaliba
2 Replies

4. Programming

Oracle Variable Passing Test

Hi, I am trying to get the oracle variables and pass the values in sql placed in procedure. VARIABLE vstat='ASDS,FGDS,VCGD,VCXC' Query : select distinct dept from College where section in ('C','D') AND CODES ='' AND NAMES IN ('RAJ','SAM'); I want CODES values to be taken from vstat... (1 Reply)
Discussion started by: Perlbaby
1 Replies

5. Programming

Oracle Procedure approach

HI All , I am new to oracle procedures. Please help me for the approach to satify the requirement. I need to create procedures. with parameters passed ( say report,type,identities,country ) It should also call sql query within the procedures and passed parameters should be used in where clause... (2 Replies)
Discussion started by: Perlbaby
2 Replies

6. Shell Programming and Scripting

Passing unix variable to oracle parameters

Please help me how to pass some unix vairable to oracle. I have used below , but not displaying passed (inval) value. calling() { sqlplus -s $1/$2@$3 <<EOF begin exec call_sql($4); end; exit EOF } calling user pwd inst value1... (17 Replies)
Discussion started by: Jairaj
17 Replies

7. Shell Programming and Scripting

Passing a value to stored procedure from unix shell script

Hi Dudes :) I want a unix shell script to pass value to SQL stored procedure. Below is the procedure declare res varchar2(10); begin odm_load_check('PRE_SANITY',res); dbms_output.put_line(res); end; select * from error_log; truncate table error_log; select * from test; (1 Reply)
Discussion started by: shirdi
1 Replies

8. Shell Programming and Scripting

passing parameter 4m shell script to a DB stored procedure

hi all please tell me how to pass parameters 4m shell script to a DataBase stored procedure. To be specific i have sybase DB. i mean i want the syntax of the command.. how to connect to DB, pass user id and password, pass the required parameter to SP.. .. need ur help frnds.. hema (0 Replies)
Discussion started by: hema2026
0 Replies

9. Shell Programming and Scripting

Passing parameters form unix to Oracle procedure

Hi, I have screen which was desined in PL/SQL Catridges in apps. In that screen some enterable fields these values r the passing parameters to create value sets, functions, menus etc in apps by using front end screens. Now in that screen i have a button. when i click that button it have to... (0 Replies)
Discussion started by: rajasekharamy
0 Replies

10. UNIX for Advanced & Expert Users

Oracle stored procedure.

I am using sqlplus. I have the stored procedure name. How can i print the stored procedure content? (2 Replies)
Discussion started by: kamil
2 Replies
Login or Register to Ask a Question