Passing unix variable to oracle parameters


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing unix variable to oracle parameters
# 1  
Old 04-08-2010
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.

Code:
calling()
{

sqlplus -s  $1/$2@$3 <<EOF
begin
exec call_sql($4);
end;
exit
EOF
}

calling  user pwd inst value1

-----------------------------------------------------------------

Procedure is :

Code:
create or replace procedure call_sql(inval in varchar2) as
begin
dbms_output.put_line('value is '||inval);
end;


Last edited by pludi; 04-08-2010 at 10:10 AM.. Reason: code tags, please...
# 2  
Old 04-08-2010
Single quotes around the $4 token, such as '$4'...
# 3  
Old 04-08-2010
Thanks Curleb. but script is not calling procedure..How to call this...?
# 4  
Old 04-08-2010
What IS it producing? Note you don't appear to be redirecting your output within this calling() function so your script might be doing it...or it might not.
# 5  
Old 04-08-2010
Help me how to proceed.?
# 6  
Old 04-08-2010
Quote:
Originally Posted by Jairaj
...
I have used below , but not displaying passed (inval) value.

Code:
calling()
{
 
sqlplus -s  $1/$2@$3 <<EOF
begin
exec call_sql($4);
end;
exit
EOF
}
 
calling  user pwd inst value1

...
Too many things are wrong here -

1) "exec" or "execute" is a sqlplus command that wraps the "begin ... end" block around whatever follows it. So you cannot put a "begin ... end" block around "exec" itself.

2) If the input parameter is a string, then add the string delimiter to it, otherwise Oracle will consider it an identifier.

3) Of course, just because the input parameter is declared a VARCHAR2 in the procedure signature doesn't mean that you cannot pass a number/integer etc. Oracle does an implicit conversion of the passed value to VARCHAR2 in that case.

4) If the input parameter is a string that has multiple words separated by whitespace, then enclose it within double-quotes in the shell script, at the function call, that is.

HTH,
tyler_durden
# 7  
Old 04-08-2010
Hi tyler,

Thanks for your input. I have tried my level.

Code:
file_cnt=`wc -l $1 | awk '{print $1}'`
sqlplus -s  uname/pwd@dbins << EOF
select count(1) into :t_cnt from $2;
insert into t_table(f_name, t_name, f_cnt,loaded) values ('$1','$2',$file_cnt,:t_cnt);
commit;
exit;
EOF

Code:
sh o2.sh filename table_name

  COUNT(1)
----------
      3448

SP2-0552: Bind variable "T_CNT" not declared.

Commit complete.

calling like below:
sh o2.sh file_name table_name

Please tell me how to insert t_cnt to oracle.

edit by bakunin: you surely have just forgotten the code-tags. I inserted them for you.

---------- Post updated at 12:00 PM ---------- Previous update was at 11:05 AM ----------

It will be great if anybody provide solution my problem.

Advance Thanks.

Last edited by bakunin; 04-08-2010 at 01:40 PM..
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. Programming

Remote login UNIX box from java passing parameters to the custom script called in the profile

Hello Good Day / Guten Tag.... I have to login the server and the user profile contains some scripts which need the inputs to be taken from the keyboard. So I use the method to conn.authenticateWithKeyboardInteractive(username, new InteractiveCallback() { public String... (1 Reply)
Discussion started by: Sanalkumaran
1 Replies

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

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. Shell Programming and Scripting

Passing Variable Parameters (C shell)

I am trying to execute a copy command via shell script. However, on occassion, 2 or more files need to copied. How do I code for the multiple arguments? Does it matter how the files are delimited? Example: I have a script to copy files from 1 dir to another called duplicate.csh In most... (1 Reply)
Discussion started by: CKT_newbie88
1 Replies

6. Shell Programming and Scripting

Passing wildcard parameters to find via a variable

I have a script to fix permissions which is made up of blocks like: FS_ROOT=/home/shared/Photos FS_EXCLUDE=( \( -path */.webviews -o -path */.thumbnails \) -prune -o ) find $FS_ROOT ${FS_EXCLUDE} -type d -not -perm 2770 -exec chmod 2770 "{}" \; That fragment works as expected, but no matter... (3 Replies)
Discussion started by: mij
3 Replies

7. Shell Programming and Scripting

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

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

9. Shell Programming and Scripting

passing oracle parameters back to Shell

Hi All, Does anyone have any solutions for passing back multiple variables back to the SHELL from a call to an ORACLE procedure: eg #username='scott' #password='tiger' #database='orcl' username='ITGCD03D03' password='tC5epIew' database='ITGCD03D' sqlplus -s... (4 Replies)
Discussion started by: satnamx
4 Replies

10. Shell Programming and Scripting

Passing arrays to oracle from unix

Hi all Iam trying to send an array to oracle procedure from unix. Iam writing a program in K Shell to pass this array to oracle. Is it possible. Please advice thanks Krishna (7 Replies)
Discussion started by: krishnasai
7 Replies
Login or Register to Ask a Question