Home » SQL & PL/SQL » SQL & PL/SQL » Calling PL/SQL function dynamically (with IN and OUT parameters (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0)
Calling PL/SQL function dynamically (with IN and OUT parameters [message #607820] Wed, 12 February 2014 05:30 Go to next message
matspring1
Messages: 6
Registered: June 2012
Location: UK
Junior Member
Hi I'm writing code to be called from a front end form to test existing db functions - so essentially select the function, enter some IN params into the form, hit the test button and the returned value and OUT parameters are displayed in the form. I want my code to call the functions to be totally dynamic (or as dynamic as possible).

I've found that because of the limitations of the USING command within EXECUTE IMMEDIATE every time I test I have to create a wrapper function on the db, to call my actual db function, and call the wrapper from the form

The form code is generic, as follows:

Note I pass in the wrapper function and only populate the IN / IN OUT / OUT parameter as required

plsql_block := 'DECLARE ' ||
' vResult Varchar2(100); ' ||
'BEGIN :vResult := ' || cFunction ||
-- In Parameters
'(cpi1 => :cParamIn1, ' ||
' cpi2 => :cParamIn2, ' ||
' cpi3 => :cParamIn3, ' ||
' cpi4 => :cParamIn4, ' ||
' cpi5 => :cParamIn5, ' ||
' cpi6 => :cParamIn6, ' ||
' cpi7 => :cParamIn7, ' ||
' cpi8 => :cParamIn8, ' ||
' cpi9 => :cParamIn9, ' ||
' cpi10 => :cParamIn10
-- In Out Parameters
' cpio1 => :cParamInOut1, ' ||
' cpio2 => :vParamInOut2, ' ||
' cpio3 => :vParamInOut3, ' ||
' cpio4 => :vParamInOut4, ' ||
' cpio5 => :vParamInOut5
-- Out Parameters
' cpo1 => :cParamOut1, ' ||
' cpo2 => :vParamOut2, ' ||
' cpo3 => :vParamOut3, ' ||
' cpo4 => :vParamOut4, ' ||
' cpo5 => :vParamOut5 ); ' ||
'END;';

Execute Immediate plsql_block
Using Out vResult, cParamIn1, cParamIn2, cParamIn3, cParamIn4, cParamIn5, cParamIn6, cParamIn7, cParamIn8, cParamIn9, cParamIn10,
-- IN OUT Parameters
In Out cParamInOut1, In Out cParamInOut2, In Out cParamInOut3, In Out cParamInOut4, In Out cParamInOut5,
-- OUT Parameters
Out cParamOut1, Out cParamOut2, Out cParamOut3, Out cParamOut4, Out cParamOut5;

An example of my test wrapper code generated on the db, is as follows:

FUNCTION XXCPTEST_USER_DETAILS3
( cPi1 in Varchar2, cPi2 in Varchar2,
cPi3 in Varchar2, cPi4 in Varchar2,
cPi5 in Varchar2, cPi6 in Varchar2,
cPi7 in Varchar2, cPi8 in Varchar2,
cPi9 in Varchar2, cPi10 in Varchar2
cPio1 in out Varchar2, cPio2 in out Varchar2,
cPio3 in out Varchar2, cPio4 in out Varchar2,
cPio5 in out Varchar2
cPo1 out Varchar2, cPo2 out Varchar2,
cPo3 out Varchar2, cPo4 out Varchar2,
cPo5 out Varchar2 )return Varchar is
vResult Varchar2(1000);

BEGIN
vResult := USER_DETAILS3(cID => cPi1, cFirstname => cPo1, cSurname => cPo2, cAge => cPo3, cSalary => cPo4, cText => cPio1, cSex => cPi2, cDOB => cPi3);
Return(vResult);
END XXCPTEST_USER_DETAILS3;

My approach works, but is long winded, I'd like to not have to create the test wrapper function on the db, any ideas??

[Updated on: Wed, 12 February 2014 05:31]

Report message to a moderator

Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #607822 is a reply to message #607820] Wed, 12 February 2014 05:42 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
Hi,

DBMS_SQL is capable to treat dynamic number of SQL bind variables.

Just curious: before/after calling the "wrapper function", how are you determining, which variables shall be used? Because, you will have to use the same way (probably the static one) for determining which variables shall be used in the DBMS_SQL call.

It seems to me that you are unnecessarily making things harder. What is wrong with the static call of the function (as you statically call it in XXCPTEST_USER_DETAILS3 anyway)?
Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #607827 is a reply to message #607822] Wed, 12 February 2014 06:44 Go to previous messageGo to next message
matspring1
Messages: 6
Registered: June 2012
Location: UK
Junior Member
Hi

I didn't realise I could us DBMS_SQL to call a function, I initially thought I could "select [function name(parameters)] from dual", however because I have OUT parameters I therefore dismissed this. Maybe there is a way I can do this? any ideas?

The in/out parameter variables are determined in the function definition form (user has to define in / out parameters, type, name etc), so I know my function parameters as test time

Thanks for taking time to reply
Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #607832 is a reply to message #607827] Wed, 12 February 2014 07:25 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
No, you cannot call a function with OUT parameters in SQL query. But you may call PL/SQL block (BEGIN ... END;) including the call of the (dynamic) function using DBMS_SQL.

As you have to declare all variables in the caller block, you have to treat them statically before/after the function call anyway, I still see no benefit of the dynamic call. But, maybe I overlooked something.

Good luck.
Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #607857 is a reply to message #607832] Wed, 12 February 2014 15:41 Go to previous messageGo to next message
matspring1
Messages: 6
Registered: June 2012
Location: UK
Junior Member
thanks for response, as a test run I'm running the following (obviously need to expand lateron) but I'm getting an ORA-06502 error, debugger tells me that I am running through my xxcp_custom_utils_16.USER_DETAILS3 code but its bombing out as soon as values are assigned to my out parameters. I'm sure its the was that the bind variables are defined.

vResult Varchar2(1000);

v_CallStmt VARCHAR2(200);
v_CursorID INTEGER;
v_Dummy Integer;

vPo1 Varchar2(100);
vPo2 Varchar2(100);
BEGIN
-- Open the cursor
v_CursorID := DBMS_SQL.OPEN_CURSOR;

v_CallStmt := 'BEGIN :vRes := xxcp_custom_utils_16.USER_DETAILS3(:Pi1, :Po1, :Po2 ); END;';
DBMS_SQL.PARSE(v_CursorID, v_CallStmt, DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(v_CursorID, 'vRes', vResult);
DBMS_SQL.BIND_VARIABLE(v_CursorID, 'Pi1', cPi1);
DBMS_SQL.BIND_VARIABLE_char(v_CursorID, 'Po1', vPo1);
DBMS_SQL.BIND_VARIABLE_char(v_CursorID, 'Po2', vPo2);

v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
DBMS_SQL.VARIABLE_VALUE(v_CursorID, 'vRes', vResult);

DBMS_SQL.CLOSE_CURSOR(v_CursorID);
Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #607858 is a reply to message #607857] Wed, 12 February 2014 15:51 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #607864 is a reply to message #607857] Wed, 12 February 2014 20:54 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
After the finding of the meaning of ORA-06502 the reason is quite obvious and stated in the link.
Is the static call (nothing precludes running it statically) 'bombing out' (are you running Oracle on a bomb launcher?) too?
Do you have any special reason for using BIND_VARIABLE_CHAR (which according to the documentation binds CHAR data type) for the OUT parameters with VARCHAR2 data type?
Also, you might need to get the value of those OUT parameters with VARIABLE_VALUE too.
By the way, you snippet misses the declaration of CPI1 and the definition of the called procedure, so it is impossible to reproduce it.
Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #608034 is a reply to message #607864] Fri, 14 February 2014 07:51 Go to previous messageGo to next message
matspring1
Messages: 6
Registered: June 2012
Location: UK
Junior Member
My ORA-06502 error was due incorrect binding of returned value - vRetVal. I've rewritten as follows, it now take collection of in parameters and returns collection of out and in out parameters that my form unpacked and displays to the form.

Thanks for helping

-- Build Pl/SQL Block
vSQL := ' DECLARE ' ||
' vRetVal Varchar2(10); ' ||
' BEGIN ' ||
' :vRetVal := ' || cFunction || '(' || vParams || '); ' ||
' END;';

DBMS_SQL.PARSE(cur, vSQL, DBMS_SQL.NATIVE);

-- Assign Bind Variable (Returned Value)
DBMS_SQL.BIND_VARIABLE(cur, 'vRetVal', '', 100);

-- Assign Bind Variables (Parameters)
For rec In 1 .. cInParamList.count Loop
DBMS_SQL.BIND_VARIABLE( cur,
cInParamList(rec).ParamName,
cInParamList(rec).ParamValue,
100);
End Loop;

test_cu := DBMS_SQL.EXECUTE(cur);

-- Extract Parameter Out Values into Out Pl/SQL table
For rec In 1 .. cInParamList.count Loop
If cInParamList(rec).ParamDir In ('O', 'IO' ) Then -- Out / In Out
vParamOutCounter := vParamOutCounter + 1;

DBMS_SQL.VARIABLE_VALUE(cur, cInParamList(rec).ParamName, vParamOutValue);
vParamOutTable.extend;
vParamOutTable( vParamOutCounter ).ParamName := cInParamList(rec).ParamName;

If cInParamList(rec).ParamType = 'N' Then
vParamOutTable( vParamOutCounter ).ParamValue := TO_NUMBER(vParamOutValue);
End If;
vParamOutTable( vParamOutCounter ).ParamValue := vParamOutValue;
End If;
End Loop;

-- Extract Returned Value
DBMS_SQL.VARIABLE_VALUE(cur, 'vRetVal', cReturnedValue);
Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #608049 is a reply to message #608034] Fri, 14 February 2014 09:15 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
Thank you for your feedback.
Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #608053 is a reply to message #608049] Fri, 14 February 2014 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
WOW!
Having PL/SQL write PL/SQL to invoke more PL/SQL will scale as well as my goat can fly.
How do you ensure that the datatype for every IN & OUT parameter is the correct datatype for every function being invoked?
Re: Calling PL/SQL function dynamically (with IN and OUT parameters [message #608059 is a reply to message #608053] Fri, 14 February 2014 10:23 Go to previous message
matspring1
Messages: 6
Registered: June 2012
Location: UK
Junior Member
The param datatype is held in the cInParamList parameter collection

Previous Topic: all_objects behaving differently when called from a pl/sql function (merged 3)
Next Topic: How to loop through a Date Range ?
Goto Forum:
  


Current Time: Fri Aug 29 19:25:32 CDT 2014

Total time taken to generate the page: 0.13438 seconds