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  |
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  |
Frank
Messages: 7901
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 |
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 |
Frank
Messages: 7901
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 |
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 #355443 is a reply to message #355427] |
Fri, 24 October 2008 01:53 |
flyboy
Messages: 1903
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 |
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 |
Frank
Messages: 7901
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 |
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 |
Frank
Messages: 7901
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 #355464 is a reply to message #355462] |
Fri, 24 October 2008 04:07 |
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  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 #355480 is a reply to message #355472] |
Fri, 24 October 2008 06:31 |
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 #356062 is a reply to message #355486] |
Wed, 29 October 2008 04:46 |
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
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 22:56:47 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
