4 Replies Latest reply: Mar 30, 2011 3:44 PM by 850097 RSS

    failed to enable table versioning due to "character string buffer too small

    850097
      When I tried to enable table versioning on 130 tables, it failed in the middle on the following error after enabling about 15 tables:

      BEGIN dbms_wm.enableversioning(' AFFINITIES, CHANNEL_USAGE_TYPE, COI_AVG_TRAFFIC_USAGE, COI_COI_RELA

      *
      ERROR at line 1:
      ORA-20171: WM error: ORA-20229: statement 'DECLARE
      err_num integer;
      err_msg varchar2(1000);
      prfx_len integer;
      BE' failed during EnableVersioning. Error:
      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at "SYS.WM_ERROR", line 342
      ORA-06512: at "SYS.WM_ERROR", line 359
      ORA-06512: at "SYS.LTDDL", line 1592
      ORA-06512: at "SYS.LTDDL", line 1140
      ORA-06512: at "SYS.LT", line 837
      ORA-06512: at "SYS.LT", line 9015
      ORA-06512: at line 1

      Our database version is 10.2.0.5.0, and Oracle Workspace Manager is also 10.2.0.5.0.

      How could we get around this?

      Any help is greatly appreciated!

      Thanks.
      Charles
        • 1. Re: failed to enable table versioning due to "character string buffer too small
          Ben Speckhard-Oracle
          Hi,

          I would recommend gathering the following trace:

          SQL> alter session set events '6502 trace name errorstack level 1, forever';

          From the trace you can find the exact line that the error is occurring on in the stack trace section, which will be beneficial in knowing exactly what is causing the error. If you can post that, I can take a closer look at it.

          Also, what character set is being used by your database?

          Regards,
          Ben
          • 2. Re: failed to enable table versioning due to "character string buffer too small
            850097
            Hi Ben,

            Thank you very much for looking into this.

            The trace file has the following for the failed statement:

            ksedmp: internal or fatal error
            ORA-06502: PL/SQL: numeric or value error: character string buffer too small
            Current SQL statement for this session:
            Declare
            schema varchar2(30) := 'VPI_NETDATA';
            tab_name varchar2(30) := 'NE_KPI_TRENDED';
            colname varchar2(40);
            colstr varchar2(32000);
            keycols varchar2(32000);

            -- for storing instead of trigger code implementing RIC checks
            insert_code clob;
            update_code clob;
            delete_code clob;
            -- user defined triggers related variables
            actual_str varchar2(32000);
            actual_str_io varchar2(32000);
            set_clause varchar2(32000);
            insertStr_io varchar2(32000);
            var_decls varchar2(32000);
            dispatcherName varchar2(100);
            bir boolean := false;
            air boolean := false;
            bur boolean := false;
            aur boolean := false;
            bdr boolean := false;
            adr boolean := false;
            olscols varchar2(10000) := wmsys.ltUtil.getHiddenOLSColumns(schema, tab_name || '_LT') ;
            -- cursor variables
            cursor colnam_cur is
            select column_name
            from dba_tab_columns dtc
            where dtc.table_name = tab_name || '_LT' and
            dtc.owner = schema and
            column_name not in ('VERSION','NEXTVER','DELSTATUS','LTLOCK') and
            column_name not like 'WM$%' and
            column_name not like 'WM@_%' escape '@'
            order by column_id;

            cursor keycol_cur is
            select dtc.column_name
            from dba_constraints dc, dba_cons_columns dcc, dba_tab_columns dtc
            where dc.owner = schema and
            dc.table_name = tab_name || '_LT' and
            dc.constraint_type = 'P' and
            dcc.owner = schema and
            dcc.table_name = tab_name || '_LT' and
            dc.constraint_name = dcc.constraint_name and
            dcc.column_name not in ('VERSION', 'LTLOCK', 'DELSTATUS', 'NEXTVER') and
            dcc.column_name not like 'WM$%' and
            dcc.column_name not like 'WM@_%' escape '@'
            and
            dtc.owner = schema and
            dtc.table_name = tab_name || '_LT' and
            dcc.column_name = dtc.column_name
            order by dtc.column_id;
            -- Versioned Table ID related variables
            vtid_var integer;
            hist_var varchar2(50);
            vt_option integer ;
            Begin
            open colnam_cur;
            fetch colnam_cur into colname;
            loop
            colstr := colstr || colname;
            fetch colnam_cur into colname;
            exit when colnam_cur%NOTFOUND;
            colstr := colstr || ',';
            end loop;
            close colnam_cur;
            open keycol_cur;
            fetch keycol_cur into colname;
            loop
            keycols := keycols || colname;
            fetch keycol_cur into colname;
            exit when keycol_cur%NOTFOUND;
            keycols := keycols || ',';
            end loop;
            close keycol_cur;
            colstr := colstr || ',';
            keycols := keycols || ',';

            if (olscols is not null) then
            colstr := colstr || olscols || ',' ;
            end if ;

            dbms_lob.createtemporary(insert_code, true, dbms_lob.call);
            dbms_lob.createtemporary(update_code, true, dbms_lob.call);
            dbms_lob.createtemporary(delete_code, true, dbms_lob.call);
            -- construct code for implementing RIC checks inside instead of triggers
            wmsys.ltric.getRICinsteadOfTrigStrs(schema, tab_name, insert_code,
            update_code, delete_code, 0 );
            select vtid, hist, validTime into vtid_var, hist_var, vt_option
            from wmsys.wm$versioned_tables
            where owner = schema and
            table_name = tab_name;
            wmsys.ud_trigs.getDispatcherInfo(schema, tab_name, dispatcherName, actual_str,
            actual_str_io, set_clause, insertStr_io, var_decls,
            bir, air, bur, aur, bdr, adr);
            wmsys.lt_ctx_pkg.allowDDLOperation('true') ;
            -- TOBEDONE: get enableNofication info from metadata tables
            wmsys.ltdtrg.CreateTriggers(schema, tab_name, colstr,
            keycols, false, insert_code, update_code, delete_code,
            vtid_var, dispatcherName, actual_str,
            actual_str_io, set_clause, insertStr_io, var_decls, /*set_clause, insertStr_io, var_decls, */
            bir, air, bur, aur, bdr, adr, hist_var, vt_option, isLocalSite=>'Y');
            wmsys.lt_ctx_pkg.allowDDLOperation('false') ;
            End;
            ----- PL/SQL Call Stack -----

            Our database is using these character sets:

            NLS_CHARACTERSET WE8ISO8859P1
            NLS_NCHAR_CHARACTERSET AL16UTF16

            If you need more information, please let me know.

            Thanks.
            Charles
            • 3. Re: failed to enable table versioning due to "character string buffer too small
              Ben Speckhard-Oracle
              Hi Charles,

              Looks like the trigger creation has failed. Is there anything special about the NE_KPI_TRENDED table? Does it have a significant number of foreign key relationships with other tables? If not, and you only specify that table, does it work?

              This type of error will typically require an updated package definition, so I would suggest to file a SR if possible.

              Regards,
              Ben
              • 4. Re: failed to enable table versioning due to "character string buffer too small
                850097
                Hi Ben,

                The table NE_KPI_TRENDED has 7 foreign key relationships with other tables. As you suggested, I tried to only specify this table but still failed

                SQL> exec dbms_wm.enableversioning('NE_KPI_TRENDED')
                BEGIN dbms_wm.enableversioning('NE_KPI_TRENDED'); END;

                *
                ERROR at line 1:
                ORA-20229: statement 'DECLARE
                err_num integer;
                err_msg varchar2(1000);
                prfx_len integer;
                BE' failed during EnableVersioning. Error:
                ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                ORA-06512: at "WMSYS.LT", line 9273
                ORA-06512: at line 1

                Thanks.
                Charles