CASE statement inside PL/SQL / sql execeeds 4000 chars

[cdutta]

Hi,

I am trying to execute an insert into table statement inside a stored procedure. The problem is that the statement contains a CASE statement which cannot be executed inside a pl/sql block. The work around is to use execute immediate 'Sql statement' ; The problem is that the SQL statement is so big it cannot fit into a v_string varchar2(4000). I did split
the sql into 5 variables v_string1, v_string2...v_string5. and then tried to execute
execute immediate v_string1||v_string2.... ||v_string5;

the procedure compiles ok but when I execute it

I get this error
ORA-06502: PL/SQL: numeric or value error: character to number conversion error.


The body of the stored procedure:

CREATE OR REPLACE PROCEDURE SP_COLLECT_TBLCIF_STAGE2
as
v_strstmt1 varchar2(4000);
v_strstmt2 varchar2(4000);
v_strstmt3 varchar2(4000);
v_strstmt4 varchar2(4000);
v_strstmt5 varchar2(4000);
BEGIN
Execute immediate 'TRUNCATE TABLE STAGE.TBLCIF_STAGE2';
/* Execute immediate 'DROP TABLE STAGE.TBLCIF_STAGE2_NEW'; */
v_strstmt1 :=
'INSERT INTO STAGE.TBLCIF_STAGE2_NEW AS'||
'select /*+ PARALLEL(STAGE.TBLCIF_STAGE1)*/'||
'CIF_ACCOUNT_NUMBER ,'||
'CIF_CHECKING_ACCT_NO ,'||
'CIF_SAVINGS_ACCT_NO ,'||
'CASE When substr(trim(CIF_SOCIAL_SECURITY) , Length(trim(CIF_SOCIAL_SECURITY)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then '||
'substr(trim(CIF_SOCIAL_SECURITY) , 1, Length(trim(CIF_SOCIAL_SECURITY ))-1)||DECODE(substr(trim(CIF_SOCIAL_SECURITY) , Length(trim(CIF_SOCIAL_SECURITY)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_SOCIAL_SECURITY), 1, Length(trim(CIF_SOCIAL_SECURITY))-1) || DECODE(substr(trim(CIF_SOCIAL_SECURITY), Length(trim(CIF_SOCIAL_SECURITY)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
' end AS CIF_SOCIAL_SECURITY,'||
'CIF_PRINCIPAL_NAME ,'||
'CIF_SEX_CODE ,'||
'CIF_SPOUSE_NAME ,'||
'CIF_ADDR_LINE_1 ,'||
'CIF_ADDR_LINE_2 ,'||
'CIF_CITY ,'||
'CIF_STATE ,'||
'SUBSTR(CIF_ZIP ,1,LENGTH(TRIM(CIF_ZIP))-1) AS CIF_ZIP ,'||
'CIF_CYCLE_CODE ,'||
'CIF_EXTERNAL_STATUS ,'||
'CIF_INTERNAL_STATUS ,'||
'CASE When substr(trim(CIF_NUMBER_CARDS), Length(trim(CIF_NUMBER_CARDS)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_NUMBER_CARDS), 1, Length(trim(CIF_NUMBER_CARDS))-1)||DECODE(substr(trim(CIF_NUMBER_CARDS) , Length(trim(CIF_NUMBER_CARDS)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_NUMBER_CARDS), 1, Length(trim(CIF_NUMBER_CARDS))-1) || DECODE(substr(trim(CIF_NUMBER_CARDS), Length(trim(CIF_NUMBER_CARDS)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
' end as CIF_NUMBER_CARDS'||
'CIF_TYPE_CARD ,'||
'CIF_OPEN_DATE ,'||
'CIF_EXPIRATION_DATE ,'||
'CIF_RENEWAL_CODE ,'||
'CASE When substr(trim(CIF_CREDIT_LINE) , Length(trim(CIF_CREDIT_LINE)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
' substr(trim(CIF_CREDIT_LINE) , 1, Length(trim(CIF_CREDIT_LINE))-1)||DECODE(substr(trim(CIF_CREDIT_LINE), Length(trim(CIF_CREDIT_LINE)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
' '-'||substr(trim(CIF_CREDIT_LINE), 1, Length(trim(CIF_CREDIT_LINE))-1) || DECODE(substr(trim(CIF_CREDIT_LINE), Length(trim(CIF_CREDIT_LINE)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
' end AS CIF_CREDIT_LINE,'||
'CASE When substr(trim(CIF_BIRTH_DATE) , Length(trim(CIF_BIRTH_DATE)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
' substr(trim(CIF_BIRTH_DATE), 1, Length(trim(CIF_BIRTH_DATE))-1)||DECODE(substr(trim(CIF_BIRTH_DATE), Length(trim(CIF_BIRTH_DATE)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
' else'||
' '-'||substr(trim(CIF_BIRTH_DATE), 1, Length(trim(CIF_BIRTH_DATE))-1) || DECODE(substr(trim(CIF_BIRTH_DATE), Length(trim(CIF_BIRTH_DATE)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_BIRTH_DATE,'||

