This content has been marked as final. Show 4 replies
So what are the preceding error messages?
oerr ora 2063 02063, 00000, "preceding %s%s from %s%s" // *Cause: an Oracle error was received from a remote database link. // *Action: *refer to the preceding error message(s)*
When posting code, use the \
tag at the start and at the end of the code being copy and and pasted. This formats the code into a readable format for forum members to view.. Also, it is a bad idea to commit inside a loop. Each commits does "+work"+ and user resources. If you loop iterates a 1000 times, it means doing a "1000 units of work" plus "using a 1000 units of resources". It is much faster, and use less resources, when you only commit once at the end of the loop. You also need to consider what happens when doing the 501st row, the loop fails. You now have 500 updated rows and 500 rows that have not been updated. Only.. which is which? It makes far better sense to update all 1000 rows and succeed, or not to update any rows (using a rollback) when any single row update fails. Finally, you should use SQL to do as much of the processing for you. It is slow and expensive using a SQL to ship data from the SQL engine into the PL engine, and then ship that very same data back to the SQL engine to run another SQL statement. Stay within the SQL engine. You could rewrite this entire block of code as a single SQL statement.
I had something here but decided it wasn't a good reply so delete.
Edited by: kendenny on Jul 14, 2009 11:39 AM
First problem I see:
CURSOR c1 IS SELECT file_number,SUBSTR(file_name,INSTR(file_name,'.',1,2)+1,LENGTH(file_name)) AS ftm_id FROM TABLE1 WHERE trim(a.start_TIME) = trim(SYSDATE-1);
What is a? There is no table here aliased as a.
I'll assume that start_time is a column of table1.
Also trim is a string function. Did you mean to use trunc? I'll assume so.
Like has previously been mentioned the real error was on the message prior to the one you quoted in the title.
As also previously mentioned using PL/SQL to do something that can be done in SQL is av ery bad practice. This entire procedure can be done by a single SQL statement:
update table1 t set t.circle=(select circle from METHODLOG@ictprd_to_amdica a,METHODINFO_INTEC_DASHBORD@ictprd_to_amdica b WHERE a.METHOD=b.method_name AND FTMID=SUBSTR(t.file_name,INSTR(t.file_name,'.',1,2)+1)) where trunc(start_time)=trunc(sysdate-1);