ORA-06502 from query that works in standalone SQL

Question

I am getting the ORA-06502 error while calling this query from PL/SQL. However if I try from the SQL prompt it is working.

From the SQL prompt this gives the expected output:

 SELECT *
  FROM
  (
   SELECT
       COL.BAN,
       MAX (COL.COL_ACTV_CODE) AS COL_ACTV_CODE,
       MAX (TO_CHAR(COL.COL_ACTV_DATE,'MM')) AS COL_ACTV_DATE
   FROM
       COLLECTION               COL,
       COLLECTION_ACTIVITIES    CAC
   WHERE (CAC.SEVERITY_LEVEL , TO_CHAR(COL.COL_ACTV_DATE,'YYYYMM')) IN
        (SELECT
               MAX(CAC.SEVERITY_LEVEL),
               MAX(TO_CHAR(COL.COL_ACTV_DATE, 'YYYYMM'))
          FROM
               COLLECTION               COL,
               COLLECTION_ACTIVITIES    CAC
          WHERE
               COL.COL_ACTV_CODE    = CAC.COL_ACTIVITY_CODE
          GROUP BY  TO_CHAR (COL.COL_ACTV_DATE , 'YYYYMM')
         )
    GROUP BY  COL.BAN
    ORDER BY  TO_CHAR (COL.COL_ACTV_DATE , 'YYYYMM') DESC
     )
     PIVOT
    (
       MAX( COL_ACTV_CODE)
     FOR COL_ACTV_DATE in ('01' as "JAN", '02' as "FEB", '03' as "MAR"));

       BAN J F M A M J J A S O N D
---------- - - - - - - - - - - - -
  90314228                 W
  90314009     K
  90314748                     E
  90314568                   E
  90314328                 W

But from PL/SQL:

BEGIN

STMT_STR := 'CREATE TABLE ACD_COL_ST
            PCTUSED 90
            NOLOGGING
            AS
 SELECT *
  FROM
  (
   SELECT
       COL.BAN,
       MAX (COL.COL_ACTV_CODE) AS COL_ACTV_CODE,
       MAX (TO_CHAR(COL.COL_ACTV_DATE,'MM')) AS COL_ACTV_DATE
   FROM
       COLLECTION               COL,
       COLLECTION_ACTIVITIES    CAC
   WHERE (CAC.SEVERITY_LEVEL , TO_CHAR(COL.COL_ACTV_DATE,'YYYYMM')) IN
        (SELECT
               MAX(CAC.SEVERITY_LEVEL),
               MAX(TO_CHAR(COL.COL_ACTV_DATE, 'YYYYMM'))
          FROM
               COLLECTION               COL,
               COLLECTION_ACTIVITIES    CAC
          WHERE
               COL.COL_ACTV_CODE    = CAC.COL_ACTIVITY_CODE
          GROUP BY  TO_CHAR (COL.COL_ACTV_DATE , 'YYYYMM')
         )
    GROUP BY  COL.BAN
    ORDER BY  TO_CHAR (COL.COL_ACTV_DATE , 'YYYYMM') DESC
     )
     PIVOT
    (
       MAX( COL_ACTV_CODE)
     FOR COL_ACTV_DATE in ('01' as "JAN", '02' as "FEB", '03' as "MAR"))';

EXECUTE IMMEDIATE STMT_STR;

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error was encountered creating table ACD_COL_ST'||SQLCODE||'  - ERROR - '||SQLERRM);
END;
COMMIT;

I get an error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Answer 1

I just check the code. Before we can answer can u plz tell me why you are creating table on the run time using Dynamic SQL. It is not possible to creat the table first and then INSERT into it using SIMPLER SQL than PLSQL. Anyhow i see in you code that there are single quotes only for strings. We need to use '' for ' in dynamic query. Please see below query i dont have workspace with me right now so cant validate the query. Pardon any syntactical error.

DECLARE
STMT_STR LONG;
BEGIN
STMT_STR := 
        'CREATE TABLE ACD_COL_ST                        
        AS 
        SELECT *  
        FROM  
        (   
        SELECT       
        COL.BAN,       
        MAX (COL.COL_ACTV_CODE) AS COL_ACTV_CODE,       
        MAX (TO_CHAR(COL.COL_ACTV_DATE,''MM'')) AS COL_ACTV_DATE   
        FROM       
        COLLECTION               COL,       
        COLLECTION_ACTIVITIES    CAC   
        WHERE (CAC.SEVERITY_LEVEL , TO_CHAR(COL.COL_ACTV_DATE,''YYYYMM'')) IN        
        (SELECT               
        MAX(CAC.SEVERITY_LEVEL),               
        MAX(TO_CHAR(COL.COL_ACTV_DATE, ''YYYYMM''))          
        FROM               
        COLLECTION               COL,               
        COLLECTION_ACTIVITIES    CAC          
        WHERE               
        COL.COL_ACTV_CODE    = CAC.COL_ACTIVITY_CODE          
        GROUP BY  TO_CHAR (COL.COL_ACTV_DATE , ''YYYYMM'')         
        )    
        GROUP BY  COL.BAN    
        ORDER BY  TO_CHAR (COL.COL_ACTV_DATE , ''YYYYMM'') DESC     
        )     
        PIVOT    
        (       
        MAX( COL_ACTV_CODE)     
        FOR COL_ACTV_DATE in (''01'' as JAN, ''02'' as FEB, ''03'' as MAR))';
  EXECUTE IMMEDIATE STMT_STR;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('An error was encountered creating table ACD_COL_ST'||SQLCODE||'  - ERROR - '||SQLERRM);
END;