how to call oracle package stored procedure with output parameter

Bharat Guru
Bharat Guru used Ask the Experts™
on
How to call or execute below package using oracle sql developer


PACKAGE BODY "PKG_PRODUCT"
AS

PROCEDURE SP_LATEST_PRODUCT(
    I_ProductType     VARCHAR2,
    O_PRODUCT OUT SYS_REFCURSOR,
    O_PRODUCTCOUNT OUT NUMBER,
    O_RESULT OUT VARCHAR2 )
AS
BEGIN
      
        IF UPPER(I_ProductType)  = 'CLOTHS' THEN
                  O_PRODUCT OUT = 5;
        ELSE
                  O_PRODUCT OUT = 15;
        END IF;
 
      O_RESULT = 'SUCCESS';
      
END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
If you are wanting to test, perhaps use an anonymous pl/sql block:
declare
  opOut sys_refcursor;
  opCount number;
  oResult varchar2(10);
begin
  pkg_product.sp_latest_product('test', opOut, opCount, oResult);
end

Open in new window

Why did you define o_product as a sys_refcursor? Also, does your package even compile with the "o_product out = 5" assignments?

Apologies for any syntax errors - I am not at my workstation where I can error check my above code, but it should set you on your way.
Mark GeerlingsDatabase Administrator

Commented:
That looks to me like a SQL Server program that was converted to Oracle.  The name of the package, starting with "SP_" is the first clue.  That is a apparently a SQL Server standard, or at least very common in SQL Server systems.  Also. "out" parameters are common in SQL Server with Microsoft-based applications but not nearly as common with Oracle-based applications.

Martyn is correct.  To call a procedure like this with an Oracle program (like SQL Developer) you need to declare appropriate variables to accept the "out" parameters.
Senior Oracle DBA
Commented:
This would be a working example of the original code.

Package header:
CREATE OR replace PACKAGE pkg_product 
AS 
  PROCEDURE sp_latest_product( 
    i_producttype  VARCHAR2, 
    o_product      OUT SYS_REFCURSOR, 
    o_productcount OUT NUMBER, 
    o_result       OUT VARCHAR2 ); 
END; 

/

Open in new window

Package body:
CREATE OR replace PACKAGE BODY pkg_product 
AS 
  PROCEDURE Sp_latest_product(i_producttype  VARCHAR2, 
                              o_product      OUT SYS_REFCURSOR, 
                              o_productcount OUT NUMBER, 
                              o_result       OUT VARCHAR2) 
  AS 
  BEGIN 
      IF Upper(i_producttype) = 'CLOTHS' THEN 
        OPEN o_product FOR 'select 5 from dual'; 
      ELSE 
        OPEN o_product FOR 'select 15 from dual'; 
      END IF; 

      o_result := 'SUCCESS'; 
  END; 
END; 

/ 

Open in new window

Steps to run it (this was done with SQL*Plus, but SQL*Developer usually accepts the same commands):
var sql_cur refcursor;
var p_count number;
var res varchar2(1000);
begin
  pkg_product.sp_latest_product('ABC',:sql_cur, :p_count, :res);
end;
/
print sql_cur

Open in new window

Not sure why you need the ref cursor, I assume that was for an example, but you cannot assign a value to a cursor.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I'm getting below error


PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
johnsoneSenior Oracle DBA

Commented:
Post code.  Without the code, there is no way we can determine what that error means.  Ideally, post the real error as well, it should actually have the line number, which is very helpful.  If you don't have it anymore, you can query USER_ERRORS.
Mark GeerlingsDatabase Administrator

Commented:
There are responses here from three of us, so saying "no response" is not accurate.  Maybe you didn't get a response exactly like what you were hoping for, but you didn't reply to johnsone's request that you give us some more information.  Without more information from you, there isn't much we can do.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
I agree with Mark. This question should not be deleted. It contains valid and potentially useful information. You didn't answer my question either.
johnsoneSenior Oracle DBA

Commented:
Also agree with Mark.  Waiting for answers from author.  That isn't a reason to delete.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
I have marked johnsone's comment as the solution from my point of view because it is the most complete. I do, however feel that my solution was also suitable but naturally cannot state that my comment is also a solution. I will leave it for other experts to decide.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial