Home » SQL & PL/SQL » SQL & PL/SQL » Paging to show limited number of records (Oracle 10g)
Paging to show limited number of records [message #355402] Thu, 23 October 2008 23:19 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I have to use the concept of paging to show only limited number of records at one time ...may be only 20.

I have to create a temporary table and a sequence which would insert a identity column in the temp table which should always start with 1 for each session.
The table and sequence needs to be abandoned after each session expires.
If concurrent users execute the procedure, the name of the table created dynamally should be different or something should be in place to have the temp table individually for each session so that they can insert their data without any intervention from other session. Only one session data should be in the temporary table.

I have tried to code the prcedure which is as below :-
 CREATE OR REPLACE PROCEDURE xSp_BasketItemsForSelect(
     LineID  IN  number,
     BasketID IN number,
     RequestedPage IN number,
     PageSize OUT number,
     PageCount OUT NUMBER,
     RowCount OUT number
     )
 IS
 BasketItem_rec  BasketItems%ROWTYPE;
 TYPE BasketItemCurTyp IS REF CURSOR;
 basketItemcur BasketItemCurTyp; 
 From1 NUMBER;
 To1 NUMBER;
 ReqPageVal NUMBER := RequestedPage;
 strsql varchar2(200);
 BEGIN
 PageSize := '20';
 PageCount := '';
 RowCount :='';
     EXECUTE IMMEDIATE
     'CREATE TABLE BasketItemss (
     ID number PRIMARY KEY,
     LineID number,
     BasketID number,
     ProductItemNumber varchar2(25),
     Quantity number,
     Price number(8,2),
     SubTotal number(10,2),
     AllocatTo1 varchar2(50),
     AllocatTo2 varchar2(50),
     TemplateID varchar2(16),
     AlternativePartNO varchar2(255),
     CategoryType char(1)
     )';
     BEGIN
     EXECUTE IMMEDIATE 'CREATE SEQUENCE BasketItemss_id_seq';
     END;
    
if ReqPageVal is not null then
    DECLARE
    BasketItemss_rec  BasketItemss%ROWTYPE;
    TYPE BasketCurTyp IS REF CURSOR;
    basketcur BasketCurTyp; 
    Begin
       Insert into BasketItemss(ID,LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,
              AllocatTo1,AllocatTo2,TemplateID,AlternativePartNO,CategoryType)
              (select BasketItemss_id_seq.nextval, LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,
              AllocatTo1,AllocatTo2,TemplateID,AlternativePartNO,CategoryType from BasketItems
        Where LineID = nvl(LineID,LineID) AND BasketID = nvl(BasketID,BasketID));
       strsql := 'Select count(*) from BasketItems';
       EXECUTE Immediate strsql  INTO RowCount;
       if RowCount <= PageSize THEN
           PageCount := 1;
       else
         strsql := 'SELECT CIEL(RowCount/PageSize) FROM dual';
         EXECUTE IMMEDIATE strsql INTO pagecount;
            IF PageCount <> (RowCount/PageSize) THEN
               PageCount := PageCount + 1;
            ELSE
              PageCount := PageCount + 0;
           END IF;
      End IF;
      if ReqPageVal > PageCount then
         ReqPageVal := PageCount;
      End if;
      if ReqPageVal < 1 then
         ReqPageVal := 1;
      End if;
      if ReqPageVal > 1 then
         From1 := ((ReqPageVal -1) * PageSize) + 1;
         To1 := (ReqPageVal * PageSize) ;
      else
         From1 := 1;
         To1 := PageSize;
     End if;
      strsql := 'select * from BasketItemss where id >= ' ||from1|| ' And id <= ' ||To1;
      OPEN basketcur FOR strsql;
         LOOP
           FETCH basketcur INTO BasketItemss_rec;
             EXIT WHEN basketcur%NOTFOUND;      
             -- process record
         END LOOP;
      CLOSE basketcur;
    end;
