Solved

PLSQL Execute Package

Posted on 2011-03-01
12
1,209 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:jeffreyjseaman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35008494
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
 

Author Comment

by:jeffreyjseaman
ID: 35008668
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35008698
There is no "IN" in the code I sent above.

What did you run?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jeffreyjseaman
ID: 35008809
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35009130
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
 

Author Comment

by:jeffreyjseaman
ID: 35009195
It ran with this message. What does this mean?
anonymous block completed
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35009260
that means it worked

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

Expert Comment

by:sdstuber
ID: 35009269
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
 

Author Comment

by:jeffreyjseaman
ID: 35010077
How do I do a fetch? in PL/SQL Thanks I'm rewarding you the points. You've been very helpful.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 35010824
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
 

Author Closing Comment

by:jeffreyjseaman
ID: 35010981
Great feedback, quick. Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Workhours Count beetween Workhours 3 50
UTL_FILE invalid file operation 5 60
Oracle performance tuning 2 57
Check for any ASM patches and install them. 1 25
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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

752 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