Home » SQL & PL/SQL » SQL & PL/SQL » Paging via Stored procedures
( ) 1 Vote
| Paging via Stored procedures [message #114768] |
Tue, 05 April 2005 09:40  |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Hello,
I come from a MS SQL Server background. (Limited experience) I have seen and used stored procedures in MS SQL via does the paging. It basically returns the desired results instead of all results. An example of such a stored proceudre would be:
CREATE PROCEDURE sp_PagedItems
(
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
Name varchar(50),
Price currency
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
Or a prefered way:
CREATE PROCEDURE GetSortedPage(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON
DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)
SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))
IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)
END
ELSE
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)
END
RETURN 0
GO
Now I would like to write the same stored proceedure for Oracle SQL. However I do not have much experience with oracle and would love if somebody could guide me.
Thanks in advance
|
|
|
|
|
|
| Re: Paging via Stored procedures [message #114784 is a reply to message #114768] |
Tue, 05 April 2005 11:37  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Result sets are returned from stored procedures through the use of a ref (reference) cursor parameter. The built-in type for this in 9i and above is sys_refcursor.
Although Oracle has something called global temporary tables, temp tables are not used (or needed) in Oracle nearly as often as they are in SQL Server.
Here is an outline to get you started:
create or replace procedure p_paged_items
(
p_page in pls_integer,
p_recs_per_page in pls_integer,
p_rc out sys_refcursor
)
is
begin
open p_rc for
select name, price
from (select t.*, rownum rn
from (select name, price
from tblitem
order by price) t
where rownum <= (p_page * p_recs_per_page)
where rn >= ((p_page * p_recs_per_page) - p_rec_per_page + 1);
end;
/
There is substantial overhead (in either database) to return a flag indicating whether or not there are more rows to follow. I have not included that indicator in this example.
For more info on returning result sets, see:
http://asktom.oracle.com/~tkyte/ResultSets/index.html
|
|
|
|
| Re: Paging via Stored procedures [message #114785 is a reply to message #114768] |
Tue, 05 April 2005 11:41 |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Well that looks ok, but it is a simplified version. What if I sorted my result then I wont get the desired result. Thats why I the first SQL SP creates a temp table and fetches that temp table. However I can still make use of it. The only thing I think left is to create a temp table in a stored procedure in Oracle. Do you know how to do that?
Thanks.
|
|
|
|
|
|
| Re: Paging via Stored procedures [message #114789 is a reply to message #114768] |
Tue, 05 April 2005 11:53 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Todd had a more complete answer than I did, I wasn't even thinking of the returning results issue, just the paging issue. And he is right that in Oracle it is rare to really need temporary tables. Although I hear it is common in sqlserver and a common source of confusion among people moving from sqlserver to oracle.
Why do you think you need one here? After you read Todd's response, and the asktom link each of us referenced, type up your solution and post it here so we can see.
But you can put complete queries inside the from clause (called an inline view) that have order by's of their own, separate from the order by of the outer query. I'm not sure why the technique wouldn't work regardless of the specific query in question.
|
|
|
|
| Re: Paging via Stored procedures [message #114792 is a reply to message #114784] |
Tue, 05 April 2005 12:15 |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Hello Tod,
I am getting two error:
Line 7: Missing right parenthesis
Line 11: SQL statement ignored.
I included the missing parenthesis, but it still shows the error.
PROCEDURE GETALLColors1
(
p_page in pls_integer,
p_recs_per_page in pls_integer,
p_rc out sys_refcursor
)
AS
BEGIN
open p_rc for
select DISTINCT INVN_SBS.ATTR
from (select t.*, rownum rn
from (select DISTINCT INVN_SBS.ATTR
from INVN_SBS
order by INVN_SBS.ATTR) t
where rownum <= (p_page * p_recs_per_page)
where rn >= ((p_page * p_recs_per_page) - p_rec_per_page + 1));
end GETALLColors1;
Thanks
|
|
|
|
| Re: Paging via Stored procedures [message #114793 is a reply to message #114792] |
Tue, 05 April 2005 12:27 |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Update:
If I run this SQL then it works:
If I run the following code then it works:
select *
from (select t.*, rownum rn
from (select INVN_SBS.ATTR
from INVN_SBS
order by INVN_SBS.ATTR) t
where rownum <= (2 * 5))
where rn >= ((2 * 5) - 5 + 1);
However if I run the same code with the variables, then I get an error in the last line:
P_REC_PER_PAGE: Invalid identifier.
Here is again my complete sp:
PROCEDURE GETALLColors1
(
p_page in pls_integer,
p_recs_per_page in pls_integer,
p_rc out sys_refcursor
)
AS
BEGIN
open p_rc for
select *
from (select t.*, rownum rn
from (select INVN_SBS.ATTR
from INVN_SBS
order by INVN_SBS.ATTR) t
where rownum <= (p_page * p_recs_per_page))
where rn >= ((p_page * p_recs_per_page) - p_rec_per_page + 1);
end GETALLColors1;
THanks
|
|
|
|
|
|
| Re: Paging via Stored procedures [message #114796 is a reply to message #114793] |
Tue, 05 April 2005 12:37 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I had left off an 's' on one of the variable references - it should be p_recs_per_page, not p_rec_per_page.
open p_rc for
select attr
from (select t.*, rownum rn
from (select attr
from invn_sbs
order by attr) t
where rownum <= (p_page * p_recs_per_page))
where rn >= ((p_page * p_recs_per_page) - p_recs_per_page + 1); |
|
|
|
|
|
|
|
| Re: Paging via Stored procedures [message #114808 is a reply to message #114803] |
Tue, 05 April 2005 14:12 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Whoa, calm down. I have a real job you know besides volunteering to answer questions here.
Again, there is serious overhead to calculating whether there are more pages to come, but, if I had to do it, it might look something like:
begin
select 'Y'
into p_more_flag
from invn_sbs
where rownum <= ((p_page * p_recs_per_page) + 1)
having count(*) = ((p_page * p_recs_per_page) + 1);
exception
when no_data_found then
p_more_flag := 'N';
end;
where p_more_flag is another OUT parameter of the procedure.
|
|
|
|
|
|
| Re: Paging via Stored procedures [message #114813 is a reply to message #114768] |
Tue, 05 April 2005 14:39 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- static procedre:
scott@ORA92> CREATE OR REPLACE PROCEDURE sp_PagedItems
2 -- input and output parameters:
3 (p_RefCursor OUT SYS_REFCURSOR,
4 p_Page IN INT,
5 p_RecsPerPage IN INT)
6 AS
7 -- declare local variables and assign values to them:
8 v_FirstRec INT := (p_Page - 1) * p_RecsPerPage;
9 v_LastRec INT := p_Page * p_RecsPerPage + 1;
10 BEGIN
11 -- open static ref cursor for page of ordered, numbered records
12 -- and how many more:
13 OPEN p_RefCursor FOR
14 SELECT ti.*,
15 (SELECT GREATEST ((COUNT (*) - v_LastRec) + 1, 0)
16 FROM tblItem) AS more_records
17 FROM (SELECT ROW_NUMBER () OVER (ORDER BY Price) AS id,
18 Name,
19 Price
20 FROM tblItem) ti
21 WHERE ti.id > v_FirstRec
22 AND ti.id < v_LastRec;
23 END sp_PagedItems;
24 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
-- execution of static procedure:
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> VARIABLE g_RefCursor REFCURSOR
scott@ORA92> EXECUTE sp_PagedItems (:g_RefCursor, 1, 5)
PL/SQL procedure successfully completed.
ID NAME PRICE MORE_RECORDS
---------- -------------------------------------------------- ---------- ------------
1 name1 1 7
2 name3 3 7
3 name5 5 7
4 name7 7 7
5 name9 9 7
scott@ORA92> EXECUTE sp_PagedItems (:g_RefCursor, 2, 5)
PL/SQL procedure successfully completed.
ID NAME PRICE MORE_RECORDS
---------- -------------------------------------------------- ---------- ------------
6 name11 11 2
7 name12 12 2
8 name10 14 2
9 name8 16 2
10 name6 18 2
scott@ORA92> EXECUTE sp_PagedItems (:g_RefCursor, 3, 5)
PL/SQL procedure successfully completed.
ID NAME PRICE MORE_RECORDS
---------- -------------------------------------------------- ---------- ------------
11 name4 20 0
12 name2 22 0
scott@ORA92>
-- dynamic procedre:
scott@ORA92> CREATE OR REPLACE PROCEDURE GetSortedPage
2 -- input and output parameters:
3 (p_RefCursor OUT SYS_REFCURSOR,
4 p_TableNames IN VARCHAR2,
5 P_SelectFields IN VARCHAR2,
6 p_SortFields IN VARCHAR2 := '1',
7 p_PageSize IN INT := 20,
8 p_PageIndex IN INT := 1,
9 p_QueryFilter IN VARCHAR2 := NULL)
10 AS
11 -- declare local variables and assign values to them:
12 v_query VARCHAR2(32767);
13 v_FirstRec INT := (p_PageIndex - 1) * p_PageSize;
14 v_LastRec INT := p_PageIndex * p_PageSize + 1;
15 BEGIN
16 -- assemble query string:
17 v_query := 'SELECT t.*,'
18 || CHR(10) || '(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)'
19 || CHR(10) || ' FROM ' || p_TableNames
20 || CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ') more_records'
21 || CHR(10) || ' FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' || p_SortFields || ') id,'
22 || CHR(10) || p_SelectFields
23 || CHR(10) || ' FROM ' || p_TableNames
24 || CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ') t'
25 || CHR(10) || ' WHERE t.id>:b_FirstRec'
26 || CHR(10) || ' AND t.id<:b_LastRec';
27 -- optional display of dynamic query:
28 FOR i IN 0 .. CEIL (LENGTH (v_query) / 250) LOOP
29 DBMS_OUTPUT.PUT_LINE (SUBSTR (v_query, (i * 250) + 1, 250));
30 END LOOP;
31 -- open dynamic ref cursor for page of ordered, numbered records
32 -- and how many more:
33 OPEN p_RefCursor FOR v_query USING v_LastRec, v_FirstRec, v_Lastrec;
34 END GetSortedPage;
35 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
-- execution of dynmaic procedure:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> VARIABLE g_Ref REFCURSOR
scott@ORA92> EXEC GetSortedPage (:g_Ref, 'Dept', 'Dept.*')
SELECT t.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM Dept
WHERE 1=1) more_records
FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) id,
Dept.*
FROM Dept
WHERE 1=1) t
WHERE t.id>:b_FirstRec
AND t.id<:b_LastRec
PL/SQL procedure successfully completed.
ID DEPTNO DNAME LOC MORE_RECORDS
---------- ---------- -------------- ------------- ------------
1 10 ACCOUNTING NEW YORK 0
2 20 RESEARCH DALLAS 0
3 30 SALES CHICAGO 0
4 40 OPERATIONS BOSTON 0
scott@ORA92> EXEC GetSortedPage (:g_Ref, 'Emp', 'Emp.*')
SELECT t.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM Emp
WHERE 1=1) more_records
FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) id,
Emp.*
FROM Emp
WHERE 1=1) t
WHERE t.id>:b_FirstRec
AND t.id<:b_LastRec
PL/SQL procedure successfully completed.
ID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MORE_RECORDS
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------
1 7369 SMITH CLERK 7902 17-DEC-80 800 20 0
2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 0
3 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 0
4 7566 JONES MANAGER 7839 02-APR-81 2975 20 0
5 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 0
6 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 0
7 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 0
8 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 0
9 7839 KING PRESIDENT 17-NOV-81 5000 10 0
10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 0
11 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 0
12 7900 JAMES CLERK 7698 03-DEC-81 950 30 0
13 7902 FORD ANALYST 7566 03-DEC-81 3000 20 0
14 7934 MILLER CLERK 7782 23-JAN-82 1300 10 0
14 rows selected.
scott@ORA92> EXEC GetSortedPage (:g_Ref,'Dept d,Emp e','Dname,Job,Sal,Ename','Dname,Job,Sal',10,1,'d.deptno=e.deptno and ename<>''BLAKE''')
SELECT t.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM Dept d,Emp e
WHERE d.deptno=e.deptno and ename<>'BLAKE') more_records
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Dname,Job,Sal) id,
Dname,Job,Sal,Ename
FROM Dept d,Emp e
WHERE d.deptno=e
.deptno and ename<>'BLAKE') t
WHERE t.id>:b_FirstRec
AND t.id<:b_LastRec
PL/SQL procedure successfully completed.
ID DNAME JOB SAL ENAME MORE_RECORDS
---------- -------------- --------- ---------- ---------- ------------
1 ACCOUNTING CLERK 1300 MILLER 3
2 ACCOUNTING MANAGER 2450 CLARK 3
3 ACCOUNTING PRESIDENT 5000 KING 3
4 RESEARCH ANALYST 3000 SCOTT 3
5 RESEARCH ANALYST 3000 FORD 3
6 RESEARCH CLERK 800 SMITH 3
7 RESEARCH CLERK 1100 ADAMS 3
8 RESEARCH MANAGER 2975 JONES 3
9 SALES CLERK 950 JAMES 3
10 SALES SALESMAN 1250 MARTIN 3
10 rows selected.
scott@ORA92> EXEC GetSortedPage (:g_Ref,'Dept d,Emp e','Dname,Job,Sal,Ename','Dname,Job,Sal',10,2,'d.deptno=e.deptno and ename<>''BLAKE''')
SELECT t.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM Dept d,Emp e
WHERE d.deptno=e.deptno and ename<>'BLAKE') more_records
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Dname,Job,Sal) id,
Dname,Job,Sal,Ename
FROM Dept d,Emp e
WHERE d.deptno=e
.deptno and ename<>'BLAKE') t
WHERE t.id>:b_FirstRec
AND t.id<:b_LastRec
PL/SQL procedure successfully completed.
ID DNAME JOB SAL ENAME MORE_RECORDS
---------- -------------- --------- ---------- ---------- ------------
11 SALES SALESMAN 1250 WARD 0
12 SALES SALESMAN 1500 TURNER 0
13 SALES SALESMAN 1600 ALLEN 0
scott@ORA92>
|
|
|
|
|
|
| Re: Paging via Stored procedures [message #114816 is a reply to message #114813] |
Tue, 05 April 2005 15:07 |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Barbara,
How can I call exec from a stored procedure?
PROCEDURE TESTGETSORTEDPAGE
(
p_RefCursor OUT SYS_REFCURSOR
)
AS
BEGIN
EXEC GetSortedPage(p_RefCursor, 'Dept', 'Dept.*');
END TESTGETSORTEDPAGE;
I got: Encountered the Symbol GetSortedPage when excepting one of the following: :=.(@%
|
|
|
|
| Re: Paging via Stored procedures [message #114822 is a reply to message #114814] |
Tue, 05 April 2005 15:20 |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Barbara,
I tried to use the dynamic sp like this:
PROCEDURE TESTGETSORTEDPAGE
(
p_RefCursor OUT SYS_REFCURSOR
)
AS
BEGIN
Global_GetSortedPage(p_RefCursor, 'INVN_SBS', 'Distinct(INVN_SBS.ATTR)');
END TESTGETSORTEDPAGE;
And got:
Mising Expression:
At Custom.Global_GetSortedPage, Line 32
At Custom.TestSortedPage, Line 8
If I dont use distinct it works, however I like to use distinct. Your help is very appreciated.
Thanks
|
|
|
|
|
|
|
|
| Re: Paging via Stored procedures [message #114828 is a reply to message #114822] |
Tue, 05 April 2005 15:44 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In Oracle, you cannot use the DISTINCT keyword in the middle of a column list in a select statement:
scott@ORA92> select row_number () over (order by 1) as id,
2 distinct (deptno)
3 from dept
4 /
distinct (deptno)
*
ERROR at line 2:
ORA-00936: missing expression
The DISTINCT keyword must be used before the first column selected:
scott@ORA92> select distinct (deptno),
2 row_number () over (order by 1) as id
3 from dept
4 /
DEPTNO ID
---------- ----------
10 1
20 2
30 3
40 4
scott@ORA92>
So, we need to rewrite the procedure so that the distinct will be first:
CREATE OR REPLACE PROCEDURE GetSortedPage
-- input and output parameters:
(p_RefCursor OUT SYS_REFCURSOR,
p_TableNames IN VARCHAR2,
P_SelectFields IN VARCHAR2,
p_SortFields IN VARCHAR2 := '1',
p_PageSize IN INT := 20,
p_PageIndex IN INT := 1,
p_QueryFilter IN VARCHAR2 := NULL)
AS
-- declare local variables and assign values to them:
v_query VARCHAR2(32767);
v_FirstRec INT := (p_PageIndex - 1) * p_PageSize;
v_LastRec INT := p_PageIndex * p_PageSize + 1;
BEGIN
-- assemble query string:
v_query := 'SELECT t.*,'
|| CHR(10) || '(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)'
|| CHR(10) || ' FROM ' || p_TableNames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ') more_records'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields || ','
|| CHR(10) || ' ROW_NUMBER() OVER (ORDER BY ' || p_SortFields || ') id'
|| CHR(10) || ' FROM ' || p_TableNames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ') t'
|| CHR(10) || ' WHERE t.id>:b_FirstRec'
|| CHR(10) || ' AND t.id<:b_LastRec';
-- optional display of dynamic query:
FOR i IN 0 .. CEIL (LENGTH (v_query) / 250) LOOP
DBMS_OUTPUT.PUT_LINE (SUBSTR (v_query, (i * 250) + 1, 250));
END LOOP;
-- open dynamic ref cursor for page of ordered, numbered records
-- and how many more:
OPEN p_RefCursor FOR v_query USING v_LastRec, v_FirstRec, v_Lastrec;
END GetSortedPage;
/
Then it will execute without problems:
scott@ORA92> CREATE OR REPLACE PROCEDURE TestGetSortedPage
2 (p_refcursor OUT SYS_REFCURSOR)
3 AS
4 BEGIN
5 GetSortedPage (p_RefCursor, 'Dept', 'DISTINCT(Deptno)');
6 END TestGetSortedPage;
7 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> EXECUTE TestGetSortedPage (:g_ref)
SELECT t.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM Dept
WHERE 1=1) more_records
FROM (SELECT DISTINCT(Deptno),
ROW_NUMBER() OVER (ORDER BY 1) id
FROM Dept
WHERE 1=1) t
WHERE t.id>:b_FirstRec
AND t.id<:b_LastRec
PL/SQL procedure successfully completed.
DEPTNO ID MORE_RECORDS
---------- ---------- ------------
10 1 0
20 2 0
30 3 0
40 4 0
scott@ORA92>
|
|
|
|
|
|
|
|
|
|
| Re: Paging via Stored procedures [message #114836 is a reply to message #114768] |
Tue, 05 April 2005 16:29 |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
It wasnt actually the order by. I had to implement the Group By. So I changed the sp to the following. I hope this is ok.
PROCEDURE Global_GetSortedPage
(
p_RefCursor OUT SYS_REFCURSOR,
p_TableNames IN VARCHAR2,
P_SelectFields IN VARCHAR2,
P_GroupByFields IN VARCHAR2 := NULL,
p_PageIndex IN INT := 1,
p_PageSize IN INT := 25,
p_SortFields IN VARCHAR2 := '1',
p_QueryFilter IN VARCHAR2 := NULL
)
AS
v_query VARCHAR2(32767);
v_FirstRec INT := (p_PageIndex - 1) * p_PageSize;
v_LastRec INT := p_PageIndex * p_PageSize + 1;
BEGIN
If Not P_GroupByFields Is Null then
-- assemble query string:
v_query := 'SELECT t.*,'
|| CHR(10) || '(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)'
|| CHR(10) || ' FROM ' || p_TableNames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ') MoreRecords'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields || ','
|| CHR(10) || ' ROW_NUMBER() OVER (ORDER BY ' || p_SortFields || ') id'
|| CHR(10) || ' FROM ' || p_TableNames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ' GROUP BY ' || P_GroupByFields || ') t'
|| CHR(10) || ' WHERE t.id>:b_FirstRec'
|| CHR(10) || ' AND t.id<:b_LastRec';
else
-- assemble query string:
v_query := 'SELECT t.*,'
|| CHR(10) || '(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)'
|| CHR(10) || ' FROM ' || p_TableNames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ') MoreRecords'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields || ','
|| CHR(10) || ' ROW_NUMBER() OVER (ORDER BY ' || p_SortFields || ') id'
|| CHR(10) || ' FROM ' || p_TableNames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ') t'
|| CHR(10) || ' WHERE t.id>:b_FirstRec'
|| CHR(10) || ' AND t.id<:b_LastRec';
end if;
-- open dynamic ref cursor for page of ordered, numbered records
-- and how many more:
OPEN p_RefCursor FOR v_query USING v_LastRec, v_FirstRec, v_Lastrec;
END Global_GetSortedPage;
It works.
|
|
|
|
| Re: Paging via Stored procedures [message #114838 is a reply to message #114836] |
Tue, 05 April 2005 16:54 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The reason that the distinct was not producing the correct results was that it was taking the id into account when considering what was distinct. So, one solution is to use the distinct in an inner subquery and the row_number to produce the id in an outer query. While you were working on your solution with group by, I was working on a solution to allow distinct. So, here is my revised version, that produces the correct results with distinct and with all of the previous scenarious. You can take your pick or combine them as you see fit.
CREATE OR REPLACE PROCEDURE GetSortedPage
-- input and output parameters:
(p_RefCursor OUT SYS_REFCURSOR,
p_TableNames IN VARCHAR2,
P_SelectFields IN VARCHAR2,
p_SortFields IN VARCHAR2 := '1',
p_PageSize IN INT := 20,
p_PageIndex IN INT := 1,
p_QueryFilter IN VARCHAR2 := NULL)
AS
-- declare local variables and assign values to them:
v_query VARCHAR2(32767);
v_FirstRec INT := (p_PageIndex - 1) * p_PageSize;
v_LastRec INT := p_PageIndex * p_PageSize + 1;
BEGIN
-- assemble query string:
v_query := 'SELECT t2.*,'
|| CHR(10) || '(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields
|| CHR(10) || ' FROM ' || p_Tablenames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ')) more_records'
|| CHR(10) || ' FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' || p_SortFields || ') id,t1.*'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields
|| CHR(10) || ' FROM ' || p_TableNames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1') || ') t1) t2'
|| CHR(10) || ' WHERE t2.id>:b_FirstRec AND t2.id<:b_LastRed';
-- optional display of dynamic query:
FOR i IN 0 .. CEIL (LENGTH (v_query) / 250) LOOP
DBMS_OUTPUT.PUT_LINE (SUBSTR (v_query, (i * 250) + 1, 250));
END LOOP;
-- open dynamic ref cursor for page of ordered, numbered records
-- and how many more:
OPEN p_RefCursor FOR v_query USING v_LastRec, v_FirstRec, v_Lastrec;
END GetSortedPage;
/
|
|
|
|
| Re: Paging via Stored procedures [message #114840 is a reply to message #114836] |
Tue, 05 April 2005 17:04 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
One thing that I do not like about your solution is that you have repeated a large block of code. If you want to add group by, then I would use the NVL2 function, something like:
|| NVL2 (p_groupbyfields, ' GROUP BY ' || p_groupbyfields, null)
and add it to the dynamic query. That way, if the parameter is null, then it does not add anything to the query, but if it is not null, then it uses it, adding " group by " in front of it. So, there is no need to repeat the block of code.
|
|
|
|
| Re: Paging via Stored procedures [message #114841 is a reply to message #114840] |
Tue, 05 April 2005 17:11 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Barbara, do you have any concerns about the fact that your approach does not use the COUNT STOPKEY optimization? It will have to run through the entire set of rows to find the requested block - even the rows after the block.
|
|
|
|
| Re: Paging via Stored procedures [message #114842 is a reply to message #114840] |
Tue, 05 April 2005 17:19 |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Thanks Barbara, I will use your approach. Another question: Do you see the possibility to show me also how I can add a field "TotalPages", which shows me the total pages? If so that would be great.
Thanks
|
|
|
|
| Re: Paging via Stored procedures [message #114845 is a reply to message #114842] |
Tue, 05 April 2005 17:38 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I spoke too soon about the nvl2, without testing. I forgot that you cannot use it directly in pl/sql, but must use select and the same for decode, so you might as well use if and end if, as I have done in the revised code below, which also adds another variable. I would not worry about not having the count stop key. The analytic row_number function and its window sort is also very fast. Here is another revision that allows for usage of either distinct or group by or both. I have also put the parameters in the order that you seem to want and used the name you are using. Regarding the total pages, where do you need this? Does it need to be another column in the result set, like the more_records, or can it be returned as a separate out parameter? Before we get too deep into this, I should suggest that you search Tom Kyte's site and read what he has to say about paging through result sets. What are these pages being returned to? If they are being returned to some other application, then usually that application should handle all of the paging and scrolling.
CREATE OR REPLACE PROCEDURE GlobalGetSortedPage
-- input and output parameters:
(p_RefCursor OUT SYS_REFCURSOR,
p_TableNames IN VARCHAR2,
P_SelectFields IN VARCHAR2,
P_GroupByFields IN VARCHAR2 := NULL,
p_PageIndex IN INT := 1,
p_PageSize IN INT := 25,
p_SortFields IN VARCHAR2 := '1',
p_QueryFilter IN VARCHAR2 := NULL)
AS
-- declare local variables and assign values to them:
v_query VARCHAR2(32767);
v_FirstRec INT := (p_PageIndex - 1) * p_PageSize;
v_LastRec INT := p_PageIndex * p_PageSize + 1;
v_groupby VARCHAR2(32767) := p_GroupbyFields;
BEGIN
-- assemble query string:
IF v_groupby IS NOT NULL THEN
v_groupby := ' GROUP BY ' || v_groupby;
END IF;
v_query := 'SELECT t2.*,'
|| CHR(10) || '(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields
|| CHR(10) || ' FROM ' || p_Tablenames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
|| CHR(10) || v_groupby
|| CHR(10) || ')) more_records'
|| CHR(10) || ' FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' || p_SortFields || ') id,t1.*'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields
|| CHR(10) || ' FROM ' || p_TableNames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
|| CHR(10) || v_groupby
|| CHR(10) || ') t1) t2'
|| CHR(10) || ' WHERE t2.id>:b_FirstRec AND t2.id<:b_LastRed';
-- optional display of dynamic query:
FOR i IN 0 .. CEIL (LENGTH (v_query) / 250) LOOP
DBMS_OUTPUT.PUT_LINE (SUBSTR (v_query, (i * 250) + 1, 250));
END LOOP;
-- open dynamic ref cursor for page of ordered, numbered records
-- and how many more:
OPEN p_RefCursor FOR v_query USING v_LastRec, v_FirstRec, v_Lastrec;
END GlobalGetSortedPage;
/
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> VARIABLE g_Ref REFCURSOR
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'Emp','DISTINCT Deptno,JOb','Deptno,Job')
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY Deptno,Job
)) more_records
FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) id,t1.*
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHE
RE 1=1
GROUP BY Deptno,Job
) t1) t2
WHERE t2.id>:b_FirstRec AND t2.id<:b_LastRed
PL/SQL procedure successfully completed.
ID DEPTNO JOB MORE_RECORDS
---------- ---------- --------- ------------
1 10 CLERK 0
2 10 MANAGER 0
3 10 PRESIDENT 0
4 20 ANALYST 0
5 20 CLERK 0
6 20 MANAGER 0
7 30 CLERK 0
8 30 MANAGER 0
9 30 SALESMAN 0
9 rows selected.
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'Emp','DISTINCT Deptno,JOb')
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
)) more_records
FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) id,t1.*
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
) t1) t2
WH
ERE t2.id>:b_FirstRec AND t2.id<:b_LastRed
PL/SQL procedure successfully completed.
ID DEPTNO JOB MORE_RECORDS
---------- ---------- --------- ------------
1 10 CLERK 0
2 10 MANAGER 0
3 10 PRESIDENT 0
4 20 ANALYST 0
5 20 CLERK 0
6 20 MANAGER 0
7 30 CLERK 0
8 30 MANAGER 0
9 30 SALESMAN 0
9 rows selected.
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'Emp','Deptno,JOb','Deptno,Job')
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY Deptno,Job
)) more_records
FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) id,t1.*
FROM (SELECT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY D
eptno,Job
) t1) t2
WHERE t2.id>:b_FirstRec AND t2.id<:b_LastRed
PL/SQL procedure successfully completed.
ID DEPTNO JOB MORE_RECORDS
---------- ---------- --------- ------------
1 10 CLERK 0
2 10 MANAGER 0
3 10 PRESIDENT 0
4 20 ANALYST 0
5 20 CLERK 0
6 20 MANAGER 0
7 30 CLERK 0
8 30 MANAGER 0
9 30 SALESMAN 0
9 rows selected.
|
|
|
|
| Re: Paging via Stored procedures [message #114847 is a reply to message #114845] |
Tue, 05 April 2005 17:50 |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Hello Barbara,
thanks again for the clarification.
"Does it need to be another column in the result set, like the more_records, or can it be returned as a separate out parameter?"
Yes, I need it as an extra field. The resultset is returned to an asp.net application. I am using some datalists there and the paging, scrolling etc. sucks there and I usually do paging on the server side - means I only return the desired rows. However In the application, I provide the user the ability to navigate through the pages, like Next/Prev/First/Last. If I had the TotalPages, then I think this sp will be complete.
Thanks Again (Vielen Dank)
Regards Vishal (Gruss Vishal)
|
|
|
|
| Re: Paging via Stored procedures [message #114848 is a reply to message #114847] |
Tue, 05 April 2005 18:17 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
This should do it:
CREATE OR REPLACE PROCEDURE GlobalGetSortedPage
-- input and output parameters:
(p_RefCursor OUT SYS_REFCURSOR,
p_TableNames IN VARCHAR2,
P_SelectFields IN VARCHAR2,
P_GroupByFields IN VARCHAR2 := NULL,
p_PageIndex IN INT := 1,
p_PageSize IN INT := 25,
p_SortFields IN VARCHAR2 := '1',
p_QueryFilter IN VARCHAR2 := NULL)
AS
-- declare local variables and assign values to them:
v_query VARCHAR2(32767);
v_FirstRec INT := (p_PageIndex - 1) * p_PageSize;
v_LastRec INT := p_PageIndex * p_PageSize + 1;
v_groupby VARCHAR2(32767) := p_GroupbyFields;
BEGIN
-- assemble query string:
IF v_groupby IS NOT NULL THEN
v_groupby := ' GROUP BY ' || v_groupby;
END IF;
v_query := 'SELECT t2.*,'
|| CHR(10) || '(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields
|| CHR(10) || ' FROM ' || p_Tablenames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
|| CHR(10) || v_groupby
|| CHR(10) || ')) more_records,'
|| CHR(10) || '(SELECT CEIL(COUNT(*)/:b_PageSize)'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields
|| CHR(10) || ' FROM ' || p_Tablenames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
|| CHR(10) || v_groupby
|| CHR(10) || ')) total_pages'
|| CHR(10) || ' FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' || p_SortFields || ') id,t1.*'
|| CHR(10) || ' FROM (SELECT ' || p_SelectFields
|| CHR(10) || ' FROM ' || p_TableNames
|| CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
|| CHR(10) || v_groupby
|| CHR(10) || ') t1) t2'
|| CHR(10) || ' WHERE t2.id>:b_FirstRec AND t2.id<:b_LastRec';
-- optional display of dynamic query:
FOR i IN 0 .. CEIL (LENGTH (v_query) / 250) LOOP
DBMS_OUTPUT.PUT_LINE (SUBSTR (v_query, (i * 250) + 1, 250));
END LOOP;
-- open dynamic ref cursor for page of ordered, numbered records
-- and how many more:
OPEN p_RefCursor FOR v_query USING v_LastRec, p_PageSize, v_FirstRec, v_Lastrec;
END GlobalGetSortedPage;
/
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> VARIABLE g_Ref REFCURSOR
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'Emp','DISTINCT Deptno,JOb','Deptno,Job', 1, 4)
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY Deptno,Job
)) more_records,
(SELECT CEIL(COUNT(*)/:b_PageSize)
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY
Deptno,Job
)) total_pages
FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) id,t1.*
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY Deptno,Job
) t1) t2
WHERE t2.id>:b_FirstRec AND t2.id<:b_LastRec
PL/SQL procedure successfully completed.
ID DEPTNO JOB MORE_RECORDS TOTAL_PAGES
---------- ---------- --------- ------------ -----------
1 10 CLERK 5 3
2 10 MANAGER 5 3
3 10 PRESIDENT 5 3
4 20 ANALYST 5 3
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'Emp','DISTINCT Deptno,JOb','Deptno,Job', 2, 4)
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY Deptno,Job
)) more_records,
(SELECT CEIL(COUNT(*)/:b_PageSize)
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY
Deptno,Job
)) total_pages
FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) id,t1.*
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY Deptno,Job
) t1) t2
WHERE t2.id>:b_FirstRec AND t2.id<:b_LastRec
PL/SQL procedure successfully completed.
ID DEPTNO JOB MORE_RECORDS TOTAL_PAGES
---------- ---------- --------- ------------ -----------
5 20 CLERK 1 3
6 20 MANAGER 1 3
7 30 CLERK 1 3
8 30 MANAGER 1 3
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'Emp','DISTINCT Deptno,JOb','Deptno,Job', 3, 4)
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY Deptno,Job
)) more_records,
(SELECT CEIL(COUNT(*)/:b_PageSize)
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY
Deptno,Job
)) total_pages
FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) id,t1.*
FROM (SELECT DISTINCT Deptno,JOb
FROM Emp
WHERE 1=1
GROUP BY Deptno,Job
) t1) t2
WHERE t2.id>:b_FirstRec AND t2.id<:b_LastRec
PL/SQL procedure successfully completed.
ID DEPTNO JOB MORE_RECORDS TOTAL_PAGES
---------- ---------- --------- ------------ -----------
9 30 SALESMAN 0 3
scott@ORA92>
|
|
|
|
| Re: Paging via Stored procedures [message #114849 is a reply to message #114848] |
Tue, 05 April 2005 18:37 |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Thanks - That did it. One last question:
I have the following queryfilter:
v_QueryFilter := 'To_Date(Invoice.CREATED_DATE,''DD/MM/YY'') = to_Date(' || EndDate || ', ''DD/MM/YY'')';
When I run the sp, I keep getting:
A non-numeric character was found where a numeric was excepted.
If I change the filter to:
v_QueryFilter := 'To_Date(Invoice.CREATED_DATE,''DD/MM/YY'') = to_Date(''15/02/2005'', ''DD/MM/YY'')';
Then it works. What is wrong with my first query?
Thanks again
[Updated on: Tue, 05 April 2005 18:39] Report message to a moderator |
|
|
|
|
|
| Re: Paging via Stored procedures [message #114859 is a reply to message #114849] |
Tue, 05 April 2005 20:38 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
What is enddate? Is it a column in the invoice table? Or is it a variable that you have declared somewhere? If it is a variable, is it declared in your procedure or as a host variable? What datatype is it? I am attempting to set up a test, so that I can reproduce your error. In general, if created_date and enddate are both date datatypes, as they should be, then do not use to_date. If they are both varchar2, then do not use to_date. If only one of them is varchar2, then use to_date on that one. You should be using four-digit years, not two-digit years. If enddate is a column in the table, then just use it in the same manner that you used created_date, without the concatenation symbols. If enddate is a local variable within your procedure, then use a bind variable in the v_query string and add the enddate to your using clause in the appropriate place. That is about all the general information to cover all of the possibilities that I can offer, until I get some specific information.
|
|
|
|
|
|
| Re: Paging via Stored procedures [message #114865 is a reply to message #114864] |
Tue, 05 April 2005 23:58 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> DEFINE enddate = '15/02/05'
scott@ORA92> EXEC GlobalGetSortedPage -
> (:g_ref,'test_tab','created_date',null,1,20,1, -
> 'to_date(created_date,''dd/mm/yy'')=to_date(''&enddate'',''dd/mm/yy'')')
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT created_date
FROM test_tab
WHERE to_date(created_date,'dd/mm/yy')=to_date('15/02/05','dd/mm/yy')
)) more_records,
(SELECT CEIL(COUNT(*)/:b_PageSize)
FROM (SELECT created_date
FROM test_tab
WHERE to_date(created_date,'dd/mm/yy')=to_date('15/02/05','dd/mm/yy')
)) total_pages
FROM (SELECT ROW_NUMBER() OVER (ORDER BY 1) id,t1.*
FROM (SELECT created_date
FROM test_tab
WHERE to_date(created_date,'dd/mm/yy')=to_date('15/0
2/05','dd/mm/yy')
) t1) t2
WHERE t2.id>:b_FirstRec AND t2.id<:b_LastRec
PL/SQL procedure successfully completed.
ID CREATED_ MORE_RECORDS TOTAL_PAGES
---------- -------- ------------ -----------
1 15/02/05 0 1
scott@ORA92>
[Updated on: Wed, 06 April 2005 00:02] Report message to a moderator |
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 14:23:54 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