else
    PageCount := -1;
    RowCount := -1;
    strsql := 'Select LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,AllocatTo1,AllocatTo2,TemplateID,
                      AlternativePartNO,CategoryType From BasketItems
                Where LineID = NVL(LineID,LineID) AND BasketID = NVL(BasketID,BasketID)';
    OPEN basketItemcur FOR strsql;
         LOOP
           FETCH basketItemcur INTO BasketItem_rec;
             EXIT WHEN basketItemcur%NOTFOUND;     
             -- process record
         END LOOP;
      CLOSE basketItemcur;
End if;

EXECUTE IMMEDIATE 'DROP TABLE BasketItemss';
EXECUTE IMMEDIATE 'DROP SEQUENCE BasketItemss_id_seq';
 END;
/


Please advice on this.

Regards,
Soni
Re: Paging to show limited number of records [message #355410 is a reply to message #355402] Thu, 23 October 2008 23:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't create tables at runtime in Oracle.
Why can't you use the session-id to distinguish between the different users?
Having the table created once (at design/develop time) will save you the trouble of these horrible dynamic sql statements.
- If you have a single insert-statement and you need to number the records inserted, use ROWNUM.
- If you want to know the number of records inserted, use sql%rowcount. No need to do a select count(*)
Re: Paging to show limited number of records [message #355427 is a reply to message #355410] Fri, 24 October 2008 00:55 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Frank,
Thanks for your input. I am planning to do it without creating tables & sequence.
I will simply use the popular select statement:
select * 
  from ( select a.*, rownum rnum
           from ( select rownum, select rownum, LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,       AllocatTo1,AllocatTo2,TemplateID,AlternativePartNO,CategoryType
  from BasketItems
 Where LineID = nvl(LineID,LineID) AND BasketID = nvl(BasketID,BasketID)) a
          where rownum <= MAX_ROWS )
where rnum >= MIN_ROWS


I have to know the number of recordcount for the above sql which I am not inserting in any table.
For this I am executing it by Execute Immediate.
  declare
  rowcount number;
  strsql varchar2(300);
  begin
  strsql := 'select rownum, LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,
AllocatTo1,AllocatTo2,TemplateID,AlternativePartNO,CategoryType
               from BasketItems
              Where LineID = nvl(LineID,LineID) AND BasketID = nvl(BasketID,BasketID)';
  EXECUTE Immediate strsql;
  rowcount := sql%rowcount;
  dbms_output.put_line('rowcount'||rowcount);
  end;



Though there are two records but I am getting only 0 recordcount.

Do I ned to use any cursor since its a multi record.


Re: Paging to show limited number of records [message #355434 is a reply to message #355427] Fri, 24 October 2008 01:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sonikumari wrote on Fri, 24 October 2008 07:55
Hi Frank,
Thanks for your input. I am planning to do it without creating tables & sequence.



Then why did you spend 99% of your original post on describing how you would create a table and sequence at runtime?
Re: Paging to show limited number of records [message #355436 is a reply to message #355434] Fri, 24 October 2008 01:28 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Sorry for the confusion but earlier I was trying to convert a procedure created in sqlserver. But as per your advice for not creating table, I found that this can be easily created using the select query with rownum.

Please advice me on getting the rowcount.

  1   declare
  2   rowcount number;
  3   strsql varchar2(300);
  4   rec BasketItems%rowtype;
  5   begin
  6   execute immediate 'select rownum, LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,
  7                     AllocatTo1,AllocatTo2,TemplateID,AlternativePartNO,CategoryType
  8                from BasketItems
  9               Where LineID = nvl(LineID,LineID) AND BasketID = nvl(BasketID,BasketID)';
 10  -- rowcount := sql%rowcount;
 11   dbms_output.put_line('rowcount'||sql%rowcount);
 12*   end;
SQL> /
rowcount0

PL/SQL procedure successfully completed.


I am getting only 0 as recordcount which should have been 2.

[Updated on: Fri, 24 October 2008 01:33]

Report message to a moderator

Re: Paging to show limited number of records [message #355441 is a reply to message #355436] Fri, 24 October 2008 01:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you insist on using dynamic sql?
Re: Paging to show limited number of records [message #355443 is a reply to message #355427] Fri, 24 October 2008 01:53 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
sonikumari wrote on Fri, 24 October 2008 07:55
I have to know the number of recordcount for the above sql which I am not inserting in any table.
For this I am executing it by Execute Immediate.

Why? What is dynamic in this SELECT statement?
For knowing the recordcount, you shall use the COUNT function.

Where LineID = nvl(LineID,LineID) AND BasketID = nvl(BasketID,BasketID)

Why simply not
Where LineID IS NOT NULL AND BasketID IS NOT NULL

, as the result is the same?

Quote:
Though there are two records but I am getting only 0 recordcount.

As you do not fetch the result set anywhere, I strongly suspect that SQL%ROWCOUNT is not set.

Quote:
Do I ned to use any cursor since its a multi record.

If you want to know only the record count, use COUNT; there is no need to fetch all the records.
Re: Paging to show limited number of records [message #355445 is a reply to message #355441] Fri, 24 October 2008 02:00 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

It is a web based application where there can be many concurrent users.
The number of pages need to be determined which can be found by dividing the number of records to page size.

So I am creating a procedure where the select statements needs to be executed.

I need to return the recordset to the user back.

Here is the procedure I am writting for it. This is still incomplete as I am working on it.

Later I will implement this code in the procedure below.

select * 
  from ( select a.*, rownum rnum
           from ( QUERY) a
          where rownum <= MAX_ROWS )
where rnum >= MIN_ROWS

Please suggest f thee is any better method than this.

 CREATE OR REPLACE PROCEDURE xSp_BasketItemsForSelect(
     LineID  IN  number,
     BasketID IN number,
     RequestedPage IN number,
     PageSize IN OUT number,
     PageCount OUT NUMBER,
     RowCount OUT number
     )
 IS
 BasketItem_rec  BasketItems%ROWTYPE;
 TYPE BasketItemCurTyp IS REF CURSOR;
 basketItemcur BasketItemCurTyp; 
 From1 NUMBER;
 To1 NUMBER;
 ReqPageVal NUMBER := RequestedPage;
 strsql varchar2(200);
 BEGIN
 IF pagesize IS NULL THEN
 PageSize := '20';
 END IF;
 PageCount := '';
 RowCount :='';
if ReqPageVal is not null then
    DECLARE
    BasketItemss_rec  BasketItemss%ROWTYPE;
    TYPE BasketCurTyp IS REF CURSOR;
    basketcur BasketCurTyp; 
    Begin
        strsql := 'select rownum, LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,
              AllocatTo1,AllocatTo2,TemplateID,AlternativePartNO,CategoryType from BasketItems
        Where LineID = nvl(LineID,LineID) AND BasketID = nvl(BasketID,BasketID)';        
       EXECUTE Immediate strsql  INTO RowCount;
if RowCount <= PageSize THEN
           PageCount := 1;
       else
         strsql := 'SELECT CIEL(RowCount/PageSize) FROM dual';
         EXECUTE IMMEDIATE strsql INTO pagecount;
            IF PageCount <> (RowCount/PageSize) THEN
               PageCount := PageCount + 1;
            ELSE
              PageCount := PageCount + 0;
           END IF;
      End IF;
      if ReqPageVal > PageCount then
         ReqPageVal := PageCount;
      End if;
      if ReqPageVal < 1 then
         ReqPageVal := 1;
      End if;
      if ReqPageVal > 1 then
         From1 := ((ReqPageVal -1) * PageSize) + 1;
         To1 := (ReqPageVal * PageSize) ;
      else
         From1 := 1;
         To1 := PageSize;
     End if;
      strsql := 'select * from BasketItemss where id >= ' ||from1|| ' And id <= ' ||To1;
      OPEN basketcur FOR strsql;
        LOOP
          FETCH basketcur INTO BasketItemss_rec;
            EXIT WHEN basketcur%NOTFOUND;      
             -- process record
        END LOOP;
      CLOSE basketcur;
    end;
else
    PageCount := -1;
    RowCount := -1;
    strsql := 'Select LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,AllocatTo1,AllocatTo2,TemplateID,
                      AlternativePartNO,CategoryType From BasketItems
                Where LineID = NVL(LineID,LineID) AND BasketID = NVL(BasketID,BasketID)';
    OPEN basketItemcur FOR strsql;
         LOOP
           FETCH basketItemcur INTO BasketItem_rec;
             EXIT WHEN basketItemcur%NOTFOUND;     
             -- process record
         END LOOP;
      CLOSE basketItemcur;
End if;
END;
/


Re: Paging to show limited number of records [message #355450 is a reply to message #355445] Fri, 24 October 2008 02:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First, get your datatypes corrected. Pagecount is a number, so it should NOT be initialized to ''.
Further, take some time to dive into the docs to see what REF CURSORS are for, as opposed to regular cursors.

Finally, try to get it working using straight and static sql. From your code, I get the idea that you don't know a lot about Oracle and PL/SQL. First try to get that piece, before getting into dynamic sql.
Re: Paging to show limited number of records [message #355452 is a reply to message #355450] Fri, 24 October 2008 02:27 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Frank,
I found a way to do it statically. Thanks for giving the hint.

I am able to do it using only :
 select count(1) INTO rowcount from BasketItems
        Where LineID = nvl(LineID,LineID) AND BasketID = nvl(BasketID,BasketID);  


Earlier I was planning to create a table and insert records into it and then fetch from the table.
Now since I am only executing the select statement, for a single row it was fine but now I have to execute the below select statement which would be a part of the procedure:
It might go something like this:-
declare
BasketItems_rec  BasketItems%ROWTYPE;
TYPE BasketCurTyp IS REF CURSOR;
basketcur BasketCurTyp; 
strsql varchar2(200);
from1 number := 1;
To1 number := 10;
begin
strsql := 'Select rownum, LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,        AllocatTo1,AllocatTo2,TemplateID, AlternativePartNO,CategoryType
             From BasketItems
            Where rownum >= ' ||from1|| ' 
              And rownum <= ' ||To1;

OPEN basketcur FOR strsql;
        LOOP
          FETCH basketcur INTO BasketItemss_rec;
            EXIT WHEN basketcur%NOTFOUND;      
            END LOOP;
      CLOSE basketcur;
END;


Please help me on this as I would need to return a recordset to the user when he executes the procedure.

[Updated on: Fri, 24 October 2008 03:31]

Report message to a moderator

Re: Paging to show limited number of records [message #355454 is a reply to message #355452] Fri, 24 October 2008 02:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Reread my post. I did not claim that you can use static sql for your final purpose; I said you should take back a step and first get it working using static sql.
The procedure you provided has no dynamic elements, so you can start rewriting it to static code.
One thing you will then see is that you cannot fetch your query into a single number field.

As for your remark about REF CURSORs: I have seen people doing the stupidest things. That does not mean that I want to do it too..
REF CURSORs are used to return a resultset. In your procedure you fetch from the REF CURSOR. This makes no sense.
Re: Paging to show limited number of records [message #355461 is a reply to message #355454] Fri, 24 October 2008 03:34 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Frank,
I have tried using statis select for rowcount.
select count(1) INTO rowcount from BasketItems
        Where LineID = nvl(LineID,LineIDIn) AND BasketID = nvl(BasketID,BasketIDIn);  


But finally I have to retrun a resultset as you can see in the above post before this.

Please suggest.

Regards

[Updated on: Fri, 24 October 2008 03:36]

Report message to a moderator

Re: Paging to show limited number of records [message #355462 is a reply to message #355461] Fri, 24 October 2008 03:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But what exactly is your problem now? You provided many different versions of your code. I'm kinda lost what the problem is.
Re: Paging to show limited number of records [message #355464 is a reply to message #355462] Fri, 24 October 2008 04:07 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

My procedure should return a recordset which would give the user a set of records for the page number given as parameter.

Please find the final version of the code as under where I need to work out the two sql queries which would return set of records and would be needed to return from this procedure.
I marked it with (--*********** This recordset needs to be returned ).

Please suggest the simplest way to do it.
Currently I am trying to use sys_refcursor to return the resultset. Correct me if I am wrong.

 CREATE OR REPLACE PROCEDURE xSp_BasketItemsForSelect(
     emp_refcur in out SYS_REFCURSOR,
     LineIDIn  IN  number,
     BasketIDIn IN number,
     RequestedPage IN number,
     PageSize IN OUT number,
     PageCount OUT NUMBER,
     RowCount OUT number
     )
 IS
 
 From1 NUMBER;
 To1 NUMBER;
 ReqPageVal NUMBER := RequestedPage;
 strsql varchar2(200);
 BEGIN
 IF pagesize IS NULL THEN
 PageSize := '20';
 END IF;
 
if ReqPageVal is not null then
    DECLARE
    
    Begin
       select count(1) INTO rowcount from BasketItems
        Where LineID = nvl(LineID,LineIDIn) AND BasketID = nvl(BasketID,BasketIDIn);        

    if RowCount <= PageSize THEN
           PageCount := 1;
       else
         strsql := 'SELECT CIEL(RowCount/PageSize) FROM dual';
         EXECUTE IMMEDIATE strsql INTO pagecount;
            IF PageCount <> (RowCount/PageSize) THEN
               PageCount := PageCount + 1;
            ELSE
              PageCount := PageCount + 0;
           END IF;
      End IF;
      if ReqPageVal > PageCount then
         ReqPageVal := PageCount;
      End if;
      if ReqPageVal < 1 then
         ReqPageVal := 1;
      End if;
      if ReqPageVal > 1 then
         From1 := ((ReqPageVal -1) * PageSize) + 1;
         To1 := (ReqPageVal * PageSize) ;
      else
         From1 := 1;
         To1 := PageSize;
     End if;
  OPEN basket_refcur FOR SELECT LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,
 AllocatTo1,AllocatTo2,TemplateID, AlternativePartNO,CategoryType From BasketItems
     Where LineID = NVL(LineID,LineIDIn) AND BasketID = NVL(BasketID,BasketIDIn)
       AND rownum >= 2 AND rownum <= 1;
--*********** This recordset needs to be returned       
--   strsql := 'select * from BasketItemss where id >= ' ||from1|| ' And id <= ' ||To1;
    end;
else
    PageCount := -1;
    RowCount := -1;
    
End if;
END;



I am sorry for making this so confusing.... because I myself was confused Sad but slowly I am getting on track.
Regards

[Updated on: Fri, 24 October 2008 05:05]

Report message to a moderator

Re: Paging to show limited number of records [message #355472 is a reply to message #355464] Fri, 24 October 2008 05:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sonikumari wrote on Fri, 24 October 2008 11:07

         strsql := 'SELECT CIEL(RowCount/PageSize) FROM dual';
         EXECUTE IMMEDIATE strsql INTO pagecount;


I am sorry for making this so confusing.... because I myself was confused Sad but slowly I am getting on track.
Regards


This can be rewritten without dynamic sql; even better this is a simple assignment. No sql needed here.

Executing your procedure must end in numerous errors.. Why not fix them before going further?

But back to your question. You state you need to return the resultset of TWO queries?
The first one you tackled, as you return the ref cursor and opened it in this proc.
Not sure if you want the results of the second one merged or whatever, but the way to go is the same as for how you opened the first ref cursor.
Re: Paging to show limited number of records [message #355480 is a reply to message #355472] Fri, 24 October 2008 06:31 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Thanks Frank,
I need to return only one recordset of the TWO. Its in else condition.

I executed the procedure and its created.
  CREATE OR REPLACE PROCEDURE xSp_BasketItemsForSelect(
       emp_refcur in out SYS_REFCURSOR,
       LineIDIn  IN  number,
       BasketIDIn IN number,
       RequestedPage IN number,
       PageSize IN OUT number,
       PageCount OUT NUMBER,
       RowCount OUT number
       )
   IS
   From1 NUMBER;
   To1 NUMBER;
   ReqPageVal NUMBER := RequestedPage;
   strsql varchar2(200);
   BEGIN
   IF pagesize IS NULL THEN
   PageSize := '20';
   END IF;
  if ReqPageVal is not null then
      DECLARE
      Begin
         select count(1) INTO rowcount from BasketItems
          Where LineID = nvl(LineID,LineIDIn) AND BasketID = nvl(BasketID,BasketIDIn);
      if RowCount <= PageSize THEN
             PageCount := 1;
         else
           SELECT CEIL(RowCount/PageSize) INTO pagecount from dual;
              IF PageCount <> (RowCount/PageSize) THEN
                 PageCount := PageCount + 1;
              ELSE
                PageCount := PageCount + 0;
             END IF;
        End IF;
        if ReqPageVal > PageCount then
           ReqPageVal := PageCount;
        End if;
        if ReqPageVal < 1 then
           ReqPageVal := 1;
        End if;
        if ReqPageVal > 1 then
           From1 := ((ReqPageVal -1) * PageSize) + 1;
           To1 := (ReqPageVal * PageSize) ;
        else
           From1 := 1;
           To1 := PageSize;
       End if;
   OPEN emp_refcur FOR SELECT LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,
 AllocatTo1,AllocatTo2,TemplateID, AlternativePartNO,CategoryType From BasketItems
     Where LineID = NVL(LineID,LineIDIn) AND BasketID = NVL(BasketID,BasketIDIn)
       AND rownum >= from1 AND rownum <= To1;
 --*********** This recordset needs to be returned
     end;
 else
     PageCount := -1;
     RowCount := -1;
 OPEN emp_refcur FOR SELECT LineID,BasketID,ProductItemNumber,Quantity,Price,SubTotal,AllocatTo1,AllocatTo2,TemplateID,
                 AlternativePartNO,CategoryType From BasketItems
          Where LineID = NVL(LineID,LineIDIn) AND BasketID = NVL(BasketID,BasketIDIn);
 --*********** This recordset needs to be returned
 End if;
 END;


I have tried to execute it on sql prompt as :
declare
 cccc SYS_REFCURSOR;
  LineIDIn number := '1';
  BasketIDIn number := '1';
  RequestedPage number := '1';
  PageSize number;
  PageCount NUMBER;
  RowCount  number;
 begin
 xSp_BasketItemsForSelect(cccc,1,1,1,PageSize,PageCount,RowCount);
End;


It also executes successfully.
Now I want to test for the records. So I tried as :

declare
 cccc SYS_REFCURSOR;
  LineIDIn number := '1';
  BasketIDIn number := '1';
  RequestedPage number := '1';
  PageSize number;
  PageCount NUMBER;
  RowCount  number;
 begin
 xSp_BasketItemsForSelect(cccc,1,1,1,PageSize,PageCount,RowCount);
 for c in  cccc loop
dbms_out.put_line(c.Quantity);

end loop;
End;


           *
ERROR at line 11:
ORA-06550: line 11, column 12:
PLS-00221: 'CCCC' is not a procedure or is
undefined
ORA-06550: line 11, column 2:
PL/SQL: Statement ignored


I am getting the above code.
Can you please advice as why I am getting this error.

Thanks for all your help on this
Re: Paging to show limited number of records [message #355486 is a reply to message #355480] Fri, 24 October 2008 07:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you search how to use REF CURSORs? How to loop through them?
Re: Paging to show limited number of records [message #356062 is a reply to message #355486] Wed, 29 October 2008 04:46 Go to previous message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Frank,
I tested looping through ref cursor using an example. I implemented in my procedure. I am pasting here the latest version of my procedure.

 CREATE OR REPLACE PROCEDURE xSp_BasketItemsForSelect(       
        emp_refcur out SYS_REFCURSOR,
        LineIDIn  IN  number,
        BasketIDIn IN number,
        RequestedPage IN number,
        PageSize IN OUT number,
        PageCount OUT NUMBER,
        RowCount OUT number
        
        )
    IS
    
    strsql varchar2(300);
    From1 NUMBER;
    To1 NUMBER;
    ReqPageVal NUMBER := RequestedPage;
 
    BEGIN
 
    IF pagesize IS NULL THEN
    PageSize := '20';
    END IF;
 
   if ReqPageVal is not null then
       DECLARE
       Begin
          select count(1) INTO rowcount from BasketItems
           Where LineID = nvl(LineID,LineIDIn) AND BasketID = nvl(BasketID,BasketIDIn);
           dbms_output.put_line('rowcount'||rowcount);
       if RowCount <= PageSize THEN
              PageCount := 1;
        dbms_output.put_line('pagesize'||pagesize);
          else
            SELECT CEIL(RowCount/PageSize) INTO pagecount from dual;
               IF PageCount <> (RowCount/PageSize) THEN
                  PageCount := PageCount + 1;
               ELSE
                 PageCount := PageCount + 0;
              END IF;              
         End IF;
        dbms_output.put_line('pagecount'||pagecount);
 
         if ReqPageVal > PageCount then
            ReqPageVal := PageCount;
         End if;
         if ReqPageVal < 1 then
            ReqPageVal := 1;
         End if;
         dbms_output.put_line('ReqPageVal'||ReqPageVal);
         if ReqPageVal > 1 then
            From1 := ((ReqPageVal -1) * PageSize) + 1;
            To1 := (ReqPageVal * PageSize) ;
         else
            From1 := 1;
            To1 := PageSize;
        End if;
  dbms_output.put_line('From1'||From1);
  dbms_output.put_line('To1'||To1);
  
    OPEN emp_refcur FOR SELECT rownum id, LineID,ProductItemNumber,Quantity,Price,SubTotal,
ALLOCATTO1, AllocatTo2,TemplateID, AlternativePartNO,CategoryType, BasketID
From BasketItems
      Where LineID = NVL(LineID,LineIDIn) AND BasketID = NVL(BasketID,BasketIDIn)
        AND rownum >= from1 AND rownum <= To1; 
 
      end;
  else
      PageCount := -1;
      RowCount := -1;
      OPEN emp_refcur FOR SELECT LineID, ProductItemNumber,Quantity,Price,SubTotal,
ALLOCATTO1, AllocatTo2,TemplateID, AlternativePartNO,CategoryType, BasketID
From BasketItems
          Where LineID = NVL(LineID,LineIDIn) AND BasketID = NVL(BasketID,BasketIDIn);

 End if;
 END;
/


I have created another procedure to call this procedure as ref cursor needs to be declared.


create or replace procedure xSp_BasketItems_call(
    lineid number,BasketID number,RequestedPage number)
   is
    c_cursor SYS_REFCURSOR;
    r_basket BasketItems%rowtype;
    LineIDIn number := lineid;
     BasketIDIn number := BasketID;
     ReqPageIn number := RequestedPage;
     PageSize number;
     PageCount NUMBER;
     RowCount  number;
    begin
    xSp_BasketItemsForSelect(c_cursor,LineIDIn,BasketIDIn ,ReqPageIn ,PageSize,PageCount,RowCount);
loop
 fetch c_cursor into r_basket;    
   exit when c_cursor%notfound;
    dbms_output.put_line('r_basket.quatity');
    dbms_output.put_line('r_quantity'||r_quantity);
  end loop;
    close c_cursor;
End;


I was testing the procedure when I executed it :
exec xSp_BasketItems_call(1,1,1);

Regards,
Soni
Previous Topic: order by relation 'father' , 'mother'
Next Topic: Ask a question
Goto Forum:
  


Current Time: Sat Dec 03 04:07:26 CST 2016

Total time taken to generate the page: 0.06022 seconds