1 2 3 4 Previous Next 45 Replies Latest reply on Aug 9, 2016 12:46 PM by AndrewSayer Go to original post
      • 15. Re: Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
        Marwim

        SELECT CODE_HTML into v_qy from t_sql where vsql=v_sql;

         

        Seems to be a bug. You should qualify the identifiers like

         

        SELECT CODE_HTML into v_qy from t_sql where t_sql.vsql=f_test.v_sql;

         

        Regards

        Marcus

         

        Edit:

        I missed the underscore. Nevertheless it is a good idea to qualify variables in a SQL.

        • 16. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
          wefty-Oracle

          Hi ,

             My code here using 11G Database....

          =================================================================================

          create or replace FUNCTION f_test (v_id VARCHAR2)  RETURN CLOB AS

           

          V_QY CLOB;

           

          BEGIN

           

              SELECT CODE_HTML into V_QY from t_sql where q_id=v_id;

          return v_qy;

          END;

          ============================================================================

          CODE_HTML Have the CLOB columns and has the all tags and total size 34560

          getting error

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

          please help me how to rectify on this

          • 17. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
            Kalpataru

            Hi wefty-Oracle,

            First run your query in any tool like Toad, Sql plus or Oracle sql developer check that it is running fine or not.

            SELECT CODE_HTML from t_sql where q_id='123'; --Put you correct q_id here

            Check that your q_id datatype is a number type or varchar2 type.

            Then use it inside your function.

            Check this thread may help you. ORA-06502: PL/SQL: numeric or value error: character string buffer too small

            • 18. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
              Solomon Yakobson

              Are you getting error compiling function or calling function?

               

              SY.

              • 19. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                kendenny

                Use to_clob

                SELECT to_clob("many text /wordscontans 33000") into v_qy from t_sql where vsql=v_sql;

                • 20. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                  kendenny

                  3235172 wrote:

                   

                  Hi ,

                     My code here using 11G Database....

                  =================================================================================

                  create or replace FUNCTION f_test (v_id VARCHAR2)  RETURN CLOB AS

                   

                  V_QY CLOB;

                   

                  BEGIN

                   

                      SELECT CODE_HTML into V_QY from t_sql where q_id=v_id;

                  return v_qy;

                  END;

                  ============================================================================

                  CODE_HTML Have the CLOB columns and has the all tags and total size 34560

                  getting error

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

                  please help me how to rectify on this

                  If the column code_html in the table t_sql is defined as a clob then there is no reason this should return that error. If there's anything else you're doing (like || or substr()) that will make the parser convert it to a varchar that could be the problem. Be sure to enclose the entire expression in to_clob() and I'll bet that fixes it.

                  • 21. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                    Bud Light

                    Just for grins.

                     

                    change:

                    create or replace FUNCTION f_test (v_sql VARCHAR2)  RETURN CLOB AS

                     

                    to:

                    create or replace FUNCTION f_test (v_sql CLOB)  RETURN CLOB AS

                    • 22. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                      AndrewSayer

                      3235172 wrote:

                       

                      Hi ,

                         My code here using 11G Database....

                      =================================================================================

                      create or replace FUNCTION f_test (v_id VARCHAR2)  RETURN CLOB AS

                       

                      V_QY CLOB;

                       

                      BEGIN

                       

                          SELECT CODE_HTML into V_QY from t_sql where q_id=v_id;

                      return v_qy;

                      END;

                      ============================================================================

                      CODE_HTML Have the CLOB columns and has the all tags and total size 34560

                      getting error

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

                      please help me how to rectify on this

                       

                      How are you calling the function?

                      Copy and paste a session that shows exactly where the error is occurring. Include all DDL and sample data that is necessary.

                       

                      I predict you are trying to assign a varchar2 variable the results of the function or you are trying to pass it as a parameter that expects a varchar2.

                      • 23. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                        wefty-Oracle

                        Hi All,

                        My code here using 11G Database....

                         

                        =================================================================================

                         

                        create or replace FUNCTION f_test (v_id VARCHAR2)  RETURN CLOB AS

                        V_QY CLOB;

                        BEGIN

                         

                           SELECT CODE_HTML into V_QY from t_sql where q_id=v_id;

                         

                        return v_qy;

                         

                        END;

                         

                        ============================================================================

                         

                        CODE_HTML Have the CLOB columns and has the all tags and total size 34560

                         

                         

                         

                        Executing functions/calling functions on below

                         

                        select f_test('100Z') FROM DUAL;

                         

                         

                         

                        getting error

                         

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

                         

                        please help me how to rectify on this

                        • 24. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                          Kalpataru

                          Hi 3235172,

                          Can you post your create table and insert statement with your Sample data for checking the issue.

                          • 25. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                            wefty-Oracle

                            HI,

                            1)CREATE TABLE T_SQL (Q_ID VARCHAR2(400),CODE_HTML CLOB);

                             

                             

                            2)

                            INSERT INTO T_SQL VALUES ('100Z',

                            '<html> <head> <b>SQL Report Details:</b><br> <style> table { width:50%; } table, th, td { border: 1px solid black; border-collapse: collapse; font-family :Arial,sans-serif; } th, td { padding: 5px; text-align: left; font-size :12px; } table#t01 th { color: #400; font-size :12px; font-family :Arial,sans-serif; } table#t01 tr:nth-child(even) { background-color: #eee; } table#t01 tr:nth-child(odd) { background-color:#fff; } </style> </head> <body> <table id="t01"> <tr style="background-color:#eee;"> <th nowrap>DOCUMENT TITLE </th> <th nowrap>DOCUMENT OWNER </th> <th nowrap>LOB </th> <th nowrap>VERTICAL </th> <th nowrap>FILE NAME </th> <th nowrap>NO. OF USERS </th> <th nowrap>TOTAL VIEWS </th> </tr> <td nowrap>Adding  Shipping Notes MICROS Orders </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap></td> <td nowrap>2 </td> <td nowrap>4 </td> </tr> <td nowrap>AM - Customer Attributes Validation </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap> </td> <td nowrap></td> <td nowrap>1 </td> <td nowrap>2 </td> </tr> <td nowrap>How to transfer SR to Technical Support Team </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>1 </td> </tr> </table>

                            <html> <head> <b>SQL Report Details:</b><br> <style> table { width:50%; } table, th, td { border: 1px solid black; border-collapse: collapse; font-family :Arial,sans-serif; } th, td { padding: 5px; text-align: left; font-size :12px; } table#t01 th { color: #400; font-size :12px; font-family :Arial,sans-serif; } table#t01 tr:nth-child(even) { background-color: #eee; } table#t01 tr:nth-child(odd) { background-color:#fff; } </style> </head> <body> <table id="t01"> <tr style="background-color:#eee;"> <th nowrap>DOCUMENT TITLE </th> <th nowrap>DOCUMENT OWNER </th> <th nowrap>LOB </th> <th nowrap>VERTICAL </th> <th nowrap>FILE NAME </th> <th nowrap>NO. OF USERS </th> <th nowrap>TOTAL VIEWS </th> </tr> <td nowrap>Adding  Shipping Notes MICROS Orders </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap>dddddddd </td> <td nowrap>2 </td> <td nowrap>4 </td> </tr> <td nowrap>AM - Customer Attributes Validation </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap> </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>2 </td> </tr> <td nowrap>How to transfer SR to Technical Support Team </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>1 </td> </tr> </table>

                            <html> <head> <b>SQL Report Details:</b><br> <style> table { width:50%; } table, th, td { border: 1px solid black; border-collapse: collapse; font-family :Arial,sans-serif; } th, td { padding: 5px; text-align: left; font-size :12px; } table#t01 th { color: #400; font-size :12px; font-family :Arial,sans-serif; } table#t01 tr:nth-child(even) { background-color: #eee; } table#t01 tr:nth-child(odd) { background-color:#fff; } </style> </head> <body> <table id="t01"> <tr style="background-color:#eee;"> <th nowrap>DOCUMENT TITLE </th> <th nowrap>DOCUMENT OWNER </th> <th nowrap>LOB </th> <th nowrap>VERTICAL </th> <th nowrap>FILE NAME </th> <th nowrap>NO. OF USERS </th> <th nowrap>TOTAL VIEWS </th> </tr> <td nowrap>Adding AI10 Shipping Notes MICROS Orders </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap></td> <td nowrap>2 </td> <td nowrap>4 </td> </tr> <td nowrap>AM - Customer Attributes Validation </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap> </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>2 </td> </tr> <td nowrap>How to transfer SR to Technical Support Team </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>1 </td> </tr> </table>');COMMIT;

                            • 26. Re: Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                              Kalpataru

                              Hi 3235172,

                              You have not post the correct error.

                              I have tested your actual error is ORA-01704: string literal too long

                              When you put insert statement like this.

                               

                              INSERT INTO T_SQL(Q_ID,CODE_HTML) VALUES ('100Z','<html> <head> <b>SQL Report Details:</b><br> <style> table { width:50%; } table, th, td { border: 1px solid black; border-collapse: collapse; font-family :Arial,sans-serif; } th, td { padding: 5px; text-align: left; font-size :12px; } table#t01 th { color: #400; font-size :12px; font-family :Arial,sans-serif; } table#t01 tr:nth-child(even) { background-color: #eee; } table#t01 tr:nth-child(odd) { background-color:#fff; } </style> </head> <body> <table id="t01"> <tr style="background-color:#eee;"> <th nowrap>DOCUMENT TITLE </th> <th nowrap>DOCUMENT OWNER </th> <th nowrap>LOB </th> <th nowrap>VERTICAL </th> <th nowrap>FILE NAME </th> <th nowrap>NO. OF USERS </th> <th nowrap>TOTAL VIEWS </th> </tr> <td nowrap>Adding  Shipping Notes MICROS Orders </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap></td> <td nowrap>2 </td> <td nowrap>4 </td> </tr> <td nowrap>AM - Customer Attributes Validation </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap> </td> <td nowrap></td> <td nowrap>1 </td> <td nowrap>2 </td> </tr> <td nowrap>How to transfer SR to Technical Support Team </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>1 </td> </tr> </table>
                              <html> <head> <b>SQL Report Details:</b><br> <style> table { width:50%; } table, th, td { border: 1px solid black; border-collapse: collapse; font-family :Arial,sans-serif; } th, td { padding: 5px; text-align: left; font-size :12px; } table#t01 th { color: #400; font-size :12px; font-family :Arial,sans-serif; } table#t01 tr:nth-child(even) { background-color: #eee; } table#t01 tr:nth-child(odd) { background-color:#fff; } </style> </head> <body> <table id="t01"> <tr style="background-color:#eee;"> <th nowrap>DOCUMENT TITLE </th> <th nowrap>DOCUMENT OWNER </th> <th nowrap>LOB </th> <th nowrap>VERTICAL </th> <th nowrap>FILE NAME </th> <th nowrap>NO. OF USERS </th> <th nowrap>TOTAL VIEWS </th> </tr> <td nowrap>Adding  Shipping Notes MICROS Orders </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap>dddddddd </td> <td nowrap>2 </td> <td nowrap>4 </td> </tr> <td nowrap>AM - Customer Attributes Validation </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap> </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>2 </td> </tr> <td nowrap>How to transfer SR to Technical Support Team </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>1 </td> </tr> </table>
                              <html> <head> <b>SQL Report Details:</b><br> <style> table { width:50%; } table, th, td { border: 1px solid black; border-collapse: collapse; font-family :Arial,sans-serif; } th, td { padding: 5px; text-align: left; font-size :12px; } table#t01 th { color: #400; font-size :12px; font-family :Arial,sans-serif; } table#t01 tr:nth-child(even) { background-color: #eee; } table#t01 tr:nth-child(odd) { background-color:#fff; } </style> </head> <body> <table id="t01"> <tr style="background-color:#eee;"> <th nowrap>DOCUMENT TITLE </th> <th nowrap>DOCUMENT OWNER </th> <th nowrap>LOB </th> <th nowrap>VERTICAL </th> <th nowrap>FILE NAME </th> <th nowrap>NO. OF USERS </th> <th nowrap>TOTAL VIEWS </th> </tr> <td nowrap>Adding AI10 Shipping Notes MICROS Orders </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap></td> <td nowrap>2 </td> <td nowrap>4 </td> </tr> <td nowrap>AM - Customer Attributes Validation </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap> </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>2 </td> </tr> <td nowrap>How to transfer SR to Technical Support Team </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>1 </td> </tr> </table>');
                              

                               

                              You have to like this.

                               

                              CREATE TABLE T_SQL (Q_ID VARCHAR2(400),CODE_HTML CLOB);
                              /
                              Table created.
                              
                              The through PL/SQL block you can insert the data into the table.
                              
                              declare
                               v_string CLOB;
                              begin
                              
                               v_string:='<html> <head> <b>SQL Report Details:</b><br> <style> table { width:50%; } table, th, td { border: 1px solid black; border-collapse: collapse; font-family :Arial,sans-serif; } th, td { padding: 5px; text-align: left; font-size :12px; } table#t01 th { color: #400; font-size :12px; font-family :Arial,sans-serif; } table#t01 tr:nth-child(even) { background-color: #eee; } table#t01 tr:nth-child(odd) { background-color:#fff; } </style> </head> <body> <table id="t01"> <tr style="background-color:#eee;"> <th nowrap>DOCUMENT TITLE </th> <th nowrap>DOCUMENT OWNER </th> <th nowrap>LOB </th> <th nowrap>VERTICAL </th> <th nowrap>FILE NAME </th> <th nowrap>NO. OF USERS </th> <th nowrap>TOTAL VIEWS </th> </tr> <td nowrap>Adding  Shipping Notes MICROS Orders </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap></td> <td nowrap>2 </td> <td nowrap>4 </td> </tr> <td nowrap>AM - Customer Attributes Validation </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap> </td> <td nowrap></td> <td nowrap>1 </td> <td nowrap>2 </td> </tr> <td nowrap>How to transfer SR to Technical Support Team </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>1 </td> </tr> </table>
                              <html> <head> <b>SQL Report Details:</b><br> <style> table { width:50%; } table, th, td { border: 1px solid black; border-collapse: collapse; font-family :Arial,sans-serif; } th, td { padding: 5px; text-align: left; font-size :12px; } table#t01 th { color: #400; font-size :12px; font-family :Arial,sans-serif; } table#t01 tr:nth-child(even) { background-color: #eee; } table#t01 tr:nth-child(odd) { background-color:#fff; } </style> </head> <body> <table id="t01"> <tr style="background-color:#eee;"> <th nowrap>DOCUMENT TITLE </th> <th nowrap>DOCUMENT OWNER </th> <th nowrap>LOB </th> <th nowrap>VERTICAL </th> <th nowrap>FILE NAME </th> <th nowrap>NO. OF USERS </th> <th nowrap>TOTAL VIEWS </th> </tr> <td nowrap>Adding  Shipping Notes MICROS Orders </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap>dddddddd </td> <td nowrap>2 </td> <td nowrap>4 </td> </tr> <td nowrap>AM - Customer Attributes Validation </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap> </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>2 </td> </tr> <td nowrap>How to transfer SR to Technical Support Team </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>1 </td> </tr> </table>
                              <html> <head> <b>SQL Report Details:</b><br> <style> table { width:50%; } table, th, td { border: 1px solid black; border-collapse: collapse; font-family :Arial,sans-serif; } th, td { padding: 5px; text-align: left; font-size :12px; } table#t01 th { color: #400; font-size :12px; font-family :Arial,sans-serif; } table#t01 tr:nth-child(even) { background-color: #eee; } table#t01 tr:nth-child(odd) { background-color:#fff; } </style> </head> <body> <table id="t01"> <tr style="background-color:#eee;"> <th nowrap>DOCUMENT TITLE </th> <th nowrap>DOCUMENT OWNER </th> <th nowrap>LOB </th> <th nowrap>VERTICAL </th> <th nowrap>FILE NAME </th> <th nowrap>NO. OF USERS </th> <th nowrap>TOTAL VIEWS </th> </tr> <td nowrap>Adding AI10 Shipping Notes MICROS Orders </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap></td> <td nowrap>2 </td> <td nowrap>4 </td> </tr> <td nowrap>AM - Customer Attributes Validation </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap> </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>2 </td> </tr> <td nowrap>How to transfer SR to Technical Support Team </td> <td nowrap>kingkingking@gmail.com </td> <td nowrap>Quote-to-Order </td> <td nowrap>Non Revenue Orders </td> <td nowrap> </td> <td nowrap>1 </td> <td nowrap>1 </td> </tr> </table>';
                              
                              INSERT INTO T_SQL(Q_ID,CODE_HTML) VALUES ('100Z',v_string);
                              
                              end;
                              

                               

                              COMMIT;
                              
                              CREATE OR REPLACE FUNCTION f_test (v_id VARCHAR2)  RETURN CLOB AS
                              V_QY CLOB;
                              BEGIN
                                 SELECT CODE_HTML into V_QY from t_sql where q_id=v_id;
                                 return v_qy;
                              END;
                              
                              SELECT   f_test ('100Z') FROM DUAL; -->Running in TOAD
                              
                              F_TEST('100Z')
                              ----------------------
                              (CLOB)
                              

                               

                               

                              Hope it will help you.

                              Just for your reference ORA-01704: string literal too long for CLOB colum

                              https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9522453800346549342

                              • 27. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                                wefty-Oracle

                                hi ,

                                  CODE_HTML COLUMNS should contains >34000 length data.

                                  can you check you are inserted data length........

                                • 28. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                                  wefty-Oracle

                                  Hi Expert,

                                       Any of them help on my issue ? please help on this....

                                  • 29. Re: CLOB -ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                                    Paul  Horth

                                    I used your example but had to  modify the insert as it didn't work.

                                     

                                    Put your function code in and it worked fine with

                                     

                                    SELECT   f_test ('100Z') FROM DUAL;


                                    You are obviously not showing us what you really ran to get that error.