6 Replies Latest reply: Apr 3, 2009 3:51 AM by 9554 RSS

    error creating 'Journalling Business Rules'


      When running 'Maintain Journalling Business Rules' for a table with 19 columns an EMPTY 'BR_xxx_JRN_DEL' trigger is created , hence the CAPI definition fails.

      If I add 1 more column to make it 20 columns the 'BR_xxx_JRN_DEL' and the CAPI are both generating correctly.

      Has anyone else found this ?????
        • 1. re:error creating 'Journalling Business Rules'
          We have not heard of anyone else experiencing this same problem.
          Does the error occur when there are 18 columns?
          Could there be something different about the table definition besides the fact that it has 19 columns?
          Are you able to reproduce this error with other tables that have 19 columns (with different datatypes, etc.)
          I suggest you run the utility with the Headstart debug monitor and set the level to DEBUG DETAILED. See if you can
          trace to a more specific cause of the error.
          • 2. re:error creating 'Journalling Business Rules'
            David gave me the following extra information:

            The scenario is:

            We have a table with 28 columns I try and boost the Journally BR's (<JN>Journal</JN> in NOTES) ,
            the following is an extract of the debug code for the DEL rule failure.

            Write Text for element type PLM with id 830266772012969583230946129301974025
            - Text type: CDIPLS number of text lines = 16 - REPLACE
            Deleted old text
            Exception: ORA-01401: inserted value too large for column
            Length Buildstring 2048
            Line number 2 Text type = CDIPLS
            Exception; Note that the text for this element may have been corrupted.

            Analysis: now that we have the exact Oracle error (inserted value too large for column), I think I know what happened.
            To understand what went wrong you have to understand how Designer stores multiline text properties.
            Even if your multiline text (in this case, the PL/SQL Block) contains multiple short lines,
            Designer stores it in one VARCHAR2 column of length 2000, with newline characters marking the line breaks.
            If the multiline text takes up more than 2000 characters, the next piece is stored in a second row.

            If a Headstart Utility wants to write multiline text which is more than 2000 characters,
            Headstart's bltext package tries to divide the text into suitable chunks of no more than 2000 characters.
            In your case this went wrong, it created a chunk of 2048 characters.
            It tries to prevent this with the following test:
            if length(l_text_lines(l_ctr + 1))+ 2 + length(l_build_string) > 1990
            What this means is, will the next line of the PL/SQL Block (l_text_lines(l_ctr+1)) increase the length
            of the current chunk (l_build_string) with so much that it would become larger than 1990 characters?
            So, this should prevent any build strings of length 2048. Still, it happened in your case.

            My advice: dive into the source of the bltext package (you can find it in [Headstart home]\hsu\scripts\bl\bltext.pkb)
            go to the procedure write_lines_to_repository, and add more debug info to find out what is wrong.
            You can add debug messages with the statement
            bllog.write(<your text>, BLLOG.DEBUG);
            for example:
            bllog.write('contents of next line to be processed: '||l_text_lines(l_ctr + 1), BLLOG.DEBUG);
            Then recreate the package in the schema of the Headstart Utilities owner (usually hsu65)
            and restart the Utilities Run Form. Try to find out what exactly is the next line it tries to add,
            why does the if-statement mentioned above not prevent the buildstring of length 2048?

            Hope this helps,
            Sandra Muller
            • 3. re:error creating 'Journalling Business Rules'
              Another client ran into the same problem, and performed the debugging as described above.
              We found the solution!

              Modify package bltext (in schema of Utilities owner, e.g. HSU65),
              in procedure write_lines_to_repository,


              if length(l_text_lines(l_ctr + 1))+ 2 + length(l_build_string) > 1990


              if length(replace(l_text_lines(l_ctr + 1), chr(10), NL_CR)||NL_CR) + length(l_build_string) > 2000

              And also replace

              if nvl(length(l_build_string),'') > 0


              if nvl(length(l_build_string),0) > 0

              This should solve the problem. I logged it as bug number 2396132.

              kind regards,
              • 4. re:error creating 'Journalling Business Rules'
                We are not huge fans of changing Oracle supplied code , only to have it lost in the next release. Will the fix be included in your the next release of headstart ??? If so, as we have a work around for the problem, we would prefer to wait for the release.
                • 5. Re: re:error creating 'Journalling Business Rules'
                  Omar M Sawalhah
                  Ok we have the modified version of hsd and we still have similar problem. Here is the detailed log
                  Log Messages

                  Utility      Maintain Journalling Business Rules
                  Prompt      Value
                  Table to be Journalled      app/ my table


                  Activity aborted with ORACLE internal errors.
                  -6502 ORA-06502: PL/SQL: numeric or value error: character string buffer too small

                  Debug Detailed

                  Number of Tables to create/delete Journalling Rules for: 1
                  Business Rule Design Definition for BR_xxx_JRN_UPD
                  No Update Trigger Columns specified. Business Rule BR_xxx_JRN_UPD will fire when any column is updated.
                  end of initial_checks
                  Create/maintain message with constraint_name = HRS_BR_xxx_JRN_UPD
                  Message QMS-00151 already exists in Headstart message tables, no new message was created.
                  Activity aborted with ORACLE internal errors.
                  -6502 ORA-06502: PL/SQL: numeric or value error: character string buffer too small

                  and this is the piece of code from bltext

                  -- changed if condition
                  -- if length(l_text_lines(l_ctr + 1))+ 2 + length(l_build_string) &gt; 1990
                  if length(replace(l_text_lines(l_ctr + 1), chr(10), NL_CR)||NL_CR)
                  length(l_build_string) &gt; 2000

                  end if;
                  end loop; -- compose 1 build string

                  -- changed if condition
                  -- if nvl(length(l_build_string),'') &gt; 0
                  if nvl(length(l_build_string),0) &gt; 0
                  -- column element_type_for added.
                  insert into cdi_text
                  ( txt_ref
                  , txt_seq
                  , txt_type
                  , txt_text
                  , element_type_for
                  ( p_el_id
                  , l_insert_at_seq
                  , p_text_type
                  , l_build_string
                  , p_el_type
                  end if; -- nvl(length(l_build_string),'') &gt; 0
                  if l_ctr &gt;= p_text_counter
                  l_build_string:= '';
                  l_insert_at_seq:= l_insert_at_seq + 1;
                  end if; -- l_ctr = p_text_counter
                  end loop; -- (maximum) over all lines in local text-store
                  end if; -- l_lock
                  when others
                  ('Exception: '||sqlerrm
                  , bllog.debug
                  (' Length Buildstring '||to_char(length(l_build_string))
                  , bllog.debug
                  (' Line number '||to_char(l_insert_at_seq)||' Text type = '||p_text_type
                  , bllog.debug
                  ( 'Exception; Note that the text for this element may have been corrupted.'
                  , bllog.errors
                  end write_lines_to_repository;
                  • 6. Re: re:error creating 'Journalling Business Rules'
                    We also had this problem in the past.
                    Besides the change in the bltext package, we also made a change in hsu_crbr, procedure add:

                    procedure add
                    ( p_text_line in varchar2
                    -- Purpose adds a single text string to the next available line in
                    -- pl/sql table g_text
                    -- if the text line is longer than 2000, split it at a chr(10)
                    l_text_line varchar2(30000);
                    l_nl_pos number(5);
                    --   l_text_line := p_text_line;
                    l_text_line := replace(p_text_line,chr(10),chr(13)||chr(10));