asked on

PLSQL Execute Package

I'm new with PL/SQL Packages. So this may be very simple. I need to execute this package.
I've included the code but not sure how to execute it. I've tried several ways and none work.

execute pkg_Soccer_Main.prc_soccer_Get_Item_Details (214,'',0,0,'');
execute pkg_Soccer_Main.prc_soccer_Get_Item_Details (214)
execute pkg_Soccer_Main.prc_soccer_Get_Item_Details (214,'','','');

Errors out everytime. So what am I doing wrong. Code attached for the 1 INPUT and 3 OUTPUT parameters.

Thanks

p_ItemId                IN      items.type,
       p_item_details_cur        OUT     sys_refcursor,
       p_action_details_cur       OUT     sys_refcursor,
       p_error_code               OUT     NUMBER,
       p_error_desc               OUT     VARCHAR2

Open in new window

Sean Stuber

you need to declare variables to receive the output parameters.

you can't write an OUT to a constant

your in/out indicates 5 parameters (1 in + 4 out), not 4 (1 in + 3 out)

try something like this...

declare
v_ItemId items.type := 214;
v_item_details_cur sys_refcursor;
v_action_details_cur sys_refcursor;
v_error_code NUMBER;
v_error_desc VARCHAR;
begin
pkg_Soccer_Main.prc_soccer_Get_Item_Details(v_itemid,v_item_details_cur,v_action_details_cur,v_error_code,v_error_desc);
end;

jeffreyjseaman

ASKER

Thanks sdstuber: I tried it and this is what i get:

Error report:
ORA-06550: line 2, column 35:
PLS-00103: Encountered the symbol "IN" when expecting one of the following:

constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national character
nchar
ORA-06550: line 2, column 65:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
. ( * @ % & - + / at loop mod remainder range rem ..
<an exponent (**)> || multiset
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Sean Stuber

There is no "IN" in the code I sent above.

What did you run?

jeffreyjseaman

ASKER

Da, Sorry about that. I copied the info down and never removed the parameters. I ran it exactly how you stated this time. I received a different message;

Error starting at line 1 in command:

declare
v_ItemId items.type := 214;
v_item_details_cur sys_refcursor;
v_action_details_cur sys_refcursor;
v_error_code NUMBER;
v_error_desc VARCHAR;
begin
pkg_Soccer_Main.prc_soccer_Get_Item_Details(v_itemid,v_item_details_cur,v_action_details_cur,v_error_code,v_error_desc);
end;

Error report:
ORA-06550: line 6, column 40:
PLS-00215: String length constraints must be in range (1 .. 32767)
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Sean Stuber

I didn't know what your error message was, so I left it undefined

change it to something like this....

v_error_desc VARCHAR2(32767);

jeffreyjseaman

ASKER

It ran with this message. What does this mean?
anonymous block completed

Sean Stuber

that means it worked

you will probably want to "do" something with the output parameters though.

Sean Stuber

like, fetch from the cursors and process whatever data is in them, check the error codes and description for whatever status the procedure might have sent you

jeffreyjseaman

ASKER

How do I do a fetch? in PL/SQL Thanks I'm rewarding you the points. You've been very helpful.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jeffreyjseaman

ASKER

Great feedback, quick. Thanks