Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calling Oracle Stored Procedure

Posted on 2003-11-18
13
Medium Priority
?
1,652 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Suggested Courses

705 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