Solved

How to write stored procedure ??

Posted on 2000-05-01
2
141 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
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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 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…
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…

740 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