Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Stored Procedure to return rowset.

Posted on 2001-06-13
10
Medium Priority
?
1,244 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 2

Accepted Solution

by:
racher earned 1200 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

886 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