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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
sudhakar_koundinyaCommented:
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.