Saving the return value of oracle function in a string

[lionne]

Dear all,
I could hardly find any information on the following problem:
I need to call an Orcale function from VBA Excel and save its return value to a string variable. Here is the code snippet:
[VBA]
Private Sub obj_classif()

Dim cn As New ADODB.Connection
Dim strSQL As String
Dim db_name, Username, Password As String

Dim rs_astype As String

Set wb = Excel.ActiveWorkbook
Set ih = wb.Sheets("InfoSheet")
db_name = ih.Cells(1, 2) 'Instance name
Username = ih.Cells(2, 2) 'Login
Password = ih.Cells(3, 2) 'Password

Set cn = New ADODB.Connection
cn.ConnectionTimeout = 600
cn.Open db_name, Username, Password

strSQL = "declare result_line varchar2(2000) := ''; " & _
" begin result_line := package.function('caller_id', 120); " & _
" commit; " & _
" dbms_output.put_line(result_line); " & _
" commit; " & _
" end;"

rs_astype = cn.Execute(strSQL) 'I get an error here, saying: Compile error: Types are incompatible.
cn.close
Set cn = Nothing
End Sub
[/VBA]

When I run this in SQL Developer, I get a semicolon-separated string in dbms_output.

What do I do wrong?

Any hints are highly appreciated.
Lionna

[Kenneth Hobs]

Offhand, I would say that rs_astype is returning a boolean variable type with values of true or false for the right side. You defined it as a string type variable.

I don't know what dbms_output is. If you are going to run it in Excel's VBA, you need to reference that object if that can be done. If it has a put_line method then maybe it has a Text or Value property. I suspect that you need another method to return the string that you want.

[SamT]

As to the Incompatible Types Error, I noticed the you are declaring dbName and UserName as Variants, not Strings.
[VBA]Dim db_name As String, Username As String, Password As String [/VBA]
HTH

[lionne]

Thank you guys for the inputs. I have modified the code, so I don't get an error now, but I still can't save the return value of a function into a variable. My rs_astype is empty. Either way of calling a function will not return me a recordset. Any ideas?

[vba]
Private Sub obj_classif()

Dim cn As New ADODB.Connection
Dim strSQL As String
Dim db_name, Username, Password As String

Dim rs_astype As Variant

Set wb = Excel.ActiveWorkbook
Set ih = wb.Sheets("InfoSheet")
db_name = ih.Cells(1, 2) 'Instanzname
Username = ih.Cells(2, 2) 'Login
Password = ih.Cells(3, 2) 'Passwort

Set cn = New ADODB.Connection
cn.ConnectionTimeout = 600
cn.Open db_name, Username, Password

strSQL = "declare result_line varchar2(2000) := ''; " & _
" begin result_line := package.function('STG_DATA_REQUEST', 120); " & _
" dbms_output.put_line(result_line); " & _
" commit; " & _
" commit; " & _
" end;"


' strSQL = "select package.function('STG_DATA_REQUEST', 120) from dual"

Set rs_astype = cn.Execute(strSQL)

cn.close
Set cn = Nothing
[/vba]

[snb]

What is the result in sn using:
[vba]Private Sub obj_classif()
With New ADODB.Connection
.ConnectionTimeout = 600
.Open Sheets("InfoSheet").Cells(1, 2).Value, Sheets("InfoSheet").Cells(2, 2).Value, Sheets("InfoSheet").Cells(3, 2).Value
sn = .Execute("declare result_line varchar2(2000) := ''; begin result_line := package.function('STG_DATA_REQUEST', 120); dbms_output.put_line(result_line);commit;commit;end;")
.Close
End With
End Sub[/vba]

[lionne]

Thanks snb, the code runs through, but:

how can I have a look on a return value? Is sn a Variant type?

This
[VBA]
MsgBox sn
[/VBA]

or this
[VBA]
Sheets("InfoSheet").Cells(5, 1).Value = sn
[/VBA]

throw an 1004 application or object-defined error. How do I save this return value?

[snb]

first use
[vba]
Msgbox typename(sn)
[/vba]

It it's 'String' you can write it directly into the sheet
if it's "Variant()" it's an array that you can write into the sheet using:

[VBA]
cells(1).resize(ubound(sn),ubound(sn,2))=sn
[/VBA]

[lionne]

thank you for the hint. Interestingly, it returns the string "Fields".. no idea where this comes from... The string I expect should look like "Text;Number;Text;Number;etc."

I also have difficulties with this:
[VBA]
cells(1).resize(UBound(sn),UBound(sn,2))=sn[/VBA]

never seen syntax "Cells(1)." How do I know from this, which worksheet it belongs to? And what is "1" is staying for here?

[snb]

You can always add
[VBA]sheets("infosheet").cells(1).value[/VBA]

cells(1) in this case is equivalent to Range("A1") or cells(1,1)

To continue on sn

[VBA]For each it in sn
msgbox it
next[/VBA]
of
[VBA]For each it in sn
msgbox it.name
next[/VBA]

[Kenneth Hobs]

Interesting, I would have thought Fields would be the word "Object" from the Typename() function. Try using Set and then a property of the Fields object.

e.g.
[vba]set rs_astype = cn.Execute(strSQL)
MsgBox rs_astype.Count
MsgBox rs_astype(1).Name
MsgBox rs_astype(1).Value[/vba]