Oracle Universal Stored Procedures

Create or replace procedure Proc_data_check is

    -- Defining Variables

V_LOCK_ID INTEGER;

V_lockhandle VARCHAR (128);

V_RELEASE_ID INTEGER;

    -- To define an error exception message encoding

V_error_code VARCHAR2 (10): = ";

    -- Defining Error Exception description information

V_error_message VARCHAR2 (1000): = ";

Cursor Check_orgs is

Select Org_code, org_name from check_org;

Begin

Dbms_lock. Allocate_unique (' Proc_data_check ', V_lockhandle, 86400);

v_lock_id: = Dbms_lock. REQUEST (V_lockhandle, Dbms_lock. X_mode, 0, FALSE);

if (v_lock_id = 0) Then

For check_org in Check_orgs loop

Null

End Loop;

v_release_id: = Dbms_lock.release (V_lockhandle);

COMMIT;

End If;

Commit

EXCEPTION

When OTHERS Then

v_release_id: = Dbms_lock.release (V_lockhandle);

    -- get the exception code

V_error_code: = SQLCODE;

    -- Get exception description information

V_error_message: = SQLERRM;

    -- maintaining exception information in the exception table

End Proc_data_check;

This article from "Ko Fai blog" blog, reproduced please contact the author!

Oracle Universal Stored Procedures