executing deletes in Pro*C

[rohanem]

Hi,
Can anyone suggest a faster way to do deletes using Pro*C..I have to delete about 3 million records from a table, which cannot be done at one shot because of the rollback segment problem..So I am deleting 100,000 rows at a time-

while (1)
{
EXEC SQL delete from table_name where date_col= :date_val and rownum<100000;
if(sqlca.sqlcode==1403)
break;
}

 

[lewisp]

I would use the Pro*C array processing method. First create an array of chars, large enough to hold ROWID. Make the array (say) 1000.

Next, select all the ROWIDS from the target table into the array using your where clause from above. This will pull 1000 ROWIDS out at a time.

Finally, use the array of ROWIDS to delete from the same table.

Loop round, selecting and deleting 1000 rows at a time until less than 1000 rows are selected into the array. Commit every 100 times round the loop.

 

[rohanem]

thanks lewis, but I think I am missing something here..This is what I did-

declare varchar del_rowid[1000];
.
.
.
EXEC SQL select rowid into :del_rowid from table_name where date_col=:date and rownum<1001;
EXEC SQL delete from table_name where rowid=:del_rowid;

Now this should delete 1000 rows right?But it is not doing so..Can you please help me with a small example as to how I an achieve this?

 

[lewisp]

You need to make the varchar multi-dimensional. So far you have only declared a single varchar of 1000 characters in size. Try something like

[tt]EXEC SQL BEGIN DECLARE SECTION;
VARCHAR del_rowid[1000][30];
.
.
[/tt]

 

[rohanem]

Where am I going wrong here-

EXEC SQL BEGIN DECLARE SECTION;

char del_rowid[1000][40];

while (1) { EXEC SQL select rowid into :del_rowid from table_name where date_col=:date_var;
if(NO_DATA_FOUND) break;
else {
for(i=0;i<1000;i++) {
EXEC SQL delete from table_name where rowid=
(select rowid from table_name where rowid=:del_rowid);
}
EXEC SQL COMMIT;
}

Somehow, it is not enetering the &quot;else&quot; part at all...Do I need to open a cursor to fetch 1000 values into del_rowid or does a simple EXEC SQL select into...as above suffice?

 

[lewisp]

Your EXEC SQL will need to be something like this:

[tt]EXEC SQL
select rowid
into :del_rowid
from table_name
where date_col=:date and rownum<1001;

EXEC SQL FOR <no_of_rows_returned>
delete table_name
where rowid=:del_rowid;[/tt]

You can determine the number of rows returned by reading ORA_TOT_ROWS and subtracting from it the number of rows returned by the previous fetch.

Also, its a while since I used Pro*C, but you used to have to null terminate the strings returned by Oracle. Do you still have to do that manually?

 

[rohanem]

yes, we still have to null-terminate the string returned by oracle.
Thanks for your help