'CIF_HOME_PHONE ,'||
'CIF_DUALITY_FLAG ,'||
'CIF_AUTH_FLAG ,'||
'CIF_CREDIT_LIFE_FLAG ,'||
'CIF_CHARGE_DDA_CODE ,'||
'CIF_RECOURSE_FLAG ,';

v_strstmt2 :='CASE When substr(trim(CIF_RECOURSE_MERCHANT), Length(trim(CIF_RECOURSE_MERCHANT)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
' substr(trim(CIF_RECOURSE_MERCHANT) , 1, Length(trim(CIF_RECOURSE_MERCHANT))-1)||DECODE(substr(trim(CIF_RECOURSE_MERCHANT), Length(trim(CIF_RECOURSE_MERCHANT)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_RECOURSE_MERCHANT), 1, Length(trim(CIF_RECOURSE_MERCHANT))-1) || DECODE(substr(trim(CIF_RECOURSE_MERCHANT), Length(trim(CIF_RECOURSE_MERCHANT)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
' end as CIF_RECOURSE_MERCHANT,'||
'CIF_SPECIAL_CODE_1 ,'||
'CIF_SPECIAL_CODE_2 ,'||
'CIF_SPECIAL_CODE_3 ,'||
'CIF_SPECIAL_CODE_4 ,'||
'CIF_SPECIAL_CODE_5 ,'||
'CIF_SPECIAL_CODE_6 ,'||
'CIF_SPECIAL_CODE_7 ,'||
'CIF_SPECIAL_CODE_8 ,'||
'CASE When substr(trim(CIF_PAY_AHEAD_FLAG), Length(trim(CIF_PAY_AHEAD_FLAG)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_PAY_AHEAD_FLAG), 1, Length(trim(CIF_PAY_AHEAD_FLAG))-1)||DECODE(substr(trim(CIF_PAY_AHEAD_FLAG), Length(trim(CIF_PAY_AHEAD_FLAG)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_PAY_AHEAD_FLAG), 1, Length(trim(CIF_PAY_AHEAD_FLAG))-1) || DECODE(substr(trim(CIF_PAY_AHEAD_FLAG), Length(trim(CIF_PAY_AHEAD_FLAG)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_PAY_AHEAD_FLAG,'||
'CIF_NEWCARD_FLAG ,'||
'CIF_CR_BUREAU_FLAG ,'||
'CIF_XREF_ACCT_NUMBER ,'||
'CIF_SEC_PHONE ,'||
'CIF_MISC_FIELD_1 ,'||
'CIF_MISC_FIELD_2 ,'||
'CIF_MISC_FIELD_3,'||
'CASE When substr(trim(CIF_NMO_ACTIVE) , Length(trim(CIF_NMO_ACTIVE)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_NMO_ACTIVE) , 1, Length(trim(CIF_NMO_ACTIVE))-1)||DECODE(substr(trim(CIF_NMO_ACTIVE) , Length(trim(CIF_NMO_ACTIVE)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_NMO_ACTIVE), 1, Length(trim(CIF_NMO_ACTIVE))-1) || DECODE(substr(trim(CIF_NMO_ACTIVE) , Length(trim(CIF_NMO_ACTIVE)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_NMO_ACTIVE ,';

