SPOOL TRIM_SRCH_CHAR_VAL.sql

SET SERVEROUTPUT ON;
SET FEEDBACK OFF;

DECLARE

v_tbl_name varchar2(30);
v_fld_name varchar2(30);
v_key varchar2(30);
v_precision NUMBER(20,0);

cursor cursor_1 is
WITH A12 AS
(SELECT DISTINCT A.TBL_NAME MAIN1 ,B.TBL_NAME CHAR1,B1.KEY_TYPE_FLG FROM CI_MD_MO_TBL A, CI_MD_MO_TBL B, CI_MD_TBL B1
WHERE TRIM(B.TBL_NAME) IN (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'SRCH_CHAR_VAL'
AND DATA_TYPE = 'VARCHAR2')
AND A.MAINT_OBJ_CD = B.MAINT_OBJ_CD
AND A.TBL_NAME = B1.TBL_NAME
AND A.TBL_ROLE_FLG = 'PRIM')
SELECT F.CHAR1,CASE CHAR1 WHEN 'SC_USER_CHAR' THEN 'USR' ELSE DECODE(SUBSTR(TRIM(D.FLD_NAME),-2,2),'ID','RNDM','USR') END KEY,D.FLD_NAME,FLD_PRECISION
FROM CI_MD_CONST C, CI_MD_CONST_FLD D, CI_MD_FLD E, A12 F
WHERE C.CONST_ID = D.CONST_ID
AND C.TBL_NAME = F.MAIN1
AND D.FLD_NAME = E.FLD_NAME
AND C.CONST_TYPE_FLG = 'PK'
AND D.SEQ_NUM = 1;


BEGIN


open cursor_1;

  loop
    fetch cursor_1 into v_tbl_name,v_key,v_fld_name,v_precision;
    exit when cursor_1%notfound;

  if ( v_key = 'RNDM') THEN

    FOR A1 IN 0..9 LOOP

DBMS_OUTPUT.PUT_LINE('UPDATE '||v_tbl_name||' A SET A.SRCH_CHAR_VAL = TRIM(A.SRCH_CHAR_VAL) WHERE A.'||v_fld_name||' BETWEEN ''' || RPAD(A1,v_precision,0) || ''' AND ''' || RPAD(A1,v_precision,9) ||''' AND A.CHAR_TYPE_CD = ( SELECT B.CHAR_TYPE_CD FROM CI_CHAR_TYPE B WHERE A.CHAR_TYPE_CD = B.CHAR_TYPE_CD AND (B.CHAR_TYPE_FLG = ''DFV'' OR ( B.CHAR_TYPE_FLG = ''FKV'' AND B.FK_REF_CD IN (SELECT T1.FK_REF_CD FROM CI_FK_REF T1, CI_MD_TBL T2 WHERE T1.TBL_NAME = T2.TBL_NAME AND T1.FK_REF_CD = B.FK_REF_CD AND T2.KEY_TYPE_FLG = ''USR'') ) )) AND TRIM(A.SRCH_CHAR_VAL) IS NOT NULL;');

DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10));
   END LOOP;

  else

DBMS_OUTPUT.PUT_LINE('UPDATE '||v_tbl_name||' A SET A.SRCH_CHAR_VAL = TRIM(A.SRCH_CHAR_VAL) WHERE A.CHAR_TYPE_CD = ( SELECT B.CHAR_TYPE_CD FROM CI_CHAR_TYPE B WHERE A.CHAR_TYPE_CD = B.CHAR_TYPE_CD AND (B.CHAR_TYPE_FLG = ''DFV'' OR ( B.CHAR_TYPE_FLG = ''FKV'' AND B.FK_REF_CD IN (SELECT T1.FK_REF_CD FROM CI_FK_REF T1, CI_MD_TBL T2 WHERE T1.TBL_NAME = T2.TBL_NAME AND T1.FK_REF_CD = B.FK_REF_CD AND T2.KEY_TYPE_FLG = ''USR'') ) )) AND TRIM(A.SRCH_CHAR_VAL) IS NOT NULL;');

DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10));

 END IF;

END LOOP;

close cursor_1;

DBMS_OUTPUT.PUT_LINE('EXIT;');

end;
/

spool off;

exit;