OraFAQ Forum: SQL & PL/SQL » HELP!!! How to preserve data order in table when fetching data in there?

Home » SQL & PL/SQL » SQL & PL/SQL » HELP!!! How to preserve data order in table when fetching data in there?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

HELP!!! How to preserve data order in table when fetching data in there? [message #36295]

Wed, 14 November 2001 11:46

Newbie
Messages: 19
Registered: November 2001

Junior Member

Hi, currently I wrote a procedure to fetch data from a query to a table. The code is somewhat like this:

PROCEDURE a IS

v_date Date;
v_unit Number;
v_price Number(20, 2);
v_total Number(20, 2);

CURSOR c_query IS
SELECT tbl1.PDate, tbl1.unit, tbl2.price, ROUND((tbl1.unit)*(tbl2.price), 2) AS total
FROM tbl1, tbl2
WHERE (tbl1.PDate = tbl2.PDate)
ORDER BY tbl1.PDate ASC;

BEGIN

DELETE FROM tbl3;

OPEN c_query;

LOOP
FETCH c_query INTO
v_date,
v_unit,
v_price,
v_total;
EXIT WHEN c_query%NOTFOUND;

INSERT INTO tbl3 (PDate, Unit, Price, Total)
VALUE (v_date,
v_unit,
v_price,
v_total);
END LOOP;

COMMIT;

CLOSE c_query;

END;

The problem is after fetching data into tbl3, the data is not in the same order as shown in the query(c_query). For example, in the query, the data is:

1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4

when fetched to tbl3 the data order seems to be random:

1 1 1 1
3 3 3 3
2 2 2 2
4 4 4 4

or some other variations. I also discovered that data remains in the same order after fetching for the first time to tbl3 when tbl3 is just being created. But when tbl3 already exists and data has been fetched for at least once, then when you run the procedure above, data don't line up correctly in the table.

I figured that this problem maybe caused by the way memory is setup in Oracle. Eventhough data from tbl3 is deleted using DELETE FROM tbl3; the memory blocks for the deleted data still exist. When fetching new data into the table, new memory blocks are created, and data are being put in there, that might be the reason why data is not in order, but then again I don't know much about Oracle at all, and it's only my speculation.

Anyway, since this procedure is supposed to be run automatically every month, it is not feasible to manually delete and create tbl3 every time to preserve data order before the fetch data process.

My question is can any of the Oracle gurus out there suggest a solution to this problem?

Is there a way to preserve data order to tbl3 without deleting and creating that table every time before fetching data?

Thanks in advance for your help!

----------------------------------------------------------------------

Report message to a moderator

Re: HELP!!! How to preserve data order in table when fetching data in there? [message #36297 is a reply to message #36295]

Wed, 14 November 2001 13:06

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Well, first of all, you should just:

PROCEDURE a IS
BEGIN
DELETE FROM tbl3;

INSERT INTO tbl3 (PDate, Unit, Price, Total)
SELECT tbl1.PDate, tbl1.unit, tbl2.price, ROUND((tbl1.unit)*(tbl2.price), 2) AS total
FROM tbl1, tbl2
WHERE (tbl1.PDate = tbl2.PDate);

COMMIT;
END;

No need for a cursor here.

And secondly, there is no guaranteed "order" to a table other than what you specify in the ORDER BY clause on a select.

So, instead of:

select * from tbl3;

you'll need to:

select * from tbl3 order by pdate;

----------------------------------------------------------------------

Report message to a moderator

Previous Topic:	Help Convert VARCHAR2 to number
Next Topic:	HELP !! How to execute an input parameter sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 22:10:22 CST 2024