asked on

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

Martyn Spencer

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 Geerlings

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.

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bharat Guru

ASKER

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:

johnsone

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 Geerlings

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 Spencer

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

johnsone

Also agree with Mark. Waiting for answers from author. That isn't a reason to delete.

Martyn Spencer

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.