4 Replies Latest reply: Mar 6, 2009 5:21 AM by 105967 RSS

    DBMS_LOB.SUBSTR

    641320
      SELECT DBMS_LOB.SUBSTR(col1,5000,1) FROM      table WHERE     NUM=100

      while executing this query i am getting error

      ORA-06502 PL/SQL NUMBERIC OR VALUE ERROR CHARACTER STRING BUFFER TOO SMALL



      can any one tell why this comes?



      S
        • 1. Re: DBMS_LOB.SUBSTR
          105967
          You have a table called "table"?!?

          Why don't you give us the exact copy/paste?

          Did you try to select less than 4000 bytes? (SQL*Plus may have a restriction)
          Try
          SELECT DBMS_LOB.SUBSTR(col1,3000,1) FROM table WHERE NUM=100
          /
          • 2. Re: DBMS_LOB.SUBSTR
            538097
            http://www.mydatabasesupport.com/forums/oracle-server/428340-dbms_lob-substr-failed-retrieve-4000-characters.html
            • 3. Re: DBMS_LOB.SUBSTR
              641320
              Yes i tried with 4000 IT is working fine.

              What you are trying to tell is Clob to varchar2 conversion happening here. it wont exceed 4000. this is ok.


              Why you think of varchar2? does dbms_lob.substr can convert clob to long?



              S
              • 4. Re: DBMS_LOB.SUBSTR
                105967
                Yes and No.

                In this example, you are using dbms_lob.substr in a plain SQL statement. In SQL, a varchar2 has a max limit of 4000 characters. In PL/SQL, a varchar2 can hold up to 32K characters. The limitation to 4000 chars is of the SQL.

                By its definition, the function dbms_lob.substr is returning a varchar2 (or raw) though no, you can not convert it into a long.