ksummers
asked on
Oracle PKG and cursor output
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/
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;
/
call tax_lines.GetSummary('5/1/
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;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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 >
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
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 >
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;
/
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;
/
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
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
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?
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?
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;
/