Calling Oracle Stored Procedure

Dear Experts,

I have a stored procedure in the Oracle Database. The procedure take two input parameters and ouputs a cursor to the client. I want to call this procedure from my VB application. I am using MS ODBC driver for Oracle to connect to the server. I tried calling but getting some errors. I dont think i am using correct way of calling it. Can you pls tell me how to do this in steps.

I want to get the cursor and navigate through it to display the data.

Thanks

Cheers!
LVL 1
naga1979Asked:
Who is Participating?
 
sudhakar_koundinyaConnect With a Mentor Commented:
oops forgot add other code



msSelect = "MY_PKG.GETINFO"
           
With mCmd
.CommandText = msSelect
.CommandType = adCmdStoredProc
.ActiveConnection = mCnn
.Parameters.Append .CreateParameter
(, adVarChar, adParamInput, 20)
.Parameters.Append .CreateParameter _
(, adVarChar, adParamInputOutput, 50)
End With

 
 mCmd(0) = "YourUserInfoFromVB"
mCmd(1) = "YourEnvironmentInfoFromVB"

and also gthrough this URL great help for you

http://www.visualbasicforum.com/showthread.php?t=2079
0
 
sudhakar_koundinyaCommented:
0
 
sudhakar_koundinyaCommented:
if you know some ASP stuff that is similar to VB

go through this link also

http://www.learnasp.com/learn/oraclerecordsetsado.asp  (USES ADO concept)
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
sudhakar_koundinyaCommented:
0
 
sudhakar_koundinyaCommented:
0
 
naga1979Author Commented:
Dear All,

My procedure looks like:

PROCEDURE GetInfo
              (userid             IN      VARCHAR2,
            environment           IN      VARCHAR2,
            cursor            OUT         CURSOR_TYPE
                )
IS
BEGIN
      OPEN cursor FOR

      SELECT USERID, ENVIRONMENT, ATTRIBUTE_NAME, ATTRIBUTE_VALUE
      FROM MYTABLE
      WHERE userid = userid
      AND   environment = environment;
END;

I worked with following code, it didn't work.

Private Sub Command1_Click()

Dim mCnn As ADODB.Connection
Dim mrsSelect As ADODB.Recordset
Dim mCmd As ADODB.Command
Dim msSelect As String
Dim mCmdPrmGender As New ADODB.Parameter

Dim sConnect As String   'Declare our connection string
  sConnect = "Data Source=MYSERVER" & ";" & _
             "User ID=USER" & ";" & _
             "Password=PWD" & ";"

  Set mCnn = New ADODB.Connection
  With mCnn
     .CommandTimeout = 10
     .CursorLocation = adUseClient
     .Provider = "MSDAORA"
     .Open sConnect

  End With


  'resultset is a keyword, fname the name of the table in the proc.

msSelect = "{call my_pkg.GetInfo(?,?, " & _
                  "{resultset 1000, cursor})}"
                 
  Set mCmd = New ADODB.Command

  With mCmd
     .CommandText = msSelect
     .CommandType = adCmdText
     .ActiveConnection = mCnn

     Set mCmdPrmGender = .CreateParameter("userid", adVarChar, _
      adParamInput, 20, "user")
     .Parameters.Append mCmdPrmGender
     Set mCmdPrmGender = .CreateParameter("environment", adVarChar, _
      adParamInput, 20, "test")
     .Parameters.Append mCmdPrmGender
     
  End With

  Set mrsSelect = New ADODB.Recordset

  Set mrsSelect = mCmd.Execute
  MsgBox mrsSelect.Fields(0)
End Sub


The error is as follows:

ORA-01060: array binds or executes not allowed
Cause: The client application attempted to bind an array of cursors or attempted to repeatedly execute against a PL/SQL block with a bind variable of type cursor.

Action: Bind a single cursor or execute the PL/SQL block one.

Pls tell me how to do this.

Cheers!
0
 
sudhakar_koundinyaCommented:
With mCmd
.CommandText = msSelect
.CommandType = adCmdStoredProc
.ActiveConnection = mCnn
.Parameters.Append .CreateParameter
(, adVarChar, adParamInput, 20)
.Parameters.Append .CreateParameter _
(, adVarChar, adParamInputOutput, 50)
End With

check this and replace your code with this and try
0
 
naga1979Author Commented:
Dear Sudhakar,

Thanks. It's working fine. But tell me why the connection object is expecting the provider property to be set to "MSDAORA". Because we have already created the DSN using that driver. I need some explanation.

Cheers!
0
 
sudhakar_koundinyaCommented:
DSN is unnecessary whe you use OLEDB/ADODB concepts.

though you have created you have not created the connection object through DSN concept, you have used ADODB concept and it asks for the driver that's it.

0
 
sudhakar_koundinyaCommented:

and I am eager to see some more points in my total points  :-)

Good Luck for your further coding

Cheers
0
 
sudhakar_koundinyaCommented:
Instead of DSN you can provide PROVIDER Name while creating connection string itself

0
 
sudhakar_koundinyaCommented:
for got added syntax

'Please change the below connection string as per your server and database being used.
 connection.ConnectionString = "PROVIDER=SQLOLEDB.1;PASSWORD=;PERSIST SECURITY INFO=TRUE;USER ID=sa;INITIAL CATALOG=TestSQL;DATA SOURCE=Rockets"

0
 
naga1979Author Commented:
Dear Sudhakar,

Thanks.

Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.