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:

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


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.
Dim db_name As String, Username As String, Password As String
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?


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

[snb]

What is the result in sn using:
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

[lionne]

Thanks snb, the code runs through, but:

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

This

MsgBox sn


or this

Sheets("InfoSheet").Cells(5, 1).Value = sn


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

[snb]

first use

Msgbox typename(sn)


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:


cells(1).resize(ubound(sn),ubound(sn,2))=sn

[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:

cells(1).resize(UBound(sn),UBound(sn,2))=sn

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
sheets("infosheet").cells(1).value

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

To continue on sn

For each it in sn
msgbox it
next
of
For each it in sn
msgbox it.name
next

[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.
set rs_astype = cn.Execute(strSQL)
MsgBox rs_astype.Count
MsgBox rs_astype(1).Name
MsgBox rs_astype(1).Value