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
Parameter passing into a function (Read 11219 times)
Steve Taylor
YaBB Newbies
*




Posts: 4
Parameter passing into a function
25.10.05 at 16:02:00
 
I have a simple function:
 
function validate_comm_code(p_comm_code in varchar2)
                       return char
is
v_valid_code      varchar2(1);
begin
   begin
     select 'Y'
     into v_valid_code
     from commodity_codes
     where comm_code = p_comm_code;
     exception
     when no_data_found then
          v_valid_code := 'N';
   end;
    
   return v_valid_code;
end;
 
 
 
that I'm calling from an Excel module:
 
 
 
Sub Validate_Comm_Code()
 
Dim v_valid_code         As String
Dim v_comm_code          As String
Dim r                    As Integer
 
For r = 1 To 10
    If Worksheets("Sheet1").Cells(r, 1) <> "" Then
      v_comm_code = Worksheets("Sheet1").Cells(r, 1)
      ExecutePLSQL "begin " & _
           "   :v_valid_code := purch_comm_codes.validate_comm_code(:v_comm_code); " & _
           " end; "
    
      MsgBox "Is " & Worksheets("Sheet1").Cells(r, 1) & " valid?  " & v_valid_code
    End If
Next r
    
End Sub
 
 
 
 
Public Sub ExecutePLSQL(SQLText As String)
    With SQLXL.Sql
     .SetText SQLText
 
     .Statements(1).Execute
    End With
End Sub
 
 
****************************************************************************
 
However, when I execute Validate_Comm_Code in Excel, no parameter is passed into the Oracle function, even though there are values in the spreadsheet (the MsgBox command confirms this).
 
I've been trying to work this out for over an hour and getting nowhere fast...
 
Any ideas?
Back to top
 
« Last Edit: 25.10.05 at 16:23:02 by Steve Taylor »  
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Parameter passing into a function
Reply #1 - 25.10.05 at 16:19:35
 
Please consider the following subroutine. I have used an anonymous PL/SQL block instead of your function so anyone can test this without creating functions. The PL/SQL anonymous block uses two parameters. When you use setText, these two parameters will be found and created in the BindVariables collection. Executing the PL/SQL statement will not multiply the in parameter by two. The result will be shown in a message.
 
Sub Test()
  SQLXL.Sql.setText "begin :p_out := :p_in * 2;  end;"
  SQLXL.Database.Parameters.BindVariables("p_in").Value = 123
  SQLXL.Database.Parameters.BindVariables("p_out").Value = 0
  With SQLXL.Sql.Statements(1).Execute
  
  MsgBox SQLXL.Database.Parameters.BindVariables("p_out").Value
End Sub
 
 
The error you made was that a normal VB variable cannot be used as bind variable.
 
You used:
 
Dim v_comm_code          As String  
 
v_comm_code = Worksheets("Sheet1").Cells(r, 1)  
ExecutePLSQL "begin :v_valid_code :=  purch_comm_codes.validate_comm_code(:v_comm_code); "
 
This does not work.  
You should have used:
 
ExecutePLSQL "begin :v_valid_code :=  purch_comm_codes.validate_comm_code(:v_comm_code); "
SQLXL.Database.Parameters.BindVariables("v_comm_code").Value = Worksheets("Sheet1").Cells(r, 1)
Back to top
 
« Last Edit: 26.10.05 at 08:59:32 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Parameter passing into a function
Reply #2 - 27.10.05 at 14:31:29
 
I was asked for further analysis of the following code. Sharing the outcome with you:
 
Quote:

This succeeds in passing the value 12345 to the Oracle function:

Sub Test

Dim v_upload_id          As Integer

ExecutePLSQL "begin    :v_valid_code :=
purch_comm_codes.validate_comm_code(12345); end; "
v_valid_code =
SQLXL.Database.Parameters.BindVariables("v_valid_code").Value

end Sub

 
12345 is now passed as a hard coded parameter. That works fine.
v_valid_code is not declared in your VB. If you do not declare it it is equivalent to
Dim v_valid_code as Variant
The value from the function call will be stored in this variable.
 
Quote:

Whereas this doesn't pass any value:

Sub Test

