Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

EXECUTE IMMEDIATE

Posted on 2016-11-14
5
Medium Priority
?
99 Views
Last Modified: 2016-11-15
I have an Oracle stored procedure which accepts 3 variables of type VARCHAR. Short example given below.

If I want to execute this stored procedure from the command line, how would I do it? I tried something like this, but it
does not work : EXECUTE IMMEDIATE PortalForClient USING var1, var2, var3

I can do this quite easily in sql server query analyzer. However Oracle is quite different, so I would appreciate some help from an Oracle expert.

CREATE OR REPLACE PROCEDURE "PortalForClient"
(
    var1 IN VARCHAR := NULL,
    var2 IN VARCHAR := NULL,
    var3 IN OUT CHAR
)
0
Comment
Question by:brgdotnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 1400 total points
ID: 41887401
You can use an anonymous block, something like this:
declare
s_var1  Varchar2(100):= NULL;
s_var2 Varchar2(100):= NULL;
x_var3  Varchar2(100);
begin
PortalForClient(s_var1,s_var2,x_var3);
dbms_output.put_line(x_var3);
end;

Also I am not sure the definition of your procedure is OK, it ought to be like this:
CREATE OR REPLACE PROCEDURE PortalForClient
 (
     var1 IN VARCHAR default NULL,
     var2 IN VARCHAR default NULL,
     var3 IN OUT CHAR
 )  IS ...

Command EXECUTE IMMEDIATE is not EXECUTE, as it can be seen here:
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve022.htm#SQPUG043

Hope this help you.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 600 total points
ID: 41887470
Just add in addition a "/" at the end of the anonymous block.

declare
s_var1  Varchar2(100):= NULL;
s_var2 Varchar2(100):= NULL;
x_var3  Varchar2(100);
begin
PortalForClient(s_var1,s_var2,x_var3);
dbms_output.put_line(x_var3);
end;
/
0
 
LVL 32

Expert Comment

by:awking00
ID: 41888061
>>CREATE OR REPLACE PROCEDURE "PortalForClient"
 (
     var1 IN VARCHAR := NULL,
     var2 IN VARCHAR := NULL,
     var3 IN OUT CHAR
 )
<<
What's the rest of the procedure?
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 41888116
It isn't necessary to post the entire procesure. Quite large. I just want to know how to execute it based upon the input parameters I privided.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41888166
You can either use a pl/sql block with begin PortalForClient(var1, var2, var3); end;/ or from sqlplus just exec PortalForClient(var1, var2, var3);
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question