Home » Infrastructure » Unix » Call oracle proc from unix
Call oracle proc from unix [message #327041] Fri, 13 June 2008 09:13 Go to next message
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
Re: Call oracle proc from unix [message #327050 is a reply to message #327041] Fri, 13 June 2008 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Call oracle proc from unix [message #327060 is a reply to message #327041] Fri, 13 June 2008 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
If you would use valid syntax, you would not get errors.
Re: Call oracle proc from unix [message #327091 is a reply to message #327060] Fri, 13 June 2008 14:05 Go to previous message
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.

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


Current Time: Sat Dec 03 16:30:16 CST 2016

Total time taken to generate the page: 0.12793 seconds