Dim v_upload_id          As Integer

v_upload_id = 12345
ExecutePLSQL "begin    :v_valid_code :=
purch_comm_codes.validate_comm_code(:v_upload_id); end; "
v_valid_code =
SQLXL.Database.Parameters.BindVariables("v_valid_code").Value

end Sub

 
Again, here v_upload_id is not declared. It will be created by VB as a Variant equivalent to:
Dim v_upload_id As Variant
 
The parameter :v_upload_id does not exist yet. I understand you wish to make a link between the VB variable v_upload_id and the parameter but this is not how it works. Note however that the use of v_valid_code is correct although you have again not declared it explicitly.
 
If your code passes integers back and forth you would like to use:
 
Sub Test
     Dim int_valid_code As Integer
 
     'Give the PL/SQL for SQL*XL to parse
     SQLXL.Sql.setText "begin :v_valid_code := purch_comm_codes.validate_comm_code(:v_upload_id); end;"
 
     'Now the parameters are defined. They were created by SQL*XL in the previous statement
     'Set the value of the input parameter
        SQLXL.Database.Parameters.BindVariables("v_upload_id").Value = 12345
 
     'Now run the PL/SQL      
     With SQLXL.Sql.Statements(1).Execute  
 
     'After the run the output parameter was populated by the function.
     'Copy the value from the parameter to the visual basic variable
     int_valid_code = SQLXL.Database.Parameters.BindVariables("v_valid_code").Value
end Sub
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Tony Nilsson
YaBB Newbies
*




Posts: 9
Re: Parameter passing into a function
Reply #3 - 08.11.05 at 16:49:36
 
Hi Steve and Gerrit-Jan,
 
I had the same problem as you, But I think I solved the case with this code.
Hope that you Steve, can pick up something and use it in your VBA code.
 
This do wat I want it to do.
Run a simple SQL statment to create a customer number list in column A
Then parse these numbers into the function 'Customer_Mail' and putting the result from the PLSQL
on the same row in column B and C.
 
 
Sub Get_Customer_contacts()
    Dim Customer As Integer
    Dim V_List As String
    Dim row As Range
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Startsheet")
    Range("A16:I65536").ClearContents
            
    'Get Customer number into column A
    SQLXL.InitialiseSQLXL
    SQLXL.Database.ConnectionType = litSQLXLADO
   SQLXL.Database.connect ****
    
SQLXL.Sql.SetText "SELECT ppe$org.nr  FROM  ppe$org, ppe$org_pers,ppe$pers  WHERE ppe$org_pers.org_id = ppe$org.id  and ppe$pers.id = ppe$org_pers.psn_id  and ppe$org.kundutskick ='J'  and ppe$org_pers.roll = 'MO'  and ppe$pers.psn_type = 'ETN'  Group by ppe$org.nr"
    Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
    SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
    With Targets(litExcel)
        .AutoFilter = False
        .AutoFit = False
        .Headings = False
        .Sort = False
        .StartFromCell = "$A$16"
        .Transpose = False
        .SQLInNote = False
        .FormatData = False
        .FreezePanes = False
        .PasteInsert = False
    End With
    With SQLXL.Sql.Statements(1)
        .ShowParametersDlg = False
        .ShowResultsetDlg = False
        .Execute
    End With
    Range("A16").Select
    
    Range("instrang").Select
    
    'Get Result from function e.g mail list:  'nisse@nisse.com;max@max.com'
    For Each row In sh.Range("kundlista").Rows
              
        Range("Instrang").Value = ""
        If CStr(row.Cells().Value) = "" Then Exit For
 
        Range("Customer").Value = CStr(row.Cells().Value)
 
        'PLSQL code
        SQLXL.Sql.SetText "begin  :v_list := Customer_Mail(:Customer); end;  "
        SQLXL.Database.Parameters.BindVariables("Customer").Value = Range("Customer").Value
        SQLXL.Sql.Statements(1).Execute
        
        'Put the instring in one cell
        Range("instrang").Value = SQLXL.Database.Parameters.BindVariables("v_list").Value
            
        'Formel in 2 new cells (Name and Email) to split the instring into
        'customer name and Email column B and C
        'and then copy it to a cell on the same row as the customer number
        row.Cells(1, 2).Value = Range("namn").Value
        row.Cells(1, 3).Value = Range("email").Value
    Next
 
    Range("A16").Select
