calling a pl/sql function with parameters from within a function in unix

Question

I have a function in my unix script which has a call to a pl/sql function. The unix function is called with values taken from user at runtime. These values are then needed to be passed to the pl/sql within the unix function along with some variables storing sql results. How do i call both the functions? How do i pass values?

Thanks in advance Pranay

Pablo Santa Cruz · Accepted Answer · 2011-03-04 10:29:24Z

1

You need an Oracle command interpreter to run PL/SQL code within a UNIX (bash?) program. You will need to call probably sqlplus, provide the corresponding credentials, and prepare a string with the SQL command corresponding to the PL/SQL procedure call.

You probably need something like this:

#!/bin/sh

echo "your sql sentence" > yourscript.sql
sqlplus user/pass@db @yourscript.sql

I know you said you use ksh but I don't know its syntax, and I am sure you can do something similar to what I posted.

edited Mar 4, 2011 at 10:29

answered Mar 4, 2011 at 10:12

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pranay Over a year ago

I am using ksh and calling sqlplus. My code goes something like this: var1=sqlplus -s name/pwd <<EOF select statement exit; EOF fun(){ sqlplus -s name/pwd <<EOF; getno(need to pass values here including var1) }

Pranay Over a year ago

Thanks..But now i have got one little problem. The called sql script prints the correct answer along with the following: 86044 PL/SQL procedure successfully completed. SP2-0734: unknown command beginning "2010/07/01..." - rest of line ignored. SP2-0734: unknown command beginning "2010/07/01..." - rest of line ignored. SP2-0042: unknown command "REL-LOC" - rest of line ignored. Here 86044 is the number i require but the rest is also getting printed. Please suggest REL-LOC

Pablo Santa Cruz Over a year ago

@Pranay: let us look at your script and the way you are actually calling it.

Collectives™ on Stack Overflow

calling a pl/sql function with parameters from within a function in unix

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related