how to call oracle package stored procedure with output parameter

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
Bharat GuruAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
johnsoneSenior Oracle DBACommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

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

Bharat GuruAuthor 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:
1
johnsoneSenior Oracle DBACommented:
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.
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
I agree with Mark. This question should not be deleted. It contains valid and potentially useful information. You didn't answer my question either.
0
johnsoneSenior Oracle DBACommented:
Also agree with Mark.  Waiting for answers from author.  That isn't a reason to delete.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.