End Sub
Back to top
 
« Last Edit: 08.11.05 at 17:04:02 by Tony Nilsson »  
  IP Logged
u-1
YaBB Newbies
*


I Love SQL*XL

Posts: 3
Re: Parameter passing into a function
Reply #4 - 22.08.06 at 10:41:36
 
Hi.....
 
I also have a problem when using parameter.
 
i use mr.Gerrit-Jan's example code  
 
Sub Test()  
  SQLXL.Sql.setText "begin :p_out := :p_in * 2;  end;"  
  SQLXL.Database.Parameters.BindVariables("p_in").Value = 123  
  SQLXL.Database.Parameters.BindVariables("p_out").Value = 0  
  With SQLXL.Sql.Statements(1).Execute  
   
  MsgBox SQLXL.Database.Parameters.BindVariables("p_out").Value  
End Sub  
 
when I run this macro, error message occurs....
   application : litADO
   module      : clsbindVariables  
   routine       : addparametersToCommand  
   error no     : 3421
   error         : application uses a value of the wrong type for the current  
                     operation    
 
then also this error message occurs....
   ORA-01008 : Not all variables boud
 
 
Actually, I wanna update my database table with Variable's value.  
 
SQLXL.Sql.setText "update production set production_remarks = :p_out where prod_id = '20000815'"
  With SQLXL.Database.Parameters.BindVariables("p_out")
    .DataType = litTypevarchar2
    .Value = remark1
  End With
SQLXL.Sql.Statements(1).Execute
 
But the ORA-01008 error message occurs also. what's wrong with the code?
Back to top
 
 
  IP Logged
u-1
YaBB Newbies
*


I Love SQL*XL

Posts: 3
Re: Parameter passing into a function
Reply #5 - 30.08.06 at 09:37:27
 
I have solved my problem. It's all about the connection provider.  
 
strConnectionString = "Provider=MSDASQL.1;User ID=xy;DataSource=production" '
 
When I used that connection,  parameter problem occurs.
 
But, after i change  the provider from MSDASQL to MSDAORA, the problem is gone.  
 
 Smiley
 
 
 
Quote from u-1 on 22.08.06 at 10:41:36:
Hi.....

I also have a problem when using parameter.

i use mr.Gerrit-Jan's example code

Sub Test()
 SQLXL.Sql.setText "begin :p_out := :p_in * 2;  end;"
 SQLXL.Database.Parameters.BindVariables("p_in").Value = 123
 SQLXL.Database.Parameters.BindVariables("p_out").Value = 0
 With SQLXL.Sql.Statements(1).Execute
 
 MsgBox SQLXL.Database.Parameters.BindVariables("p_out").Value
End Sub

when I run this macro, error message occurs....
  application : litADO
  module : clsbindVariables
  routine : addparametersToCommand
  error no     : 3421
  error   : application uses a value of the wrong type for the current  
   operation  

then also this error message occurs....
  ORA-01008 : Not all variables boud


Actually, I wanna update my database table with Variable's value.

SQLXL.Sql.setText "update production set production_remarks = :p_out where prod_id = '20000815'"
 With SQLXL.Database.Parameters.BindVariables("p_out")
   .DataType = litTypevarchar2
   .Value = remark1
 End With
SQLXL.Sql.Statements(1).Execute

But the ORA-01008 error message occurs also. what's wrong with the code?

Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Parameter passing into a function
Reply #6 - 03.09.06 at 20:52:37
 
Hi,
 
This code is always sensitive to the driver you are using. I would like to review what you have done. Can you send your complete code to me please so I can try it here?
 
In the first code snippet you mention I would use a few extra statements to explitely set the datatype and direction of the parameter.
 
In the second code snippet you sent I would like to see the definition and the value of your variable remark1
 
When you contact me directly, would you also mind telling me the database type you used (Oracle, SQL Server, ...) and the driver you used to connect?
 
Best regards, Gerrit-Jan Linker
Back to top
 
 

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