Solved

How to write stored procedure ??

Posted on 2000-05-01
2
142 Views
Last Modified: 2010-05-02
For Oracle, it cannot return a set of records by using stored procedure
like using MSSQL.

How can I write the stored procedure in Oracle that can return
record by record or similar things so that I can collect it in VB.

Thanks !!

0
Comment
Question by:ssflo
[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
2 Comments
 
LVL 6

Expert Comment

by:Marine
ID: 2765978
Oh thats going to be very hard, i tried once and couldn't do that as it's done in SQL Server. Maybe someone can shade some light for both of us on this.
0
 

Accepted Solution

by:
garymather earned 10 total points
ID: 2766072
Here's how I do it.

1. Create a package. This is need for the REF CURSOR declaration:

CREATE PACKAGE Test_Pack AS
  TYPE RecSet IS REF CURSOR;
END Test_Pack;
/

CREATE PROCEDURE Test_Proc(RecordSet OUT Test_Pack.RecSet) IS
BEGIN
   OPEN RecordSet FOR
      SELECT * FROM All_Objects;
END Test_Proc;
/

Now in VB:

Dim con      As ADODB.Connection
Dim cmd      As ADODB.Command
Dim rs            As ADODB.Recordset

‘ Open a connection to the database
Set con = New ADODB.Connection
With con
   .ConnectionString = “DSN=OraLocal; UID=system; PWD=;”
   .Open
End With

Set cmd = New ADODB.Command
With cmd
   .ActiveConnection = con
   .CommandText = “{call Test_Proc(?)}”
   .CommandType = adCmdText .Parameters.Append .CreateParameter(, 2, adParamOutput)
   Set rs = cmd.Execute
End With

With rs
   Do While Not .EOF
      Debug.Print !Owner, !Object_Name
      .MoveNext
   Loop
End With

con.Close
Set con = Nothing


Hope this helps...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

752 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