| Insert INTO/Select/Returning Into (Bulk Collect) [message #154282] |
Fri, 06 January 2006 10:22  |
krishmoore
Messages: 5
Registered: May 2005
|
Junior Member |
|
|
Hi All,
I am jus wondering whether this is possible.
My requirement is this.
There are around 8 million rows in table A.We want to identify certain rows from this table based on some condition,then move these rows to another table B,And then delete the identifed rows in table A.
So what we cud Ideally do is
Insert into Table B selecting the rows from A (after identifying) then delete them from A using ROWID.
This is my code Snippet.
==============================================================
DECLARE
TYPE TY_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
ALLOT_ROWID TY_ROWID;
BEGIN
INSERT INTO RET_TEST
SELECT A.ROWID ROW_ID, A.CT_PRODUCT_ID, A.INSTANCE, A.BILLING_TARGET_IND, A.BILLING_TARGET, A.NO_OF_UNITS,
A.RESULT_TYPE_FLAG,A.STAMP_ADD_DTIME,
APPS.MAINT_LOCATION_CD,
APPS.MAIN_LOCATION_DESC ,
OFFCE_CNTRY.CV_OFCE_ID_C,
OFFCE_CNTRY.CV_OFCE_N,
OFFCE_CNTRY.CV_CNTRY_N
FROM A
INNER JOIN C
ON A.BILLING_TARGET = TO_CHAR(APPS.AII_APPLN_ID_C)
-- Trying to Return the ROWID for those rows which are selcted
-- so that i can do a bul delete
RETURNING A.ROWID BULK COLLECT INTO ALLOT_ROWID;
FORALL II IN ALLOT_ROWID.FIRST .. ALLOT_ROWID.LAST
DELETE FROM A
WHERE ROW_ID = ALLOT_ROWID(II);
END;
==============================================================
I am gettin the error ORA-00933 SQL Command Not Properly ended.
Why is this Error??
is there any better way to do this??
Any help is appreciated.
Regards
Krishna
|
|
|
|
| Re: Insert INTO/Select/Returning Into (Bulk Collect) [message #154306 is a reply to message #154282] |
Fri, 06 January 2006 12:22  |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
|
You can't use RETURNING in combination with a subquery on an insert, only with a VALUES clause. Assuming, however, that your where clause for you insert subquery is already efficient, you could just use the same thing for the delete. The ROWID delete usually doesn't buy you a whole lot more speed.
|
|
|
|
| Re: Insert INTO/Select/Returning Into (Bulk Collect) [message #154329 is a reply to message #154306] |
Fri, 06 January 2006 19:46 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Try a multi-table insert to do the insert into C whilst saving the rowids into a temporary table. Then delete the rows in A where rowids exist in the temporary table. (Excuse syntax, I don't have Oracle on this computer).
CREATE GLOBAL TEMPORARY TABLE TEMP_ROWID (
RID ROWID
) ON COMMIT PRESERVE ROWS;
INSERT ALL
INTO C (col1, col2, col3, ...)
VALUES (a.col1, a.col2, a.col3, ...)
INTO TEMP_ROWID (rid)
VALUES (a.rowid)
SELECT a.rowid, ....
FROM A
JOIN ....
/
DELETE FROM A
WHERE ROWID IN (SELECT RID FROM TEMP_ROWID)
/
_____________
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
