INSERTing data in an SORTED fashion

[uparna]

Hi, This is quite a verbose post and i apologise....the problem is very simple(stated by the subject), but i am mentioning it in an example so that we can be clear of what i am talking abt.....

I Have 3 Tables called
Initial_Input (App_Key NUMBER NOT NULL,Status CHAR(1) NULL,Paper_Key NUMBER NULL, Paper_Descr VARCHAR2(1000) NULL),
Processing (App_Key NUMBER NOT NULL,Status CHAR(1) NULL,Paper_Key NUMBER NULL, Paper_Descr VARCHAR2(1000) NULL)and
Final_Outcome(App_Key NUMBER NOT NULL,Status CHAR(1) NULL,Paper_Key NUMBER NULL, Paper_Descr VARCHAR2(1000) NULL).

The user loads an excel sheet via application to the Initial_Input Table to the Descr Column only. The Application , while loading the data in to the table , uses a sequence and loads the APP-KEY column also. Suppose the user's excel sheet contained the following data :
Paper_Descr
-----------
Paper 1
Paper 2
Paper 3
Paper 4
Paper 5
Paper 6

the Apploads the following data into the initial_Input table
APP_KEY STATUS PAPERKEY Paper_Descr
--------------------------------------------
101 Paper 1
102 Paper 2
103 Paper 3
104 Paper 4
105 Paper 5
106 Paper 6


Now , a database procedure takes this data from the intial table and puts it into the Processing table using

INSERT INTO PROCESSING SELECT * FROM INITIAL_INPUT ;(Data inserted is obviously not in a sorted (RECKEY) fashion.)

Now there are a set of validations on the descr column and some of them will 'SUCCESS' and others are 'REJECT'. ALL these rows are put into FINAL_OUTCOME , and a set of base tables are inserted with the 'SUCCESS' data.

Here is what the data looks like in the PROCESS TABLE after the processing of data has been done:
APP_KEY STATUS PAPERKEY Paper_Descr
--------------------------------------------
102 R Paper 2
106 S Paper 6
103 S Paper 3
101 S Paper 1
104 R Paper 4
105 S Paper 5

Now , SP generates PAPERKEYS for the successful records using a sequence

UPDATE PROCESSING SET PAPERKEY = SEQ_X.NEXTVAL where STATUS = 'S';

here is the data in the PROCESS TABLE now:
APP_KEY STATUS PAPERKEY Paper_Descr
--------------------------------------------
102 R Paper 2
106 S 1111 Paper 6
103 S 1112 Paper 3
101 S 1113 Paper 1
104 R Paper 4
105 S 1114 Paper 5

and THIS is where i have the problem: i want the generated paperkeys to be in the order or the APPKEYS. For this , i tried the following: i tried inserting rows in PROCESSING in the order of APPKEYS ..... this doesnt seem possible : ORDER BY STATEMENTS inside a INSERT statement in an SP doesnt work.
SO , instead of INSERT INTO PROCESSING SELECT * FROM INITIAL_INPUT ;,
i created a view VW_INITIAL_INPUT which was SELECT * FROM INITIAL_INPUT order by APPKEY; and i changed the statement to
INSERT INTO PROCESSING SELECT * FROM VW_INITIAL_INPUT ;

This doesnt seem to affect order of insertion at all : PROCESSING still has the rows in an unsorted manner. I then tried this to change the update statement:
UPDATE PROCESSING SET PAPERKEY = SEQ_X.NEXTVAL where STATUS = 'S' ORDER BY RECKEY;

Obviously this is an invalid SQL statement.

I dont want to use cursors for inidividual rows in the insert/update statement as they are costly (even if i use BIND variables). The SP is very complicated ( 2000+ lines) and i am giving a padded down version of it so that we can analyze the problem area better. Any ideas? Help !

Thanks for reading anyway........i know its a long post and i appreciate your patience.

Regards,
S. Jayaram Uparna .

 

[SpiritOfLennon]

Have you tried
INSERT INTO PROCESSING SELECT * FROM VW_INITIAL_INPUT order by appkey?

SOL
I'm only guessing but my guess work generally works for me.

 

[uparna]

Hi Spirit ,
Yeah , i did a while ago and the SP doesnt compile with it. Doest work..

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Uparna,

Following is a Stored Procedure that successfully is "INSERTing data in an SORTED fashion":

SQL> truncate table emp6;

Table truncated.

SQL> create or replace procedure uparna is
  2  begin
  3  insert into emp6  select * from (select * from s_emp order by last_name, first_name);
  4  end;
  5  /

Procedure created.

SQL> exec uparna

PL/SQL procedure successfully completed.

SQL> select last_name, first_name from emp6 where rownum <= 10;

LAST_NAME                 FIRST_NAME
------------------------- -------------------------
Biri                      Ben
Catchpole                 Antoinette
Chang                     Eddie
Dancs                     Bela
Dumas                     Andre
Garcia                    Carmen
Giljum                    Henry
Havel                     Marta
Maduro                    Elena
Magee                     Colin

10 rows selected.

SQL>

Does this accomplish what you need?

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:37 (16Feb04) UTC (aka "GMT" and "Zulu&quot, 11:37 (16Feb04) Mountain Time)

 

[uparna]

Please excuse me while i go to the wall and bang my head.

Regards,
S. Jayaram Uparna .

 

[sem]

And once more: withough explicit ORDER BY clause all your results are just a chance, not a rule. Especially in parallel environment.

Regards, Dima

http://seminihin.narod.ru

 

[uparna]

HI Santa ,
back from banging my head........did 2 test runs......results are not as expected. .the Processing table still has the rows in an unsorted fashion, regardless of the double inline view.

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Uparna,

Sem delivered the correct explanation/disclaimer: "Without explicit ORDER BY clause all your results are just a chance, not a rule. (Especially in parallel environment.)" So, despite the "correct" order from the INSERT statement, relational tables are not supposed to have any particular internal order. So, if you depend upon a particular order from a table, then the only appropriate method is with an ORDER BY in the SELECT statement. Is there anything preventing you from SELECTing data with an ORDER BY?

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:10 (16Feb04) UTC (aka "GMT" and "Zulu&quot, 15:10 (16Feb04) Mountain Time)