v_strstmt3 :='CASE When substr(trim(CIF_NMO_DELQ) , Length(trim(CIF_NMO_DELQ)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_NMO_DELQ) , 1, Length(trim(CIF_NMO_DELQ))-1)||DECODE(substr(trim(CIF_NMO_DELQ), Length(trim(CIF_NMO_DELQ)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_NMO_DELQ) , 1, Length(trim(CIF_NMO_DELQ))-1) || DECODE(substr(trim(CIF_NMO_DELQ), Length(trim(CIF_NMO_DELQ)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_NMO_DELQ ,'||
'CASE When substr(trim(CIF_NMO_OVRLIMT), Length(trim(CIF_NMO_OVRLIMT)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_NMO_OVRLIMT), 1, Length(trim(CIF_NMO_OVRLIMT))-1)||DECODE(substr(trim(CIF_NMO_OVRLIMT), Length(trim(CIF_NMO_OVRLIMT)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_NMO_OVRLIMT), 1, Length(trim(CIF_NMO_OVRLIMT))-1) || DECODE(substr(trim(CIF_NMO_OVRLIMT), Length(trim(CIF_NMO_OVRLIMT)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_NMO_OVRLIMT,'||
'CASE When substr(trim(CIF_DATE_LAST_STMT), Length(trim(CIF_DATE_LAST_STMT)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_DATE_LAST_STMT), 1, Length(trim(CIF_DATE_LAST_STMT))-1)||DECODE(substr(trim(CIF_DATE_LAST_STMT), Length(trim(CIF_DATE_LAST_STMT)),1),''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F' ',''6'',''G'',''7'',''H'',''8'',''I'',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_DATE_LAST_STMT), 1, Length(trim(CIF_DATE_LAST_STMT))-1) || DECODE(substr(trim(CIF_DATE_LAST_STMT), Length(trim(CIF_DATE_LAST_STMT)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_DATE_LAST_STMT ,'||
'CASE When substr(trim(CIF_DATE_LAST_PYMT), Length(trim(CIF_DATE_LAST_PYMT)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_DATE_LAST_PYMT), 1, Length(trim(CIF_DATE_LAST_PYMT))-1)||DECODE(substr(trim(CIF_DATE_LAST_PYMT), Length(trim(CIF_DATE_LAST_PYMT)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_DATE_LAST_PYMT), 1, Length(trim(CIF_DATE_LAST_PYMT))-1) || DECODE(substr(trim(CIF_DATE_LAST_PYMT), Length(trim(CIF_DATE_LAST_PYMT)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_DATE_LAST_PYMT ,'||
'CASE When substr(trim(CIF_CURRENT_BALANCE), Length(trim(CIF_CURRENT_BALANCE)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_CURRENT_BALANCE), 1, Length(trim(CIF_CURRENT_BALANCE))-1)||DECODE(substr(trim(CIF_CURRENT_BALANCE), Length(trim(CIF_CURRENT_BALANCE)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_CURRENT_BALANCE), 1, Length(trim(CIF_CURRENT_BALANCE))-1) || DECODE(substr(trim(CIF_CURRENT_BALANCE), Length(trim(CIF_CURRENT_BALANCE)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_CURRENT_BALANCE,'||
'CASE When substr(trim(CIF_PAY_DUE), Length(trim(CIF_PAY_DUE)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_PAY_DUE), 1, Length(trim(CIF_PAY_DUE))-1)||DECODE(substr(trim(CIF_PAY_DUE), Length(trim(CIF_PAY_DUE)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_PAY_DUE), 1, Length(trim(CIF_PAY_DUE))-1) || DECODE(substr(trim(CIF_PAY_DUE), Length(trim(CIF_PAY_DUE)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
' end as CIF_PAY_DUE,'||
'CASE When substr(trim(CIF_LAST_STMT_BAL), Length(trim(CIF_LAST_STMT_BAL)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_LAST_STMT_BAL), 1, Length(trim(CIF_LAST_STMT_BAL))-1)||DECODE(substr(trim(CIF_LAST_STMT_BAL), Length(trim(CIF_LAST_STMT_BAL)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
' '-'||substr(trim(CIF_LAST_STMT_BAL), 1, Length(trim(CIF_LAST_STMT_BAL))-1) || DECODE(substr(trim(CIF_LAST_STMT_BAL), Length(trim(CIF_LAST_STMT_BAL)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_LAST_STMT_BAL ,';

v_strstmt4 := 'CASE When substr(trim(CIF_CURR_AMT_DELQ), Length(trim(CIF_CURR_AMT_DELQ)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_CURR_AMT_DELQ), 1, Length(trim(CIF_CURR_AMT_DELQ))-1)||DECODE(substr(trim(CIF_CURR_AMT_DELQ), Length(trim(CIF_CURR_AMT_DELQ)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_CURR_AMT_DELQ), 1, Length(trim(CIF_CURR_AMT_DELQ))-1) || DECODE(substr(trim(CIF_CURR_AMT_DELQ), Length(trim(CIF_CURR_AMT_DELQ)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_CURR_AMT_DELQ ,'||
'CASE When substr(trim(CIF_DAYS_DELQ), Length(trim(CIF_DAYS_DELQ)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
' substr(trim(CIF_DAYS_DELQ), 1, Length(trim(CIF_DAYS_DELQ))-1)||DECODE(substr(trim(CIF_DAYS_DELQ), Length(trim(CIF_DAYS_DELQ)),1),''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6 '',''G'',''7'',''H'',''8'',''I'',''9'',''{'',''0'')'||
'else'||
' '-'||substr(trim(CIF_DAYS_DELQ), 1, Length(trim(CIF_DAYS_DELQ))-1) || DECODE(substr(trim(CIF_DAYS_DELQ), Length(trim(CIF_DAYS_DELQ)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_DAYS_DELQ ,'||

