Procedure with OUT parameters in a PostgreSQL package not working

Question

During migration from Oracle to PostgreSQL. I encounter a problem: Procedure with OUT parameters in a PostgreSQL package not working. Whenever run the procedure, it say procedure does not exist.

CREATE OR REPLACE PACKAGE pkg_productdetails
IS
  Procedure p_getprod_details(in_locationid numeric, OUT cur_Product_typedetails refcursor, OUT cur_Productlist refcursor);
END pkg_productdetails;

CREATE OR REPLACE PACKAGE BODY pkg_productdetails
IS
  Procedure p_getprod_details(in_locationid numeric, OUT cur_Product_typedetails refcursor, OUT cur_Productlist refcursor) IS
  BEGIN
      OPEN cur_Product_typedetails FOR
--select the cur_Product_typedetails ;

 OPEN cur_Productlist FOR
--select the cur_Productlist;

  END;
END pkg_productdetails;

when I run this procedure, it say pkg_productdetails.p_getprod_details(numeric) does not exist.

SELECT pkg_productdetails.p_getprod_details(10001);

You call a procedure which has 3 parameters only with one and two of them are OUT? You need two cursor variables to send to this procedure. — Mottor, Jul 29 '16 at 07:25
@Mottor when we are calling this pacakge.procedure from code with all 3 parameters (1 IN and 2 OUT). it gives same error. — Paarth, Jul 29 '16 at 07:34
When there is "procedure does not exist" there are two cases, you do not see the procedure because you don't have grants/synonyms and so on or you have another signature (order and type of parameters). Show how you call it, because this EXEC above is not right. — Mottor, Jul 29 '16 at 14:05
@Mottor This was a example of the case when we using OUT parameters in Package.procedure. Other procedures without OUT parameters in same package **pkg_productdetails** are working fine. This is not code issue because all other functions, procedures and package.procedures are working fine except Procedure with OUT parameters in the packages. We are sending all parameters to package.procedure. — Paarth, Jul 30 '16 at 06:18

score 0 · Accepted Answer · answered Aug 12 '16 at 05:18

One work around I got to handle this situation If we convert procedures to functions, its working.

CREATE OR REPLACE PACKAGE pkg_productdetails
IS
  Function p_getprod_details(in_locationid numeric) RETURNS SETOF refcursor;
END pkg_productdetails;

CREATE OR REPLACE PACKAGE BODY pkg_productdetails
IS
  FUNCTION p_getprod_details(in_locationid numeric) RETURNS SETOF refcursor IS
cur_Product_typedetails refcursor;
cur_Productlist  refcursor;

  BEGIN
  OPEN cur_Product_typedetails FOR
--select the cur_Product_typedetails ;
return next cur_Product_typedetails;


 OPEN cur_Productlist FOR
--select the cur_Productlist;
return next cur_Productlist;

  END;
END pkg_productdetails;

when I run this package Function, it is working pkg_productdetails.p_getprod_details(numeric).

SELECT pkg_productdetails.p_getprod_details(10001);

returning <unnamed portal 1> and <unnamed portal 2>

Procedure with OUT parameters in a PostgreSQL package not working

1 Answers1