Solved

PLSQL Execute Package

Posted on 2011-03-01
12
1,201 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 73

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 73

Expert Comment

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

What did you run?
0
 

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 73

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
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: 35009195
It ran with this message. What does this mean?
anonymous block completed
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35009260
that means it worked

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

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 73

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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now