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.
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])