10 Replies Latest reply: Apr 22, 2013 5:48 AM by user11995332 RSS

    SELECT CLOB ISSUE REMOVING CARRIAGE RETURN

    user11995332
      Hello Everyone,

      I am facing issue while selecting a CLOB column.

      While spooling clob it removing the CRLF (LF) part in spooled file.

      But when i download same CLOB file from toad or SQL DEVELOPER file download with proper CRLF.

      Is there any bug in oracle or do i need to do some setting or call some function for resolving it.

      Kindly suggest what could be the issue and what could be its solution.

      Thanks

      Edited by: user11995332 on Apr 19, 2013 4:24 AM
        • 1. Re: SELECT CLOB ISSUE REMOVING CARRIAGE RETURN
          sybrand_b
          First of all:
          There is no reason to use capitals for the subject line: this is considered shouting.
          Secondly: If there is an issue, it is usually version dependent, so you should always post your 4 digit Oracle version.
          Finally: we have to guess how you arrived at your issue, as you don't specify which tool you were using, you don't specify the statement in error, and no table definition,
          so nobody can reproduce anything.

          -----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: select CLOB issue removing carriage return (while spooling to file from
            user11995332
            Hi Sybrand,

            Apology for these silly mistakes as i m not regular on OTN i am not aware of it.

            Please find below information:

            Oracle version : 11.2.0.3

            I am using sqlplus for spooling CLOB data to file.

            I have created table named as test with one column with CLOB datatype. That table having one value and a value is mention below:

            {1:F01ABCDXX33AXXX0000000000}{2:I202WXYZXX22XXXXN}{3:{108:FTFXS523125}}{4:
            :20:FXS523125
            :21:210893-2
            :32A:111013EUR1000000,00
            :57A:TESTXX2LXXX
            :58A:/780839
            TESTXX2LXXX
            -}
            • 3. Re: SELECT CLOB ISSUE REMOVING CARRIAGE RETURN
              Solomon Yakobson
              Are you spooling in SQL*Plus? If so, what OS? Most likely Unix. In Unix lines are separated by LF. In Windows where you run SQL*Developer and Toad lines are separated by CRLF.

              SY.
              • 4. Re: SELECT CLOB ISSUE REMOVING CARRIAGE RETURN
                user11995332
                I am try on both the OS windows as well as in unix but same problem is occurring. Opening clob data from toad or sql developer gives proper CRLF but when i spool it / converting CLOB to TO_CHAR in toad CR gets removed. still unable to trace what is the reason for CR removal from data.

                Edited by: user11995332 on Apr 22, 2013 2:44 AM
                • 5. Re: SELECT CLOB ISSUE REMOVING CARRIAGE RETURN
                  Peter Gjelstrup
                  Hi,

                  It is very hard to understand your question, since only you know what you are doing. Would be responders are left guessing.

                  In particular this seentense give very little meaning to anyone not standing behind you:
                  Opening clob data from toad or sql developer gives proper CRLF but when i spool it / converting CLOB to TO_CHAR in toad LF gets removed
                  Which is it, Your subject say CR is removed, still you keep saying LF.?

                  Spool from where - Is it a Toad spool? - What happens on which OS, how do you determine what happened. Do you move files between Unix and Windows. Is spooled file later opened in Toad. If so how are your settings for "New line format characer data"

                  This would be minimum effort trying to explain such problem
                  sqlplus spool file for chr(13)||chr(10).
                  (Although that is still lacking on the OS part)


                  Anyway, SY probably answered it. (Guessing that it is CR removed, and not LF)
                  If this also is the case when spooling to Windows OS, then please demonstrate.


                  Regards
                  Peter
                  • 6. Re: SELECT CLOB ISSUE REMOVING CARRIAGE RETURN
                    user11995332
                    Hi Peter,

                    i have corrected the statement regarding CR and LF. Actually CR is getting removed from the string present in CLOB.

                    Still i have not found any solution for this. i have tried to spool the clob in EXCEL but in excel CR and LF both gets removed and result is coming in a single line.
                    • 7. Re: SELECT CLOB ISSUE REMOVING CARRIAGE RETURN
                      theoa
                      How do you know that the CRs are in your CLOB? If the original file was created in Unix, it probably has only LF.
                      I would not be surprised it Toad and Sqldeveloper interpret a single LF in the CLOB as a CR/LF on screen.
                      • 8. Re: SELECT CLOB ISSUE REMOVING CARRIAGE RETURN
                        user11995332
                        Hi Theoa,

                        When i check my string in binary mode i can only find 0A not 0D, but when i save this file using option given in a toad 0A and 0D is coming properly. This problem occurs using sqlplus only. However in toad CRLF is coming properly.
                        • 9. Re: SELECT CLOB ISSUE REMOVING CARRIAGE RETURN
                          theoa
                          user11995332 wrote:
                          When i check my string in binary mode i can only find 0A not 0D, but when i save this file using option given in a toad 0A and 0D is coming properly. This problem occurs using sqlplus only. However in toad CRLF is coming properly.
                          No, the problem occurs using Toad. The clob does not contain the CR!
                          Sql*plus is displaying it as it really is.
                          Toad is converting the Unix LF to the Windows CR/LF.

                          If you want the CR added in sql*plus, your could use REPLACE(clob, chr(10), chr(13)||chr(10));
                          • 10. Re: SELECT CLOB ISSUE REMOVING CARRIAGE RETURN
                            user11995332
                            But when i am trying same on windows this issue occurs on windows also.