SQL & PL/SQL

Hi All,

I'm having following procedure

--------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE proc_test_insert
(
p_emp_no IN NUMBER := NULL,
p_emp_name IN VARCHAR2 := NULL,
p_dept_name IN VARCHAR2 := NULL,
p_return_status OUT VARCHAR2

)
AS
err_code NUMBER (5);
program_name ERROR_LOG.PROGRAM_NAME%TYPE :='proc_test_insert';
error_desc VARCHAR2(500);

BEGIN

p_return_status := 'Success';
err_code := 100;

BEGIN

INSERT INTO EMP(emp_no,emp_name,doj) VALUES (emp_seq.NEXTVAL,p_emp_name,SYSDATE);

EXCEPTION WHEN OTHERS THEN

err_code := 101;
ERROR_DESC := SUBSTR(SQLERRM, 1 , 512);
error_info := 'err_code:'||To_Char(err_code);
ERROR(program_name,ERROR_DESC,error_info,'');
DBMS_OUTPUT.PUT_LINE('INSIDE EXCEPTION BLOCK::'||ERROR_DESC||' , err_code:'||To_Char(err_code));
return_status := 'Fail';
ROLLBACK;

END;

IF err_code = 100
THEN
BEGIN

INSERT INTO DEPT(dept_no,dept_name,active_status) VALUES (dept_seq.NEXTVAL,p_dept_name,'A');

EXCEPTION WHEN OTHERS THEN

err_code := 102;
ERROR_DESC := SUBSTR(SQLERRM, 1 , 512);
error_info := 'err_code:'||To_Char(err_code);
ERROR(program_name,ERROR_DESC,error_info,'');
DBMS_OUTPUT.PUT_LINE('INSIDE EXCEPTION BLOCK::'||ERROR_DESC||' , err_code:'||To_Char(err_code));
return_status := 'Fail';
ROLLBACK;

END;

IF err_code = 100
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;


END proc_test_insert;
/
--------------------------------------------------------------------------------


Can anyone please let me know, how to declare a transaction inside a procedure? the callilng program is not initiating any transaction so I'd like to manage transaction inside a procedure.

Please correct this procedure code using transaction processing.

Thanks in advance.

Regards,
Sharath.