LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 07-02-2008, 05:53 AM   #1
Stilltray
LQ Newbie
 
Registered: Dec 2005
Posts: 8

Rep: Reputation: 0
execute oracle procedure from linux script and sqlplus


I use:

sqlplus USERNAME/PASSWORD@sid <<EOF
exec pr$ocedure;
EOF

or

sqlplus USERNAME/PASSWORD@sid <<EOF
begin
pr$ocedure;
end;
/
EOF


gives as result:

Excuting script.. sending output to mylogdir

BEGIN pr; END;


(or in the first case:

BEGIN pr; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'PR' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



((Our procedures in Oracle are named pr$....))

Is seems that is isnt' working??

Anybody any idea what's going wrong/ what I'm missing??

Last edited by Stilltray; 07-02-2008 at 06:40 AM.
 
Old 07-03-2008, 06:07 AM   #2
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Distribution: UBUNTU 5.10 since Jul-18,2006 on Intel 820 DC
Posts: 637

Rep: Reputation: 140Reputation: 140
In any unix (including linux), $ in the shell expands what makes what follows as a variablle. (Equivalent to % in DOS bach file).

So, in your case, pr$ocedure become pr<value of ocedure>, ie jusr pr, since ocedure is undefined.

So you have to "escape" the $ by enclosing it in single quotes. The single quote makes everything inside it be a literal.

So your scripts would become.

sqlplus USERNAME/PASSWORD@sid <<EOF
exec 'pr$ocedure';
EOF

or

sqlplus USERNAME/PASSWORD@sid <<EOF
begin
'pr$ocedure';
end;
/
EOF

Please try this and give feedback.
 
Old 07-03-2008, 12:19 PM   #3
Stilltray
LQ Newbie
 
Registered: Dec 2005
Posts: 8

Original Poster
Rep: Reputation: 0
@AnanthaP,
Thx for you answer.

I thought I already tried your suggestion, nevertheless I've tried it again just now.
It didn't work.

The code is now:

begin
'pr$ocedure';
end;


The result is:


BEGIN 'pr'; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "pr" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
The symbol "return" was substituted for "pr" to continue.




By the way: The distro is RHEL 4.x
 
Old 11-14-2008, 10:35 AM   #4
geekchica
LQ Newbie
 
Registered: Nov 2008
Posts: 1

Rep: Reputation: 0
escape char

try this:

sqlplus USERNAME/PASSWORD@sid <<EOF
exec pr\$ocedure;
EOF

You do need to escape the $, but use a backslash instead of single quotes.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
execute a unix shell script from a database trigger(oracle 10g) nayant.m Linux - Newbie 1 03-28-2008 07:54 AM
remote sqlplus connection to an oracle server saavik Linux - Networking 0 01-20-2006 03:16 AM
sqlplus command from script Grassie Coetzee LinuxQuestions.org Member Intro 2 03-13-2005 12:01 PM
Running SQLPLUS after installing Oracle 8i linux_pioneer Solaris / OpenSolaris 4 08-14-2004 11:59 PM
Need to connect to db with SQLplus...or ANY oracle client.... vous Linux - Software 2 08-06-2003 04:54 AM


All times are GMT -5. The time now is 09:21 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration