[RESOLVED] Call Oracle SP from VB

[xblade]

Please help...

I'm new to Oracle and need to call a oracle stored procedure from vb. The stored procedure will eventually have many select statements, but for now I need to execute a simple select statements and display the results in VB. Can anyone assist as I'm not sure what to do next.

Oracle

CREATE OR REPLACE PROCEDURE TEST
AS
BEGIN
  EXECUTE IMMEDIATE 'SELECT * FROM S_USERS';
END;

VB - Form

Private Sub Command1_Click()
  Open_Connection
  
  SQL ("CALL TEST()")
  
  'SQL ("SELECT * FROM S_USERS")
  'SQL ("EXECUTE TEST")
 
  MsgBox RsSQL.RecordCount
  
  Set Cn = Nothing
  Set RsSQL = Nothing
End Sub

VB - Module

Option Explicit
 
     'Database connections
     Public Cn As New ADODB.Connection
 
     'Record connections
     Public RsSQL As New ADODB.Recordset
 
 
Public Function Open_Connection() As Boolean
  'Set Connection
  Set Cn = New ADODB.Connection
    
  'Connection to Database
  With Cn
    .ConnectionTimeout = 5
    .CommandTimeout = 5
    '.ConnectionString = "Provider=SQLOLEDB.1;Data Source=***;Database=***;User ID=***;Password=***;Persist Security Info=True"
    .ConnectionString = "Provider=OraOLEDB.Oracle.1;Data Source=***;Database=***;User ID=***;Password=***;Persist Security Info=True"
    
    .Open
  End With
  
  'Connection succeded
  Open_Connection = True
End Function
 
Public Sub SQL(SQL As String)
  'Set Recordset
  Set RsSQL = New ADODB.Recordset
    
  'Record set SQL
  With RsSQL
    .CursorLocation = adUseClient
    
    .Open SQL, Cn, adOpenKeyset, adLockOptimistic
  End With
End Sub

Using : Oracle 8i, VB6.0 SP5

Thanks in advance

[GaryMazzone]

You are going to need parameters for your SP. If you are going to pass in the SQL statement you will need at least 2 paramters one is an In and the other is an Out paramter.

Create or Replace Procedure Test (
      pSQL IN Varchar2,
      pRet  OUT Ret_S_Users
)
Begin

Open pRet For 
       Select * From S_USERS

End;
/

The you will need to define Ret_S_Users as a Cursor somewhere in a package on the database.

[xblade]

Thanks for the response.

Here's what I have done so far...

------------------------
CREATE OR REPLACE PACKAGE Ret_S_Users AS
TYPE cur IS REF CURSOR;
END Ret_S_Users;
/

-------------------
CREATE OR REPLACE Procedure Test
(
pSQL IN Varchar2,
pRet OUT Ret_S_Users.cur
)
IS
Begin

Open pRet For
Select * From S_USERS;

End;
/

What parameter's do I need to parse into Test and will I be able to EXECUTE the statements from TOAD with the following statement CALL TEST('???'); to test the sp.

[GaryMazzone]

This is how I have always done this:

CREATE OR REPLACE PACKAGE cv_types AS
  
  
  /*  
    Record type for Platforms     
  */
  TYPE REC_s_users IS Record (  
   fname         VARCHAR2(15),
   lname          VARCHAR2(25)
  );
  TYPE CV_REC_s_users IS REF CURSOR RETURN REC_s_users;
  
End cv_types;
/


CREATE OR REPLACE PROCEDURE SP_GET_S_USERS (
     psuers	OUT cv_types.CV_REC_PLAT_ITEMS2 )
          
AS
BEGIN

	OPEN psuers FOR
    	SELECT  
     		fname,
      		lname,
  	FROM  
		s_users
  	ORDER BY 
		lname,fname;

  
EXCEPTION 
  WHEN OTHERS THEN         
      ROLLBACK WORK;
      RAISE;
END SP_GET_S_USERS;
/

if VB

Set adoCommand = New ADODB.Command
With adoCommand
    .ActiveConnection = mdlGeneral.gDBConn
    .CommandType = adCmdStoredProc
    .CommandText = "SP_GET_S_USERS"
.Parameters.Append .CreateParameter(, adBSTR, adParamOutput, , rs)End With
adoCommand.Execute

I'm not sure of the RS value, do a search and there are examples of pulling the rs back from the procedure.

I have always done my test in SQL*Plus. I have attached a word doc to this with an example of doing this.

[xblade]

Thanks Gary for the help...
your examples really helped a lot. Tested my stored proc in sqlplus and vb and it's working. !!!! COOL !!!!!

[Hack]

If you have found a resolution to this problem, please pull down the Thread Tools menu and click the Mark Thread Resolved button. That will let everyone know that you have your answer