Solved

How to write stored procedure ??

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

776 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