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  |
 |
dag1ngapr1nce
Messages: 19
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  |
flyboy
Messages: 1903
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 #607832 is a reply to message #607827] |
Wed, 12 February 2014 07:25 |
flyboy
Messages: 1903
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 |
|
dag1ngapr1nce
Messages: 19
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 #607864 is a reply to message #607857] |
Wed, 12 February 2014 20:54 |
flyboy
Messages: 1903
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 |
|
dag1ngapr1nce
Messages: 19
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);
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 01:29:02 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
