?
Solved

Oracle PKG and cursor output

Posted on 2007-08-08
7
Medium Priority
?
676 Views
Last Modified: 2013-12-18
I need help with an Oracle package PKG that I am currently working on.  I need more understanding on how cursors work and give output.  I want my procedure will take in two date parameters and return a table based on my SQL query.  I want to call the package / procedure in the SQL Editor like,

call tax_lines.GetSummary('5/1/2005 12:00:00 AM', '5/31/2005 12:00:00 AM')

When I call the package, nothing is returned in table form.  What am I doing wrong?  Here is the package code.

CREATE OR REPLACE PACKAGE TAX_LINES AS
 
  PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date);
 
END WELLS_TAX_LINES;
/
 
CREATE OR REPLACE PACKAGE BODY TAX_LINES AS
 
  PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date) is
 
  cursor c_Count is
    SELECT * FROM LOANS WHERE loan_posting_date > POSTEDA
               AND loan_posting_date < POSTEDB;

  begin
    open c_Count;
    close c_Count;
  end;
 
END TAX_LINES;
/



0
Comment
Question by:ksummers
7 Comments
 
LVL 23

Accepted Solution

by:
paquicuba earned 2000 total points
ID: 19655210
Follow this example (You need a ref cursor)

PAQUI@DEV > CREATE OR REPLACE PACKAGE TAX_LINES AS
  2
  3    PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date, c_Count OUT SYS_REFCURSOR );
  4
  5  END TAX_LINES;
  6  /

Package created.

Elapsed: 00:00:00.00
PAQUI@DEV >
PAQUI@DEV > CREATE OR REPLACE PACKAGE BODY TAX_LINES AS
  2
  3    PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date, c_Count OUT SYS_REFCURSOR ) is
  4     begin
  5     open c_Count for
  6      SELECT 1 COL1, 2 COL2 FROM DUAL WHERE TRUNC(SYSDATE) BETWEEN POSTEDA AND POSTEDB;
  7    end;
  8
  9  END TAX_LINES;
 10  /

Package body created.

Elapsed: 00:00:01.04
PAQUI@DEV > VARIABLE REFCUR REFCURSOR

PAQUI@DEV > EXEC TAX_LINES.GetSummary(TRUNC(SYSDATE), TRUNC(SYSDATE),:REFCUR);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18
PAQUI@DEV > PRINT REFCUR

      COL1       COL2
---------- ----------
         1          2

Elapsed: 00:00:00.01
PAQUI@DEV >
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19655234
CREATE OR REPLACE PACKAGE TAX_LINES AS
 
  PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date, c_Count OUT SYS_REFCURSOR );
 
END TAX_LINES;    -- ¿¿¿¿WELLS_TAX_LINES ????
/
 
CREATE OR REPLACE PACKAGE BODY TAX_LINES AS
 
  PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date, c_Count OUT SYS_REFCURSOR ) is
 
  begin
    open c_Count is
    SELECT * FROM LOANS WHERE loan_posting_date > POSTEDA
               AND loan_posting_date < POSTEDB;
  end;
 
END TAX_LINES;
/
0
 

Author Comment

by:ksummers
ID: 19655369
Sorry, I edited the create statement when I posted the question.  WELLS_TAX_LINES  to TAX_LINES but that is not the problem.  Sorry for that typo here.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 23

Expert Comment

by:paquicuba
ID: 19655496
No problem. It's simple, look at this example:

PAQUI@DEV > CREATE TABLE LOANS ( LOAN_NUMBER NUMBER, LOAN_OWNER VARCHAR2(20), LOAN_POSTING_DATE DATE);

Table created.

Elapsed: 00:00:00.15
PAQUI@DEV > INSERT INTO LOANS VALUES(1,'JOHN',SYSDATE-7);

1 row created.

Elapsed: 00:00:00.12
PAQUI@DEV > INSERT INTO LOANS VALUES(2,'LAURA',SYSDATE-5);

1 row created.

Elapsed: 00:00:00.01
PAQUI@DEV > INSERT INTO LOANS VALUES(3,'BILL',SYSDATE-2);

