Linker IT Software
Google
Web www.oraxcel.com
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 3423 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.
 
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?
 
Eg:
 
Col1      col2      col3      col4      col5            Col6
Setup:
Package      Pkg
Procedure      prc
 
Data:
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
PROCEDURE 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.
 
Answer:
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.
 
Example:
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