Solved

Oracle Stored Procedure to return rowset.

Posted on 2001-06-13
10
1,217 Views
Last Modified: 2010-08-05
Hi, Oracle Experts.
I am working with Oracle 8.1.6 and ADO2.6 in VB6.
I need to use the recordset object of ADO2.6 to return some records from an Oracle stored procedure.
My questions:
1.I need to return a set of records from Customers table from an oracle Stored Procedure. How can I return it. (In SQL server I just write "Select * from Customers" and then I execute this SP from VB it will return me a records into a recordset object.)

2.Please paste some example of stored procedure and VB code for this purpose.

Thanks, RRR.
0
Comment
Question by:RRR
[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
  • 7
  • 3
10 Comments
 
LVL 2

Expert Comment

by:racher
ID: 6185445
I would use cursor variables.

Rather than me try and explain exactly what they are there is a good description in Chapter 5 in the PLSQL manual.
It explains the difference between strong and weak REF CURSORs as well.

Here is an example of how I've been using them with weak REF CURSORs

In a the package spec I have devined the following type  
  TYPE ref_cur_typ IS REF CURSOR;

In the package body I can now have functions like the one below. This one will return a REF CURSOR to a list of sectors for the given structure code ordered by sector_level. Our VB  and COM developers then use this packaged function.
Sorry I can't give an example of the VB code, I'm the Oracle expert on this project!

FUNCTION list_sector_names
    (par_industrial_structure_code     IN VARCHAR2)
      RETURN ref_cur_typ IS
  cv_sectors     ref_cur_typ;
BEGIN
  OPEN cv_sectors FOR
    SELECT s.description, s.sector_level
      FROM sectors s
      WHERE s.structure_code = par_structure_code
      ORDER BY s.sector_level;
  RETURN (cv_sectors);
END list_sector_names;
0
 
LVL 3

Author Comment

by:RRR
ID: 6199776
Hi racher .
Please explane me what is the package spec :

"In a the package spec I have devined the following type  
 TYPE ref_cur_typ IS REF CURSOR;"

and where I can paste this line.

As I understend, you suggest me to write a Function . Can I do it in a procedure or I must use a function?
If I must to use a function then please paste more details about this declaration of cursor type.

Thanks, RRR.
0
 
LVL 3

Author Comment

by:RRR
ID: 6202630


0
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!

 
LVL 2

Accepted Solution

by:
racher earned 300 total points
ID: 6202776
A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body.
The specification (spec for short) is the interface
to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms,
and so implements the spec.
I virtually always use packages.
For a full explanation see Chapter 8 in the PLSQL manual. This also lists the advantages of using packages.

In my book if a sub program only has one out parameter, it's a function not a procedure.

Hope that helps

Graham

0
 
LVL 3

Author Comment

by:RRR
ID: 6210039
Hi, racher.
I think your comments are helped me, but I steel do not understend how can I run my function in SQL Plus - I have a function that receives a numeric parameter and should return a cursor type parameter(set of rows). Where this rows should be pasted in SQL Plus into spool file or can I output it to a screen.
Here defenitions of my package and function :

Package:
MyPackage IS

    TYPE ref_cur_LRs IS REF CURSOR;
   
    FUNCTION MyFunction(par_examssortorder IN NUMBER)
    RETURN ref_cur_LRs;
     
END MyPackage;

Function:

MyFunction (par_examssortorder IN NUMBER)
    RETURN MyPackage.ref_cur_LRs
IS c_LRs MyPackage.ref_cur_LRs;
BEGIN
    OPEN c_LRs FOR
    SELECT *
    FROM "SYSTEM"."LRs" s
          WHERE s.EXAMSSORTORDER = par_examssortorder
          ORDER BY s.EXAMNAME;
       
        RETURN (c_LabResults);  
END MyFunction ;

Thanks. RRR.
0
 
LVL 3

Author Comment

by:RRR
ID: 6210043
Hi, racher.
I think your comments are helped me, but I steel do not understend how can I run my function in SQL Plus - I have a function that receives a numeric parameter and should return a cursor type parameter(set of rows). Where this rows should be pasted in SQL Plus into spool file or can I output it to a screen.
Here defenitions of my package and function :

Package:
MyPackage IS

    TYPE ref_cur_LRs IS REF CURSOR;
   
    FUNCTION MyFunction(par_examssortorder IN NUMBER)
    RETURN ref_cur_LRs;
     
END MyPackage;

Function:

MyFunction (par_examssortorder IN NUMBER)
    RETURN MyPackage.ref_cur_LRs
IS c_LRs MyPackage.ref_cur_LRs;
BEGIN
    OPEN c_LRs FOR
    SELECT *
    FROM "SYSTEM"."LRs" s
          WHERE s.EXAMSSORTORDER = par_examssortorder
          ORDER BY s.EXAMNAME;
       
        RETURN (c_LRs);  
END MyFunction ;

Thanks. RRR.
0
 
LVL 3

Author Comment

by:RRR
ID: 6210047
My before last comments have some errors. Ignore it. The last comments are good.
RRR.
0
 
LVL 3

Author Comment

by:RRR
ID: 6213967
racher, I increase the points.
I need it ASAP, thanks.
RRR.
0
 
LVL 2

Expert Comment

by:racher
ID: 6214246
Chapter 29 of the "Supplied PL/SQL Packages Reference" Manual has the answer - DBMS_OUTPUT

e.g.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE (?hello?);
END;
/


Here is an example of the testing one of my procedures

SET SERVEROUTPUT ON
DECLARE
  TYPE ref_cur_typ IS REF CURSOR;
  v_cur ref_cur_typ;
  TYPE v_rec IS RECORD
    (fund_code               funds.fund_code%TYPE
    ,fund_name               funds.fund_name%TYPE
    ,fund_manager_code          funds.fund_manager_code.id%TYPE);
  v_list v_rec;
BEGIN
  v_cur := dp_gpa_fund.list_funds(2046,'N','fund_name');
  LOOP
    fetch v_cur INTO v_list;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_list.fund_code ||' '|| v_list.fund_name);
  END LOOP;
END;
/

NB you may need to increase the size of the sqlplus buffer

Graham Racher
0
 
LVL 3

Author Comment

by:RRR
ID: 6223840
Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

734 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