Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PLSQL Execute Package

Posted on 2011-03-01
12
Medium Priority
?
1,218 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
Technology Partners: 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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
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…

719 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