Solved

EXECUTE IMMEDIATE

Posted on 2016-11-14
5
33 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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
>>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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now