alter session set current_schema=&&v_username.;

DECLARE

VCODE CLOB;

VVERS VARCHAR2(20) := '&&P_VERS.';

VSER VARCHAR2(10) := '&&P_SER.';

VPSU NUMBER := &&P_PSU.;

$IF DBMS_DB_VERSION.VER_LE_11

$THEN

CURSOR OBJS IS

SELECT OWNER,

NAME,

TYPE,

NULL ORIGIN_CON_ID,

NULL CON_ID,

LINE,

MAX(LINE) OVER (PARTITION BY OWNER, NAME, TYPE) LAST_LINE,

TEXT

FROM DBA_SOURCE

ORDER BY OWNER, NAME, TYPE, LINE ASC;

$ELSE

CURSOR OBJS IS

SELECT OWNER,

NAME,

TYPE,

ORIGIN_CON_ID,

CON_ID,

LINE,

MAX(LINE) OVER (PARTITION BY OWNER, NAME, TYPE, ORIGIN_CON_ID, CON_ID) LAST_LINE,

TEXT

FROM CDB_SOURCE WHERE ORIGIN_CON_ID=CON_ID -- AND CON_ID IN (1,2)

ORDER BY OWNER, NAME, TYPE, ORIGIN_CON_ID, CON_ID, LINE ASC;

$END

BEGIN

VCODE := ''; -- Zera a variável

FOR I IN OBJS

LOOP

VCODE := VCODE || I.TEXT;

IF I.LINE = I.LAST_LINE THEN

INSERT INTO T_HASH (OWNER, NAME, TYPE, ORIGIN_CON_ID, CON_ID, MD5_ENC, SHA1_ENC, SERIES, ORAVERSION, PSU, CODE)

VALUES

(I.OWNER, I.NAME, I.TYPE, I.ORIGIN_CON_ID, I.CON_ID, SYS.DBMS_CRYPTO.HASH(VCODE, SYS.DBMS_CRYPTO.HASH_MD5), SYS.DBMS_CRYPTO.HASH(VCODE, SYS.DBMS_CRYPTO.HASH_SH1), VSER, VVERS, VPSU, VCODE);

VCODE := ''; -- Zera a variável

END IF;

END LOOP;

END;

/