asked on

ORA-0000: normal, successful completion

what is the meaning of this error

Guy Hengel [angelIII / a3]

this means that there has been no error, actually.

Jacobfw

This is a successful completion code.
Unless you are suspecting your procedure is not working, this is a valid and good outcome.

You can use this to test for successful completion as well in a calling procedure.

gram77

ASKER

The main proc calls 4 procs, the 4 procs insert data into 4 temp tables.
That's pretty much what the proc does.

PROCEDURE main_proc (
p_param1 col1%TYPE,
p_param2 col2%TYPE,
p_param3 col3%TYPE,
v_var4 col4%TYPE,
rc_stats out sys_refcursor
) IS

BEGIN

proc1 (
p_param1 => v_var1,
p_param2 => v_var2,
p_param3 => v_var3,
v_var4 => v_var4
      );

IF v_wildcard_status IS NULL
THEN
-- Clean up tt_ tables...
DELETE FROM tt_table1;
DELETE FROM tt_table2;
DELETE FROM tt_table3;
COMMIT;

proc2 (
p_param1 => v_var1,
p_param2 => v_var2,
p_param3 => v_var3,
v_var4 => v_var4,
p_inst_vndr_id => NULL
);-- inserts into tt_table1

proc3 (
p_param1 => v_var1,
p_param2 => v_var2,
p_param3 => v_var3,
v_var4 => v_var4,
p_inst_vndr_id => NULL
);-- inserts into tt_table2

proc4 (
p_param1 => v_var1,
p_param2 => v_var2,
p_param3 => v_var3,
v_var4 => v_var4,
p_inst_vndr_id => NULL
);-- inserts into tt_table3

END IF;

write_to_log('END: main proc');

END main_proc;

****************************************

proc1 (
p_param1 => v_var1,
p_param2 => v_var2,
p_param3 => v_var3,
v_var4 => v_var4,
p_inst_vndr_id => NULL
);-- inserts into tt_table1

      BEING
       write_to_log('START: proc1');
            insert into tt_table1 values (..);
       write_to_log('END: proc1');
      END;

****************************************
proc2 (
p_param1 => v_var1,
p_param2 => v_var2,
p_param3 => v_var3,
v_var4 => v_var4,
p_inst_vndr_id => NULL
);-- inserts into tt_table2

      BEING
       write_to_log('START: proc2');
            insert into tt_table2 values (..);
       write_to_log('END: proc2');
      END;

****************************************
      proc3 (
p_param1 => v_var1,
p_param2 => v_var2,
p_param3 => v_var3,
v_var4 => v_var4,
p_inst_vndr_id => NULL
);-- inserts into tt_table3

      BEING
       write_to_log('START: proc3');
            insert into tt_table3 values (..);
       write_to_log('END: proc3');
      END;

****************************************
proc4 (
p_param1 => v_var1,
p_param2 => v_var2,
p_param3 => v_var3,
v_var4 => v_var4,
p_inst_vndr_id => NULL
);-- inserts into tt_table1

      BEING
       write_to_log('START: proc4');
            insert into tt_table4 values (..);
       write_to_log('END: proc4');
      END;

*******
Logs:
*******
END: proc1
START: proc1

END: proc2
START: proc2

END: proc3
START: proc3

END: proc4
START: proc4

END: main proc
START: main proc

Jacobfw

And your question is?

gram77

ASKER

what is the issue with this proc, why is it giving out the error

Jacobfw

It is not an error.
As indicated above, it is a "normal successful" completion message.

gram77

ASKER

normally, oracle does not give out any message when it is a normal, successfull execution.
so why does it show this messgage here, and how can i track which proc, line# is giving out this message

ASKER CERTIFIED SOLUTION

Jacobfw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gram77

ASKER

Excellent, business rule violated, and SQLERRM message set. that is the cause of the issue.