OraFAQ Forum: Unix » Call oracle proc from unix

Home » Infrastructure » Unix » Call oracle proc from unix

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Call oracle proc from unix [message #327041]

Fri, 13 June 2008 09:13

souvik_roy
Messages: 16
Registered: June 2008

Junior Member

I have a pl/sql proc my_procedure() with in param emp_name and out param emp_id.
I have written shell script as follows:

#!/bin/ksh

#EMP_ID=0
ename='das'
sqlplus -s <dbuser>/<dbpass>@<dbsid> << END_OF_SQL

var EMP_ID NUMBER(10) :=0;
exec my_procedure($ename, :EMP_ID);
#print :EMP_ID;
echo "Employee id is: $EMP_ID"
exit
END_OF_SQL

it is showing the error as
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE ] ]
SP2-0552: Bind variable "EMP_ID" not declared.
SP2-0552: Bind variable "EMP_ID" not declared.
SP2-0734: unknown command beginning "echo "Empl..." - rest of line ignored.

could you please help overcoming this error..

Regards,
Souvik

Report message to a moderator

Re: Call oracle proc from unix [message #327050 is a reply to message #327041]

Fri, 13 June 2008 09:57

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Reported Messages

Reported By: souvik_roy On: Fri, 13 June 2008 16:30 In: Platforms � Unix � Call oracle proc from unix
Reason It is very urgent for me..

The only urgent thing for you is to read OraFAQ Forum Guide.
You will learn how to post.
You will learn what you must not do...
like reporting message to get attention.

Regards
Michel

Report message to a moderator

Re: Call oracle proc from unix [message #327060 is a reply to message #327041]

Fri, 13 June 2008 10:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If you would use valid syntax, you would not get errors.

Report message to a moderator

Re: Call oracle proc from unix [message #327091 is a reply to message #327060]

Fri, 13 June 2008 14:05

andrew again
Messages: 2577
Registered: March 2000

Senior Member

your single quotes are meta-charachters to the shell, so don't make it through to pl/sql.

dev>>cat ttt.ksh  
#!/bin/ksh
unixvar1=5
unixvar2=ABC
sqlplus -s scott/tiger@dev <<EOF
  set serveroutput on
  exec dbms_output.put_line('unixvar1='||to_char($unixvar1));
  exec dbms_output.put_line('unixvar2='||'$unixvar2');
EOF

dev>>ttt.ksh    
unixvar1=5

PL/SQL procedure successfully completed.

unixvar2=ABC

PL/SQL procedure successfully completed.

Report message to a moderator

Previous Topic:	unix shell script error
Next Topic:	Word size of a new solaris server (T1000)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 05 11:49:00 CDT 2025