How to Dynamically Pass Parameter to plsql Function & Capture its Output Value in a Shell Variable?

 
Thread Tools Search this Thread
Homework and Emergencies Homework & Coursework Questions How to Dynamically Pass Parameter to plsql Function & Capture its Output Value in a Shell Variable?
# 1  
Old 08-08-2017
How to Dynamically Pass Parameter to plsql Function & Capture its Output Value in a Shell Variable?

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted!

1. The problem statement, all variables and given/known data:



2. Relevant commands, code, scripts, algorithms:


Code:
#! /bin/ksh
 
  
  v="ORG_ID"
  
 get_next_xfer_id ()
{
NEXT_ID=$(sqlplus -s $1 <<\!
  set pages 0 feed off
  select bolinf.brok_fndload_pkg.brok_fndload_profile_chk('$2')  
 from dual;
!
)

}
  
  
 get_next_xfer_id $1 $v
 

echo $NEXT_ID

When I am passing like this function it is not working.


Code:
#! /bin/ksh
 
  
  
  
 get_next_xfer_id ()
{
NEXT_ID=$(sqlplus -s $1 <<\!
  set pages 0 feed off
  select bolinf.brok_fndload_pkg.brok_fndload_profile_chk('ORG_ID')  
 from dual;
!
)

}
  
  
 get_next_xfer_id $1 
 

echo $NEXT_ID

when I am passing like above like i am hardcoding the paramter in function the plsql function is working .


Please let me know how to pass the parameter dynamically to plssql function as well at the same time capture the output value in a shell variable 3. The attempts at a solution (include all code and scripts):



4. Complete Name of School (University), City (State), Country, Name of Professor, and Course Number (Link to Course):


Note: Without school/professor/course information, you will be banned if you post here! You must complete the entire template (not just parts of it).
Oracle India, Bangalore,India


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by sujitdas2104; 08-08-2017 at 10:00 AM.. Reason: Added CODE tags.
# 2  
Old 08-08-2017
Please make sure you fill in ALL relevant form sections, esp. No. 4!
# 3  
Old 08-08-2017
Quote:
Originally Posted by RudiC
Please make sure you fill in ALL relevant form sections, esp. No. 4!
And No. 1.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to pass position parameter into function.?

Hi Gurus, I have request which needs to pass position parameter to a function. I tried below simple code, it doesn't work. #!/bin/bash func_1(){ echo $1 } func_1 $ ./set_file abc $ do I need add some to get the position para first? thanks in advance. (3 Replies)
Discussion started by: ken6503
3 Replies

2. Shell Programming and Scripting

How to capture system() function output in variable

How to capture system() function output in awk variable and the print that awk variable..... (8 Replies)
Discussion started by: bharat1211
8 Replies

3. Shell Programming and Scripting

Pass a variable string in To_Date Oracle function in shell script

Hello, I am trying to execute an SQL query from shell script. A part of script is something like this: fromDate=`echo $(date +"%F%T") | sed "s/-//g" | sed "s/://g"` $ORACLE_HOME/sqlplus -s /nolog <<EOD1 connect $COSDBUID/$COSDBPWD@$COSDBSID spool... (4 Replies)
Discussion started by: sanketpatel.86
4 Replies

4. Shell Programming and Scripting

How to pass a function with a variable parameter into another variable?

Hello again :) Am currently trying to write a function which will delete a record from a file. The code currently looks as such: function deleteRecord() { clear read -p "Please enter the ID of the record you wish to remove: " strID ... (2 Replies)
Discussion started by: U_C_Dispatj
2 Replies

5. Shell Programming and Scripting

How to pass parameter to User defined function in shell script?

Hello, Can anyone guide me tin passing parameters into user defined function of shell script (KSH). Here is my code, InsertRecord() { DB_TBL=$(sqlplus $USERID/$PASSWORD@$DATABASE << EOF set head off set feed off set serveroutput on INSERT INTO TBL1 ( OLD_VAL, NEW_VAL, ... (7 Replies)
Discussion started by: Poonamol
7 Replies

6. Shell Programming and Scripting

What is the maximum number of parameter we can pass to a shell script function?

what is the maximum number of parameter we can pass to a shell script function (8 Replies)
Discussion started by: alokjyotibal
8 Replies

7. Shell Programming and Scripting

How to pass a variable as a parameter to DB2 database from shell script

I need to pass a variable as a parameter from shell script into a DB2 database. var=bhuk_1123_Q_11/22/09 select * from tbl1 where serial_id='$var'; I have tried executing it using db2 -tvf scriptname Somebody please help me out with this. It is throwing an error. Please tell me how... (2 Replies)
Discussion started by: ss3944
2 Replies

8. Shell Programming and Scripting

pass parameter to function

HI all I have a code like ############################################## minyear() { curryear=$1 echo $curryear } ##Main Program ## minyear exit ####### when i execute "sh scriptname 2005" output should be like 2005 but the output is blank. I guess i need to pass parameter to... (3 Replies)
Discussion started by: vasuarjula
3 Replies

9. Shell Programming and Scripting

[csh] How to capture output from a command and pass it on to a variable?

Hi there! I'm trying to write a script that will capture output from a command and assign it to a variable. Let's say, for example, I'd like to catch from inside the script whatever the following command outputs: ls *.aaa and put it into a variable "listoffiles". What I tried was: set... (3 Replies)
Discussion started by: machinogodzilla
3 Replies

10. UNIX for Advanced & Expert Users

Passing a unix variable value to a Plsql function

Suppose I have a unix variable called RGNM which is holding a value. Now I want to call a plsql function in my script. THis plsql function takes one IN parameter. I want to pass my UNIX VARIABLE Value to the plsql function. Can i just give it by giving $RGNM in the function after calling sqlplus... (1 Reply)
Discussion started by: cobroraj
1 Replies
Login or Register to Ask a Question