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
Solved

PLSQL Execute Package

Posted on 2011-03-01
12
1,205 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 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