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

jeffreyjseamanAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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               VARCHAR2(32767);

--  declare enough columns to hold the results of the cursor
-- even better is to declare a record type that matches the structure of the cursor
-- but if you don't know what it will be then use columns
  col1 number;   -- change this to appropriate type
  col2 number;  -- change this to appropriate type
  col3 number;  -- change this to appropriate type

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);


-- you could put this fetch into a loop and fetch until you use up all of the data
fetch v_item_details_cur into col1, col2,col3;


end;
0
 
sdstuberCommented:
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;
0
 
jeffreyjseamanAuthor Commented:
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:
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

What did you run?
0
 
jeffreyjseamanAuthor Commented:
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:
0
 
sdstuberCommented:
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);
0
 
jeffreyjseamanAuthor Commented:
It ran with this message. What does this mean?
anonymous block completed
0
 
sdstuberCommented:
that means it worked

you will probably want to "do" something with the output parameters though.
0
 
sdstuberCommented:
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
0
 
jeffreyjseamanAuthor Commented:
How do I do a fetch? in PL/SQL Thanks I'm rewarding you the points. You've been very helpful.
0
 
jeffreyjseamanAuthor Commented:
Great feedback, quick. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.