Oracle procedure with pageNumber and pagesize are input parameters,should output a recordset is showing error

Question

I am new in oracle. I need to create a procedure to return some details to application. Pagenumber and pagesize are passed as input parameters. Need to get the result in P_RECORDSET . I dont know what i am written is correct. Now an error is coming near WITH statement that 'SQL Statement Ignored'

CREATE OR REPLACE PROCEDURE INTEGRATION_PRO
(PAGENUMBER IN INT := 1,  
 ROWCOUNT in INT := 10,
 P_RECORDSET OUT TYPES.CURSORTYPE)
As  
BEGIN  

WITH TempResult AS(
SELECT *    
FROM PERMIT  
),
TempCount AS (
SELECT COUNT(*) AS MaxRows FROM PERMIT)

SELECT *
FROM TempResult, TempCount
ORDER BY TEMPRESULT.APPLICATION_REF_ID
 OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY

 open P_RECORDSET for  SELECT *
FROM TempResult, TempCount
ORDER BY TEMPRESULT.APPLICATION_REF_ID
 OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY

END INTEGRATION_PRO;
GO

Using `@` as part of an identifier is invalid in PL/SQL (or standard SQL). Additionally: the CTE `tempresult` is completely useless. And procedures need to be [ended](https://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql/10207695#10207695) with `/` in Oracle (or at least in most Oracle tools) — , Aug 27 '19 at 06:45

score 1 · Answer 1 · answered Aug 27 '19 at 06:54

This is oracle database and you have used many other languages code into it.

I have corrected your code as following (see inline comments for description):

CREATE OR REPLACE PROCEDURE INTEGRATION_PRO (
    PAGENUMBER    IN            INT DEFAULT 1, -- DEFAULT is used to assign default value
    ROWCOUNT      IN            INT DEFAULT 10, -- DEFAULT is used to assign default value
    P_RECORDSET   OUT           SYS_REFCURSOR -- sys_refcursor is used as data type
) AS  
BEGIN
    open P_RECORDSET FOR -- directly OPEN cursor for query
    WITH TEMPRESULT AS (
        SELECT
            *
        FROM
            PERMIT  
    ), TEMPCOUNT AS (
        SELECT
            COUNT(*) AS MAXROWS
        FROM
            PERMIT
    )
    SELECT
        *
    FROM
        TEMPRESULT,
        TEMPCOUNT
    ORDER BY
        1 -- TEMPRESULT.APPLICATION_REF_ID can not be identified here
OFFSET (ROWCOUNT-1)*PAGENUMBER ROWS -- @ is removed and actual input parameter names are used
FETCH NEXT ROWCOUNT ROWS ONLY;

--open P_RECORDSET for  SELECT *
--FROM TempResult, TempCount
--ORDER BY TEMPRESULT.APPLICATION_REF_ID
-- OFFSET (@PageNum-1) *
--@PAGESIZE ROWS FETCH NEXT
--
--@PAGESIZE ROWS ONLY 

END INTEGRATION_PRO;

Tejash, I think `OFFSET (ROWCOUNT-1)*PAGENUMBER ROWS` must be `OFFSET (PAGENUMBER-1)*ROWCOUNT ROWS` — Ankit Bajpai, Aug 27 '19 at 06:58
That is up to the OP. I have just corrected the syntax errors — Popeye, Aug 27 '19 at 07:04
If you just go with the OPs intent, He needs to select first 10 rows for first time and then fetch next 10 rows. So it must be `OFFSET (PAGENUMBER-1)*ROWCOUNT ROWS` i.e. `OFFSET (1-1)*10 = 0` rows and then `OFFSET (2-1)*10 = 10` rows and so on. — Ankit Bajpai, Aug 27 '19 at 07:06

Ankit Bajpai · Accepted Answer · 2019-08-27T12:35:53.947

0

There were too many issues with your code. You can simply use below code -

CREATE OR REPLACE PROCEDURE INTEGRATION_PRO (PAGENUMBER IN INT DEFAULT 1,
                                             ROWCOUNT IN INT DEFAULT 10,
                                             P_RECORDSET OUT TYPES.CURSORTYPE)
As  
BEGIN  
     OPEN P_RECORDSET FOR
     WITH TempCount AS (
                        SELECT COUNT(*) AS MaxRows FROM PERMIT)
     SELECT *
     FROM PERMIT, TempCount
     WHERE id = 10
     ORDER BY PERMIT.APPLICATION_REF_ID
     OFFSET (PAGENUMBER - 1) * ROWCOUNT ROWS
     FETCH NEXT ROWCOUNT ROWS ONLY;

END INTEGRATION_PRO;

edited Aug 27 '19 at 12:35

answered Aug 27 '19 at 06:54

Ankit Bajpai

13,128
4
25
40

In the second select query, where do i give where clauses?'SELECT * FROM PERMIT where id=10 ,TempCount ' is showing error – Ajoe Aug 27 '19 at 11:45
Okay. One more doubt, When I run this procedure, its not displaying the resultset. What query is required to display that? – Ajoe Aug 28 '19 at 03:46
For returning the result, You need to be sure that query is returning some data. Run your query separately and see if it returning something or not? If your query is giving some data, The procedure must return data. – Ankit Bajpai Aug 28 '19 at 06:26

Oracle procedure with pageNumber and pagesize are input parameters,should output a recordset is showing error

2 Answers2