Oracle Stored Procedure example

create or replace PROCEDURE ReadAuditItem(sRecordId IN VARCHAR2,

sLoginId IN VARCHAR2 DEFAULT NULL,

sFieldName IN VARCHAR2 DEFAULT NULL,

sNewValue IN VARCHAR2 DEFAULT NULL,

sOldValue IN VARCHAR2 DEFAULT NULL,              

sOperation IN VARCHAR2 DEFAULT NULL,

sResultSet OUT SYS_REFCURSOR

)

AS

new_value VARCHAR2(2000);

old_value VARCHAR2(2000);

column_list VARCHAR2(4000);

suser_id VARCHAR2(15);

slogin VARCHAR2(50);

CURSOR C1 IS

SELECT   s_audit_item.row_id

, s_audit_item.created

, s_audit_item.created_by

, s_audit_item.last_upd

, s_audit_item.last_upd_by

, s_audit_item.modification_num

, s_audit_item.conflict_id

, s_audit_item.buscomp_name

, s_audit_item.operation_cd

, s_audit_item.record_id

, s_audit_item.user_id

, s_audit_item.operation_dt

, s_audit_item.child_bc_name

, s_audit_item.field_name

, s_audit_item.new_val

, s_audit_item.old_val

, s_audit_item.audit_log

FROM LINX_PRD.s_audit_item

WHERE s_audit_item.record_id = sRecordId;

TYPE COLUMN_NAME_TYPE IS REF CURSOR;

column_name_cur COLUMN_NAME_TYPE;

column_name VARCHAR2(200);

field_name VARCHAR2(75);

BEGIN

FOR item IN C1

LOOP

SELECT login INTO slogin

FROM LINX_PRD.s_user WHERE row_id = item.user_id;

IF item.audit_log IS NULL

THEN

INSERT INTO xo_audit_item_t ( row_id

, created

, created_by

, last_upd

, last_upd_by

, modification_num

, conflict_id

, buscomp_name

, operation_cd

, record_id

, user_id

, operation_dt

, child_bc_name

, field_name

, new_val

, old_val

, user_login

)

VALUES (  item.row_id

, item.created

, item.created_by

, item.last_upd

, item.last_upd_by

, item.modification_num

, item.conflict_id

, item.buscomp_name

, item.operation_cd

, item.record_id

, item.user_id

, item.operation_dt

, item.child_bc_name

, item.field_name

, item.new_val

, item.old_val

, slogin

);

GOTO end_loop;

END IF;

GetColumnList(item.audit_log, column_list);

OPEN column_name_cur FOR 

SELECT EXPR

FROM (WITH T AS (SELECT '' || column_list || '' AS TXT FROM DUAL)

SELECT REGEXP_SUBSTR (TXT,

'[^|]+',

1,

LEVEL)

EXPR

FROM T

CONNECT BY LEVEL <=

LENGTH (REGEXP_REPLACE (TXT, '[^|]*')) + 1) X;

LOOP

FETCH column_name_cur into column_name;

EXIT WHEN column_name_cur%NOTFOUND;

FindAuditValues(item.audit_log, column_name, old_value, new_value);

SELECT  s_audit_field.field_name

INTO field_name

FROM LINX_PRD.s_audit_field ,

LINX_PRD.s_audit_buscomp

WHERE s_audit_buscomp.buscomp_name = item.buscomp_name

AND s_audit_field.audit_bc_id = s_audit_buscomp.row_id 

AND s_audit_field.col_name = column_name

AND ROWNUM < 2;

INSERT INTO xo_audit_item_t ( row_id

, created

, created_by

, last_upd

, last_upd_by

, modification_num

, conflict_id

, buscomp_name

, operation_cd

, record_id

, user_id

, operation_dt

, child_bc_name

, field_name

, new_val

, old_val

, user_login

)

VALUES ( item.row_id

, item.created

, item.created_by

, item.last_upd

, item.last_upd_by

, item.modification_num

, item.conflict_id

, item.buscomp_name

, item.operation_cd

, item.record_id

, item.user_id

, item.operation_dt

, item.child_bc_name

, field_name

, old_value

, new_value

, slogin);

END LOOP;

CLOSE column_name_cur;

<<end_loop>> NULL;

END LOOP;

IF sLoginId IS NOT NULL

THEN

SELECT row_id INTO suser_id

FROM LINX_PRD.s_user WHERE sLoginId IS NOT NULL  AND login = sLoginId;

END IF;

OPEN sResultSet FOR SELECT t.* FROM xo_audit_item_t t

WHERE (sFieldName IS NULL OR t.field_name = sFieldName)

AND (sOldValue IS NULL OR t.old_val = sOldValue)

AND (sNewValue IS NULL OR t.new_val = sNewValue)

AND (sOperation IS NULL OR t.operation_cd = sOperation)

AND (suser_id IS NULL OR t.user_id = suser_id)

;

DELETE FROM xo_audit_item_t;

END;