Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, R.

Asked: October 06, 2022 - 11:29 pm UTC

Last updated: October 07, 2022 - 1:57 am UTC

Version: PL/SQL Release 12.2.0.1.0 - Production

Viewed 10K+ times! This question is

You Asked

We have an overloaded stored procedure with the following declarations in our 3rd party application:
PROCEDURE GetPriceInfo (Location IN VARCHAR2,  ProductCode IN VARCHAR2, SalePrice OUT NUMBER);
PROCEDURE GetPriceInfo (Location IN VARCHAR2,  ProductCode IN VARCHAR2, ContractID IN VARCHAR2, SalePrice OUT NUMBER);

Our external tool would not handle this overloading properly and exits with "PLS-00307: too many declarations of 'GetPriceInfo' match this call" error.

As a workaround, I tried using an anonymous block by declaring all the parameters with appropriate type for the first version of Stored Procedure and that did provide the desired result.
Dbms_Output.Put_Line (SalePrice);


The above dumped the SalePrice correctly in dbeaver and gets executed successfully in our external tool. But unfortunately, the tool would only accept recordset/table output for further processing. In SQL server I can simply do
SELECT @SalePrice AS SalesPrice

after the procedure execution. but
SELECT SalePrice FROM DUAL

would not work in Oracle.

How do we handle this so that the out parameter could be returned as a single field recordset?
My privilege on the 3rd party application is very restrictive; don't have permissions to create global temporary tables.

and Connor said...

I don't what your external tool is, but options to explore:

1) Return a ref cursor

declare
  v_sale_price number
  rc sys_refcursor;
begin
  GetPriceInfo(..., v_sale_price);
  open rc for select v_sale_price sale_price from dual;
end;


2) wrapper function

create or replace
function get_sale_price([parms]) return number is
  v_sale_price number
begin
  GetPriceInfo(..., v_sale_price);
  return v_sale_price;
end;


and then do: select get_sale_price from dual;

3) pipeline function


create or replace
function get_sale_price([parms]) return sys.odcinumberlist piplined is
  v_sale_price number
begin
  GetPriceInfo(..., v_sale_price);
  pie row ( v_sale_price );
  return;
end;


then do: select column_value from get_sale_price([parms])

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why?

Fabulo, October 10, 2022 - 6:37 am UTC

Our external tool would not handle this overloading properly and exits with "PLS-00307: too many declarations of 'GetPriceInfo' match this call" error.

A declaration with a different number of non-defaulted parameters cannot produce "too many declarations". For your case-insensitive identifiers, the error message should refer to the procedure name in uppercase.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library