Solved

Oracle Stored Procedure to return rowset.

Posted on 2001-06-13
10
1,224 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

615 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