Help - Calling a Oracle Package function from VB Code

G

Geeta

I am trying to execute a Function in Oracle package from VB , but the app is
giving me an error "Invlaid SQL statement at rs = cm.execute"

Here is my function and VB code
====================

FUNCTION GetSetting(p_section IN VARCHAR2, p_name IN VARCHAR2) RETURN
VARCHAR2 IS
setting_value VARCHAR2(4000);
BEGIN
SELECT cs.VALUE
INTO setting_value
FROM CONFIG_SETTING cs
WHERE cs.SECTION = p_section
AND cs.NAME = p_name;
RETURN setting_value;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;

=======
VB Code
=====

Sub TESTFUNCTION()
Dim rs, sq, pkey, teststr, cm, constr

Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Set cm = CreateObject("adodb.command")

conn.Open "Provider=OraOLEDB.Oracle.1;Server=mdcx;Data Source=mdcx;User
Id=mdma;Password=mdma1;"

Set cm.ActiveConnection = conn
cm.CommandText = "P_CONFIG_SETTING.getsetting(P1,P2)"
cm.CommandType = 1

'Create and Append the parameters

cm.Parameters.Append cm.CreateParameter("P1", 129, 1, 20, "ROUTE_ROLLOVER")
cm.Parameters.Append cm.CreateParameter("P2", 129, 1, 20, "TO_ADDRESS")

' Execute the stored procedure

rs = cm.Execute

If Not rs.EOF Then
MsgBox "got some rows"
End If

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

End Sub

Any help is highly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top