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
Solved

Oracle Stored Procedure to return rowset.

Posted on 2001-06-13
10
1,212 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
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 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 …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

856 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