Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1657
  • Last Modified:

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!
0
naga1979
Asked:
naga1979
  • 10
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

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.

  • 10
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now