Solved

How to write stored procedure ??

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 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…
Suggested Courses
Course of the Month7 days, 11 hours left to enroll

632 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