Solved

EXECUTE IMMEDIATE

Posted on 2016-11-14
5
73 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
5 Comments
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 350 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 150 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

823 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