Solved

Oracle Stored Procedure to return rowset.

Posted on 2001-06-13
10
1,210 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

863 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

27 Experts available now in Live!

Get 1:1 Help Now