'CASE When substr(trim(CIF_NMO_CONT_DELQ), Length(trim(CIF_NMO_CONT_DELQ)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_NMO_CONT_DELQ), 1, Length(trim(CIF_NMO_CONT_DELQ))-1)||DECODE(substr(trim(CIF_NMO_CONT_DELQ), Length(trim(CIF_NMO_CONT_DELQ)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_NMO_CONT_DELQ), 1, Length(trim(CIF_NMO_CONT_DELQ))-1) || DECODE(substr(trim(CIF_NMO_CONT_DELQ), Length(trim(CIF_NMO_CONT_DELQ)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_NMO_CONT_DELQ ,'||
'CIF_NEW_CREDIT_SCORE ,'||
'CIF_ANNUAL_CHRG_FLAG ,'||
'CIF_CTD_TERMS_FLAG ,'||
'CIF_LS_TERMS_FLAG ,'||
'CASE When substr(trim(CIF_BEHAVIOR_SCORE), Length(trim(CIF_BEHAVIOR_SCORE)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_BEHAVIOR_SCORE), 1, Length(trim(CIF_BEHAVIOR_SCORE))-1)||DECODE(substr(trim(CIF_BEHAVIOR_SCORE), Length(trim(CIF_BEHAVIOR_SCORE)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
''-'||substr(trim(CIF_BEHAVIOR_SCORE), 1, Length(trim(CIF_BEHAVIOR_SCORE))-1) || DECODE(substr(trim(CIF_BEHAVIOR_SCORE), Length(trim(CIF_BEHAVIOR_SCORE)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_BEHAVIOR_SCORE ,'||
'CIF_DATE_STAT_CHG ,';
v_strstmt5 :=
'CASE When substr(trim(CIF_HOME_PHONE_FORMAT), Length(trim(CIF_HOME_PHONE_FORMAT)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
'substr(trim(CIF_HOME_PHONE_FORMAT), 1, Length(trim(CIF_HOME_PHONE_FORMAT))-1)||DECODE(substr(trim(CIF_HOME_PHONE_FORMAT), Length(trim(CIF_HOME_PHONE_FORMAT)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
' '-'||substr(trim(CIF_HOME_PHONE_FORMAT), 1, Length(trim(CIF_HOME_PHONE_FORMAT))-1) || DECODE(substr(trim(CIF_HOME_PHONE_FORMAT), Length(trim(CIF_HOME_PHONE_FORMAT)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_HOME_PHONE_FORMAT,'||
'CASE When substr(trim(CIF_BUS_PHONE_FORMAT), Length(trim(CIF_BUS_PHONE_FORMAT)), 1) in ( ''{'' ,''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'') then'||
' substr(trim(CIF_BUS_PHONE_FORMAT), 1, Length(trim(CIF_BUS_PHONE_FORMAT))-1)||DECODE(substr(trim(CIF_BUS_PHONE_FORMAT), Length(trim(CIF_BUS_PHONE_FORMAT)),1), ''A'',''1'',''B'',''2'',''C'',''3'',''D'',''4'',''E'',''5'',''F'',''6'',''G'',''7'',''H'',''8'',''I' ',''9'',''{'',''0'')'||
'else'||
' '-'||substr(trim(CIF_BUS_PHONE_FORMAT), 1, Length(trim(CIF_BUS_PHONE_FORMAT))-1) || DECODE(substr(trim(CIF_BUS_PHONE_FORMAT), Length(trim(CIF_BUS_PHONE_FORMAT)),1), ''J'',''1'',''K'',''2'',''L'',''3'',''M'',''4'',''N'',''5'',''O'',''6'',''P'',''7'',''R'',''8'',''Q' ',''9'',''}'',''0'')'||
'end as CIF_BUS_PHONE_FORMAT,'||
'CIF_FILLER_VALUE_SPACES'||
'from STAGE.TBLCIF_STAGE1';
execute immediate v_strstmt1||v_strstmt2||v_strstmt3||v_strstmt4||v_strstmt5;

exception
when value_error then
dbms_output.put_line ('VALUE_ERROR');
END;

everything boils down to two questions:

1. How can I include a CASE in a pl/sql block?
2. What datatype should I use to store the entire SQL statement that needs to be executed by execute immediate?

Thanks,
CD

[amar]

You are not limited to 4000 bytes for varchar2 in pl/sql. Use a defn like:

v_strstmt varchar2(32767);

32767 is the max dim you can have in pl/sql.


I am not sure whether the big statement will be executed by "execute immediate". Please post your results here

-amar