Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Dynamically build a PL/SQL package call (Read 3379 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Dynamically build a PL/SQL package call
18.09.06 at 07:23:06
Dynamically build a PL/SQL package call
Martin from Australia asked the following question.
I want to be able to dynamically format a PL/SQL call (e.g., provide the package/procedure name in some field then generate the call via XL concatenate) and have the call return values into XL cells (i.e., PL/SQL out parameters). Is that possible?
Col1      col2      col3      col4      col5            Col6
Package      Pkg
Procedure      prc
Name      Value      Id      Error      Message      Call (generated from concatenate)
OP1      123      1      0            pkg.prc(:col1,:col2,:col3,:col4, :col5);
OP2      234      2      0            pkg.prc(:col1,:col2,:col3,:col4, :col5);
OP2      456            -1      Duplicate key
pkg.prc(:col1,:col2,:col3,:col4, :col5);
Where the data values in col3, col4 and col5 are populated when I execute the calls in col6.
I have tried but get errors  
- "Parameter type not supported" if I haven't specified the parameter
types (string/date/number)
- "Parameter object improperly defined" (MS error?) if have specified
the parameters types, followed by "No value given for one or more
required parameters"
The PL/SQL pkg/prc looks like this:
SQL> desc pkg.prc
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_IN_NAME                      VARCHAR2                IN
 P_IN_VALUE                    NUMBER                    IN    
 P_OUT_ID                       NUMBER                  OUT
 P_OUT_ERR_NUM                  NUMBER                  OUT
 P_OUT_ERR_MSG                  VARCHAR2                OUT
Any suggestions are welcome.
This is very well possible. When you use bind variables (e.g. :MyVar) like you do you must ensure the direction and the type of the variables are correct. Some drivers may be a little difficult to support this. You may therefore try another driver too.  
I would suggest you use substitution variables for the input variables (e.g. &MyVar). These are string subsituted into your package calls. Further I would suggest you use the cell name of the parameter as variable name.
Finally it is a good idea to wrap the package call in an anonymous PL/SQL block with begin and end.
begin   pkg.prc( '&A1' , :B1 , :C1 , :D1 , :E1 );   end;
This will do the trick.
Back to top
« Last Edit: 18.09.06 at 07:30:09 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1