• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3547
  • Last Modified:

Handling an oracle error in a execute immediate loop run a pl/sql block

I'm running all of this in a package body/procedure but dumbed it down to see if you guys have any handy solutions.
I have a table that contains a field with anonymous pl/sql.
I'm running a procedure that goes thru the table and does an execute immediate.
Updates the LUD (last updated time) and if it ran into an error, logs the error in the table.

Everything works fine except if had ran into a very malformed data in the statement (i.e. just contains a 'BEGIN'.  The whole procedure fails and halts.

I'm okay with it writing an error out on that row but it doesn't  I get the whole procedure to fail and no exception properly handled.
[1]: (Error): ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 25 ORA-06550: line 1, column 5: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     begin case declare exit for goto if loop mod null pragma    raise return select update while with <an identifier>    <a double-quoted delimited-identifier> <a bind variable> <<    close current delete fetch lock insert open rollback    savepoint set sql execute commit forall merge    <a single

I'd want that row with the malformed data to error and just write out an error occurred to the row in the table and move on.
thoughts?
CREATE TABLE mytable
(utn NUMBER  PRIMARY KEY
, stmt VARCHAR2(4000)
, lud DATE -- processed date
, stmt_error VARCHAR2(4000) -- if an error occurs
)
/
 
INSERT INTO mytable
("UTN","STMT","LUD","STMT_ERROR")
VALUES
(4,'begin null; end;',NULL,NULL)
/
INSERT INTO mytable
("UTN","STMT","LUD","STMT_ERROR")
VALUES
(5,'begin null; end;',NULL,NULL)
/
 
 
DECLARE  
    error_code          NUMBER;
    error_message       VARCHAR2(355);
 
    CURSOR curCH_PROC IS 
        SELECT * 
        FROM mytable
        WHERE lud IS NULL 
        ORDER BY utn;
 
BEGIN
   
    FOR myrec IN curCH_PROC LOOP
        BEGIN
            execute IMMEDIATE myrec.stmt;
            
            UPDATE mytable
            SET lud = SYSDATE
            WHERE utn = myrec.utn;
            
            COMMIT;
            
        EXCEPTION WHEN OTHERS THEN
            error_code    := SQLCODE;
            error_message := SQLERRM;
            
            UPDATE mytable
            SET lud = SYSDATE
                ,stmt_error = SUBSTR('ERROR CODE: ' || TO_CHAR(error_code) || ' ERROR_MESSAGE: ' || ERROR_MESSAGE, 1, 4000)
            WHERE utn = myrec.utn;      
            
            COMMIT;          
        END; 
    END LOOP;
END;
 
-- above  data set works well
-- now  try this to show that an error is capture properly.
truncate table mytable
/
INSERT INTO mytable
("UTN","STMT","LUD","STMT_ERROR")
VALUES
(3,'begin null end;',NULL,NULL)
/
INSERT INTO mytable
("UTN","STMT","LUD","STMT_ERROR")
VALUES
(4,'begin null; end;',NULL,NULL)
/
INSERT INTO mytable
("UTN","STMT","LUD","STMT_ERROR")
VALUES
(5,'begin null; end;',NULL,NULL)
/
-- above data set works and utn row 3 shows that it errors.
--
-- but now the problem row utn2 in the next data set.
truncate table mytable
/
INSERT INTO mytable
("UTN","STMT","LUD","STMT_ERROR")
VALUES
(2,'begin',NULL,NULL)
/
INSERT INTO mytable
("UTN","STMT","LUD","STMT_ERROR")
VALUES
(3,'begin null end;',NULL,NULL)
/
INSERT INTO mytable
("UTN","STMT","LUD","STMT_ERROR")
VALUES
(4,'begin null; end;',NULL,NULL)
/
INSERT INTO mytable
("UTN","STMT","LUD","STMT_ERROR")
VALUES
(5,'begin null; end;',NULL,NULL)
/

Open in new window

0
E43509
Asked:
E43509
  • 4
  • 2
  • 2
1 Solution
 
Ivo StoykovCommented:
what is there in?
{code}myrec.stmt{code}
I
0
 
E43509Author Commented:
In my 'real' one, it is suppose to contain as shown below.
My samples above just have
BEGIN
   null;
END;

just to show the issue.
I received a malformed pl/sql that just had "BEGIN" and with nothing else.  Instead of just writing an error out, the whole routine failed and would not process any records.
BEGIN
    rpx_pkg.set_training('123445566','ACCTRAIN');
    COMMIT;
END;

Open in new window

0
 
E43509Author Commented:
Or the statement code could be missing a semicolon after the END
BEGIN
  null;
END

It seems that any malformed pl/sql block not only errors (which is ok) but doesn't allow the loop to just keep on going and processing.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
JamesMcNCommented:
Your variable "error_message;" is too small for the string returned by SQLERRM. Try increasing it to 1000 from 355. This is the reason why your script is failing.

Also, there is overhead associated with each and every COMMIT statement, as the LGWR process has to be called to write the results to disk. Instead, why not run the entire script within a single transaction, and just have a local procedure to store error messages as part of an autonomous transaction (see attached script).

Hope this helps.

James
DECLARE  
    error_code          NUMBER;
    error_message       VARCHAR2(1000);
 
    CURSOR curCH_PROC IS 
        SELECT * 
        FROM mytable
        WHERE lud IS NULL 
        ORDER BY utn;
    
    PROCEDURE lp_log_error(
       sp_utn        IN NUMBER,
       sp_error_code IN NUMBER,
       sp_error_msg  IN VARCHAR2
    )
    IS
       PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
       UPDATE mytable
          SET lud = SYSDATE,
              stmt_error = SUBSTR('ERROR CODE: ' || TO_CHAR(error_code) || ' ERROR_MESSAGE: ' || ERROR_MESSAGE, 1, 4000)
        WHERE utn = sp_utn;      
            
       COMMIT;
    END lp_log_error;
BEGIN
    FOR myrec IN curCH_PROC LOOP
        BEGIN
            execute IMMEDIATE myrec.stmt;
            
            UPDATE mytable
            SET lud = SYSDATE
            WHERE utn = myrec.utn;
            
        EXCEPTION WHEN OTHERS THEN
            lp_log_error( myrec.utn, SQLCODE, SQLERRM );       
        END;
    END LOOP;
    
    COMMIT;
END;
/

Open in new window

0
 
Ivo StoykovCommented:
you have smae values on rows 9 & 14 as rows 66 & 71

if you remove either set and run script on clear, there is no errors (at least on my set)
HTH
i
0
 
E43509Author Commented:
Perfect ... missed that variable size completely.  Thanks for the additional thoughts on managing the commits!
0
 
JamesMcNCommented:
Sorry, forgot to mention aswell that if you decide to go with my suggested script, you won't need the following variables anymore:

error_code          NUMBER;
error_message       VARCHAR2(1000);
0
 
E43509Author Commented:
Yep, I'm employing your logic.  Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now