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');
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
Forum Rules
Click Here to Expand Forum to Full Width
Bookmarks