Solved

How to write stored procedure ??

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now