1 row created.

Elapsed: 00:00:00.00
PAQUI@DEV > COMMIT;

Commit complete.

Elapsed: 00:00:00.04
PAQUI@DEV > SELECT * FROM LOANS;

LOAN_NUMBER LOAN_OWNER           LOAN_POST
----------- -------------------- ---------
          1 JOHN                 01-AUG-07
          2 LAURA                03-AUG-07
          3 BILL                 06-AUG-07

Elapsed: 00:00:00.15
PAQUI@DEV > CREATE OR REPLACE PACKAGE TAX_LINES AS
  2
  3    PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date, c_Count OUT SYS_REFCURSOR );
  4
  5  END TAX_LINES;
  6  /

Package created.

Elapsed: 00:00:00.01

PAQUI@DEV > CREATE OR REPLACE PACKAGE BODY TAX_LINES AS
  2
  3    PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date, c_Count OUT SYS_REFCURSOR ) is
  4
  5    begin
  6      open c_Count FOR
  7      SELECT * FROM LOANS WHERE loan_posting_date > POSTEDA
  8                 AND loan_posting_date < POSTEDB;
  9    end;
 10
 11  END TAX_LINES;
 12  /

Package body created.

Elapsed: 00:00:00.12
PAQUI@DEV > VARIABLE REFCUR REFCURSOR
PAQUI@DEV > EXEC TAX_LINES.GetSummary(TRUNC(SYSDATE-10), TRUNC(SYSDATE),:REFCUR);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
PAQUI@DEV > PRINT REFCUR

LOAN_NUMBER LOAN_OWNER           LOAN_POST
----------- -------------------- ---------
          1 JOHN                 01-AUG-07
          2 LAURA                03-AUG-07
          3 BILL                 06-AUG-07

Elapsed: 00:00:00.01
PAQUI@DEV >
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19655511
Just use the metadata below, declare a refcursor variable and print it (You can also use a function if you don't want to print the variable)

CREATE OR REPLACE PACKAGE TAX_LINES AS
 
  PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date, c_Count OUT SYS_REFCURSOR );
 
END TAX_LINES;
/
 
CREATE OR REPLACE PACKAGE BODY TAX_LINES AS
 
  PROCEDURE GetSummary(POSTEDA in date, POSTEDB in date, c_Count OUT SYS_REFCURSOR ) is
 
  begin
    open c_Count FOR
    SELECT * FROM LOANS WHERE loan_posting_date > POSTEDA
               AND loan_posting_date < POSTEDB;
  end;
 
END TAX_LINES;
/
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 19655764
Dude,
Just to look at this from a different angle. Instead of giving you the answer to what is wrong syntactically, consider what your program does, what you want it to do and in general, where the gap is:
What id DOES is the following:
Procedure is invoked...
Open a cursor.
Close a cursor.
End.

The main comments/problems with your procedure are as follows:
1. As a procedure, you require an out parameter if you want to actually return something to the calling program unit.
2. Alternatively, when a single value is returned, we typically structure as a function. (e.g. FUNCTION GetSummary(POSTEDA in date, POSTEDB in date) RETURN SYS_REFCURSOR...
3. If a ref cursor is not your thing, you select the data into an Associative array (known as index-by tables in 9i and simply pl/qsl tables in 8i).  The benefit here is that if you actually need to do processingon the data before you pass it out, you can.  Further, if required, you can also retrieve bulk collect into a PL/SQL table / associative array. To do this however you'll need to define the record type and table of records (i.e. the array). You can then select into through a CURSOR FOR LOOP.

Regards,
JT
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19657334
The simplest Oracle PL\SQL procedures *DO NOT* return record sets, or arrays, etc.!  This seems to be an assumption that many people new to Oracle make, but it is *NOT* true in Oracle!  Yes, it is possible to get an Oracle PL\SQL procedure to give you a "ref cursor" or an array, but the syntax for this is more complex than the simple package you posted.

What is the business problem you are hoping to solve with this technique?  Or, what is the programming language or tool that you would like to get a "table" of results returned to?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

840 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