CASE statement inside PL/SQL / sql execeeds 4000 chars
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: CASE statement inside PL/SQL / sql execeeds 4000 chars

  1. #1
    Join Date
    Jul 2000
    Posts
    41

    Angry

    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


  2. #2
    Join Date
    Mar 2001
    Posts
    314
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width