Solved

EXECUTE IMMEDIATE

Posted on 2016-11-14
5
77 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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

821 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