Solved

Calling Oracle Stored Procedure

Posted on 2003-11-18
13
1,639 Views
Last Modified: 2010-05-01
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!
0
Comment
Question by:naga1979
  • 10
  • 3
13 Comments
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9776679
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9776693
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
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9776701
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9776705
0
 
LVL 1

Author Comment

by:naga1979
ID: 9776869
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
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9777197
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
 
LVL 14

Accepted Solution

by:
sudhakar_koundinya earned 250 total points
ID: 9777217
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
 
LVL 1

Author Comment

by:naga1979
ID: 9777508
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
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9778493
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
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9778513

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

Good Luck for your further coding

Cheers
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9778675
Instead of DSN you can provide PROVIDER Name while creating connection string itself

0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9778702
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
 
LVL 1

Author Comment

by:naga1979
ID: 9784670
Dear Sudhakar,

Thanks.

Cheers!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pull multiple cvs files into one access table 28 68
Input past end of file vbs script 9 91
Using "ScreenUpdating" 6 63
Run code from text file in vb 1 64
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question