1 2 Previous Next 20 Replies Latest reply: Mar 2, 2006 2:31 PM by 495186 RSS

    Runtime Error

    487104
      Hi All,

      I got this error while executing a mapping:

      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at "SU_OWB_RUNREPOWN.WB_RT_MAPAUDIT", line 1762
      ORA-06512: at "SU_OWB_RUNREPOWN.WB_RT_MAPAUDIT", line 2651
      ORA-06512: at "ALOK.M_TEST", line 732
      ORA-01722: invalid number
      ORA-02063: preceding line from TESTMIG2@CONN_TGT_TEAR_SRC
      ORA-06512: at "ALOK.M_TEST", line 1028
      ORA-06512: at "ALOK.M_TEST", line 2622
      ORA-06512: at line 1

      Can anyone let me know the source of this error?

      regds
      -AP
        • 1. Re: Runtime Error
          99896
          Hello,

          Errors like yours often sourced in incompatibles within your mapping.
          For example, source object has field of type VARCHAR2(100) and you mapping it to the target object’s field of type VARCHAR2(50). In this case it’s possible that in source data long string will appear and it wouldn’t fit into target. In that case during Row based operation you will get error like “String buffer too small”, because the data go through local PLSQL variables.
          Implicit data conversion may lead to similar type of errors: if you map VARCHAR field from the source to the NUMERIC target field assuming all strings within the source represent numbers, you may catch an error “invalid number”.

          The solution is simple: perform mapping validation and respect its result. If you ignore validation warnings you should be ready for errors in runtime.

          Sincerely,
          Sergey
          • 2. Re: Runtime Error
            487104
            Thanks Sergey,
            The problem is not that simple. I was checking column by column. Suppose my target has n no of columns. When I start mapping the first two columns, the mapping goes of fine. I gradually keep increasing the mapped fields one by one and execute at each step to see which field was causing the problem. And I find that for one particular field the execution has this error. This field was a prefect match in length. The mapping validates without any warnings/errors.
            This might be probably because Oracle has some string buffers that keeps some concatenated values of all the string variable in a mapping and somehow it is causing the problem ...
            I am still looking for an answer that solves this annoying problem.


            regards
            -AP
            • 3. Re: Runtime Error
              99896
              Hello, AP

              What is estimated length of row (in bytes) of the rowset that goes to the target (regarding your step-by-step exercise)? What is DB_BLOCK_SIZE of the target database? Are there some aggregations or mapping is just bulk move from source to target?

              I remember I experienced the similar problem trying to move rows which width was close or excide to DB_BLOCK_SIZE, but I had mapping like “pivot” that involve heavy grouping / aggregation for a very width survey form. There is a known restriction regarding it.
              Please check the details above.

              Sergey
              • 4. Re: Runtime Error
                487104
                Thanks Sergey for the information.
                But this is the information what I have been able to collect regarding the target database.
                The BLOCK_SIZE (in table dba_tablespaces) parameter has size of 8192 bytes. My each record that is getting loaded has a total length of around VARCHAR2(800) which is 800 bytes. There are some 59000 records in total in source tables.

                The mapping is a bulk move from source to target with some concatenations for a few source fields.

                I dont think I am exceeding the BLOCK_SIZE parameter in the target database, Am I?

                regards
                -AP
                • 5. Re: Runtime Error
                  99896
                  Ok. Have you looked through mapping code? The error message you’ve posted at the first message pointing to exact rows of source code. So, what operator cause exception to be raised?

                  Sergey
                  • 6. Re: Runtime Error
                    487104
                    Hi Sergey,

                    As the error message in my first post said, there was some string buffer error in the package WB_RT_MAPAUDIT which is in the OWB Runtime Repository.

                    We can hardly go and do anything on this package. The hands are tied it seems.

                    The line numbers of the generated code as mentioned in the error message does not give any meaningful infos.

                    I have done concatenations more that what is being used here ( one column of varchar2(100) is getting concatenated with another column of varchar2(200) and there are some one to one mappings of varchar2 columns from source to target), but it is very strange that the error is occuring only for this mapping.

                    regards
                    -AP
                    • 7. Re: Runtime Error
                      99896
                      Hello, AP

                      I didn’t mean you to dig OWB code – exception correspond to piece of your code (ALOK.M_TEST) also. So the question was what operation in your code correspond to the error. It would be call to audit facility to report the error during row loading, I guess.

                      Anyway, I think I have one version of that happening.
                      During your mapping (which is executed in some of “Row based” modes or “Set-based failover Row-based”) error occur – “invalid number”. OWB log facility attempt to make log record. When Running in Row base mode OWB track values of fields for the record that failed to load; so, trying to log your record, OWB cracks with your long-concatenation-product-string – and raised an exception "string buffer to small" from the deep (SU_OWB_RUNREPOWN.WB_RT_MAPAUDIT).
                      Hence table column for the field value in audit table is VARCHAR2(4000) width, I suspect temp variable within code is shorter – OWB bug maybe?

                      Now, the check scenario: Try to run your mapping in Set base mode (no failover). Observe the results – I expect failure with “Invalid number” Exception in the audit trail, but the mapping itself will complete without exceptions (but with error code). Run the same mapping in Row base – you will get your first error. If so – my guess is right. If not – will think over.
                      Please, publish the results.

                      Sergey.
                      • 8. Re: Runtime Error
                        gillesp
                        Hello AP and Sergey

                        I'm kind of experiencing the same problems. When I execute the mapping with the deployment manager there is no problem, no error and all records are inserted. When I execute the exact same mapping in TOAD with the following code (that works fine for other mappings)

                        DECLARE
                        RetVal NUMBER;
                        P_ENV WB_RT_MAPAUDIT.WB_RT_NAME_VALUES;

                        BEGIN
                        -- P_ENV := NULL; Modify the code to initialize this parameter

                        RetVal := ITG.ITG_LOONK_BUDGET_TI.MAIN ( P_ENV );
                        COMMIT;
                        END;

                        I get the following error:

                        ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                        ORA-06512: at "OWB_RUN.WB_RT_MAPAUDIT", line 1762
                        ORA-06512: at "OWB_RUN.WB_RT_MAPAUDIT", line 2651
                        ORA-06512: at "ITG.ITG_LOONK_BUDGET_TI", line 682
                        ORA-01722: invalid number
                        ORA-06512: at "ITG.ITG_LOONK_BUDGET_TI", line 972
                        ORA-06512: at "ITG.ITG_LOONK_BUDGET_TI", line 2345
                        ORA-06512: at line 8

                        There are no weird or long concatenations. Only thing is a varchar2(255) converted to number but I'm 100% sure that that is not causing the problem.

                        So any help would be kindly appreciated!

                        Thanks in advance,
                        Gilles
                        • 9. Re: Runtime Error
                          487104
                          When I run the mapping in Set Based Mode (no failover), the following is the result:

                          The mappings completes with warnings and with no records inserted to the target table. The error in this case as obtained from Runtime Audit Browser is -

                          "ORA-12805: parallel query server died unexpectedly"


                          When I run the mapping in Row Based Mode, the following is the result:

                          The mappings completes with errors and with no records inserted to the target table. The error in this case as obtained from Runtime Audit Browser is -

                          "ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SU_OWB_RUNREPOWN.WB_RT_MAPAUDIT", line 1762 ORA-06512: at "SU_OWB_RUNREPOWN.WB_RT_MAPAUDIT", line 2651 ORA-06512: at "ALOK.M_TEST", line 732 ORA-01722: invalid number ORA-02063: preceding line from TESTMIG2@CONN_TGT_TEAR_SRC ORA-06512: at "ALOK.M_TEST", line 1028 ORA-06512: at "ALOK.M_TEST", line 2603 ORA-06512: at line 1 "


                          It is good I have found one more person to support my view ... it seems very strange that a one to one mapping suddenly behaves like this and points to some packages in OWB runtime repository.

                          Any more help Sergey will be highly appreciated .....

                          Regards
                          -AP
                          • 10. Re: Runtime Error
                            487104
                            When I run the mapping in Set Based Mode (no failover), the following is the result:

                            The mappings completes with warnings and with no records inserted to the target table. The error in this case as obtained from Runtime Audit Browser is -

                            "ORA-12805: parallel query server died unexpectedly"


                            When I run the mapping in Row Based Mode, the following is the result:

                            The mappings completes with errors and with no records inserted to the target table. The error in this case as obtained from Runtime Audit Browser is -

                            "ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SU_OWB_RUNREPOWN.WB_RT_MAPAUDIT", line 1762 ORA-06512: at "SU_OWB_RUNREPOWN.WB_RT_MAPAUDIT", line 2651 ORA-06512: at "ALOK.M_TEST", line 732 ORA-01722: invalid number ORA-02063: preceding line from TESTMIG2@CONN_TGT_TEAR_SRC ORA-06512: at "ALOK.M_TEST", line 1028 ORA-06512: at "ALOK.M_TEST", line 2603 ORA-06512: at line 1 "


                            It is good I have found one more person to support my view ... it seems very strange that a one to one mapping suddenly behaves like this and points to some packages in OWB runtime repository.

                            Any more help Sergey will be highly appreciated .....

                            Regards
                            -AP
                            • 11. Re: Runtime Error
                              gillesp
                              Sergey, AP,

                              Running the mapping set based (no fail over) returns directly an ORA-01722: invalid number as Sergey expected.

                              Running row based or row based target only returns the previous error. All options execute perfectly well with the deployment manager.

                              Further more I tried running the mappings with auditting level set to none (so i would expect nothing to write to any WB_RT_AUDIT tables). The result is that the table gets truncated and nothing inserted, but the procedure completes succesfully.

                              I'm running out of options...

                              Cheers Gilles
                              • 12. Re: Runtime Error
                                99896
                                Hello, guys
                                I faced myself with the similar situation today – not while working with OWB. The root of error was just as I expected. Have a look (and maybe execute) at the code below:

                                DECLARE
                                tmp STRING(10);
                                BEGIN
                                RAISE_APPLICATION_ERROR(-20001,'This text is too long for my exception handler');
                                EXCEPTION
                                WHEN OTHERS THEN
                                tmp := SQLERRM;
                                DBMS_OUTPUT.PUT_LINE(tmp);
                                END;

                                Code execution produces the same shape of stack trace as in yours original mappings. It is so called "Error while reporting an error" situation :) Hope, it gives you an idea of what really happening.
                                The only concern is Gilles note, that says the mapping runs fine under Deployment Manager, but fails from PLSQL block. I don’t have explanation for this.

                                So, I think it’s time to publish your software versions here and open TAR or at least wait for Oracle guys from OWB team to join our discussion.

                                Sergey
                                • 13. Re: Runtime Error
                                  385662
                                  Click on the target object in the mapping and do generate intermediate result and try running the generated statement in SQL*Plus and then debug.
                                  • 14. Re: Runtime Error
                                    gillesp
                                    Avinash,

                                    That isn't causing the problem. The generated statement works just fine. The problem is that the OWB auditing cannot handle a generated error!

                                    For Sergey and AP:

                                    OWB client version: 10.1.0.4.0
                                    OWB repository version: 10.1.0.4.0

                                    select VALUE from nls_database_parameters where parameter =
                                    'NLS_RDBMS_VERSION'
                                    delivers 9.2.0.1
                                    1 2 Previous Next