Wednesday, November 18, 2009

What happen when not exception handle ininsert statement ?

What happen when not exception handle ininsert statement ?
================================================== ========
----create a test table
CREATE TABLE test( col number);
---add primary ket to that table
ALTER TABLE test
ADD CONSTRAINT pk_test
PRIMARY KEY(col)
USING INDEX;
---create a procedure to insert record into that table
CREATE OR REPLACE PROCEDURE proce_insert IS
BEGIN
INSERT INTO testVALUES(1);
END proce_insert;
/

----create and Run a anonymous block without exception handling
BEGIN
proce_insert;
proce_insert;
END;
/
--generating error and
-- no records inserted as expected
SELECT * FROM test;

----Create and Run a anonymous block with exception handling
BEGIN
proce_insert;
proce_insert;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
--no error occure and one record inserted
SELECT * FROM test;

commit;

delete from test;

No comments: