4 Replies Latest reply on Jul 14, 2009 6:48 PM by kendenny

    ORA-02063: preceding line error while using dblink

    711794
    711794 Jul 14, 2009 10:07 AM
      CREATE OR REPLACE PROCEDURE TEST AS
      BEGIN
      DECLARE

      v_circle VARCHAR(10);

      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);
      BEGIN

      FOR r1 IN c1 LOOP
      BEGIN

      dbms_output.put_line(r1.file_number);

      SELECT CIRCLE INTO v_circle FROM METHODLOG@ictprd_to_amdica a,METHODINFO_INTEC_DASHBORD@ictprd_to_amdica b
      WHERE a.METHOD=b.method_name AND FTMID=r1.ftm_id;

      UPDATE TABLE1 SET CIRCLE=v_circle WHERE FILE_number=r1.file_number;

      COMMIT;

      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      NULL;
      END;

      END LOOP;

      END;
      END;
        • 1. Re: ORA-02063: preceding line error while using dblink
          Toon Koppelaars
          Toon Koppelaars Jul 14, 2009 10:09 AM (in response to 711794) 
          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)*
          So what are the preceding error messages?
          • 2. Re: ORA-02063: preceding line error while using dblink
            Billy~Verreynne
            Billy~Verreynne Jul 14, 2009 12:36 PM (in response to 711794)
            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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
            • 3. Re: ORA-02063: preceding line error while using dblink
              kendenny
              kendenny Jul 14, 2009 6:40 PM (in response to 711794)
              I had something here but decided it wasn't a good reply so delete.

              Edited by: kendenny on Jul 14, 2009 11:39 AM
              • 4. Re: ORA-02063: preceding line error while using dblink
                kendenny
                kendenny Jul 14, 2009 6:48 PM (in response to 711794)
                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);
                trim(a.start_time).
                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);