Stored procedure causes ORA-0000
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Stored procedure causes ORA-0000

  1. #1
    Join Date
    Oct 2000
    Posts
    48
    We have a stored procedure that truncates several tables. However, running it generates ORA-0000 errors on all tables, yet the tables appear to be truncated (but I'm not 100% sure there were rows in these tables to begin with):

    truncated FABBRCC :ORA-0000: normal, successful completion
    truncated FABCKMC :ORA-0000: normal, successful completion
    truncated FARCKMC :ORA-0000: normal, successful completion
    truncated FBRATHC :ORA-0000: normal, successful completion

    What is causing these errors? Should I just ignore them, or does the procedure need to be altered? The procedure looks like this:

    PROCEDURE TRUNC_FIMSMGR_TABLES
    as
    procedure one_table(tab_name varchar2) is
    cursor_id integer; -- holds cursor id
    return_value integer; -- holds call return value
    str varchar2(150); -- string to hold DDL statement
    BEGIN
    cursor_id := dbms_sql.open_cursor;
    str := 'truncate table fimsmgr.' || tab_name;
    dbms_sql.parse (cursor_id, str, dbms_sql.v7);
    return_value := dbms_sql.execute(cursor_id);
    dbms_sql.close_cursor(cursor_id);
    commit;
    DBMS_OUTPUT.PUT_LINE
    ('truncated ' || tab_name || ' :' ||
    sqlerrm );
    EXCEPTION
    When Others then
    DBMS_OUTPUT.PUT_LINE
    ('error ' || tab_name || ' :' ||
    sqlerrm);
    dbms_sql.close_cursor(cursor_id);
    END;
    BEGIN /* Main Procedure */
    DBMS_OUTPUT.PUT_LINE
    ('TRUNCATE FIMSCOL TABLES' || to_char(sysdate,' DD-MON-YYYY HH24:MM'));
    one_table ('FABBRCC');
    one_table ('FABCKMC');
    one_table ('FARCKMC');
    one_table ('FBRATHC');

  2. #2
    Join Date
    Mar 2001
    Posts
    314
    This is NO error. Oracle raises ORA-0000 on successful completion of something.

    -amar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width