Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

EXECUTE IMMEDIATE

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
brgdotnet
Asked:
brgdotnet
2 Solutions
 
Helena Markováprogrammer-analystCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
awking00Commented:
>>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
 
brgdotnetAuthor Commented:
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
 
awking00Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now