9 Replies Latest reply on Jul 13, 2010 3:13 AM by user9542546

    convertion

    user9542546
      Hi,

      I have an issue with migrating data, In source table two columns with varchar2 datatypes and in destination the target column is clob datatype.
      So here my task is to concat two source columns and populate into target(clob) column .
      But after concatination many records have morethan 4000 characters so the target column is not accepting and throwing an error like exceeding limit.

      could anyone please guide how to overcome this issue.

      Thanks in advance.

      Regards
      Sunilkumar
        • 1. Re: convertion
          Etbin
          I don't have database access to prove it, but something like this should work
          select id,cast(varchar2_column || the_other_varchar2_column as clob) clob_column
            from source_table
          Regards

          Etbin
          • 2. Re: convertion
            MScallion
            You can use TO_CLOB on the source columns;
            create table t1 as (
              select lpad('a',4000,'a') str1,
                     lpad('a',4000,'a') str2
              from dual);
             
            Table created
            create table t2(clob1 clob);
             
            Table created
            select length(str1), length(str2)
            from t1;
             
            LENGTH(STR1) LENGTH(STR2)
            ------------ ------------
                    4000         4000
            insert into t2(clob1)
              select to_clob(str1)||to_clob(str2) from t1;
             
            1 row inserted
            select length(clob1) from t2;
             
            LENGTH(CLOB1)
            -------------
                     8000
            • 3. Re: convertion
              Solomon Yakobson
              Etbin wrote:
              I don't have database access to prove it, but something like this should work
              It will not:
              varchar2_column || the_other_varchar2_column
              datatype os still varchar2 and will fail if excceeds 4000 bytes before casting to clob. you need to convert one of the columns to clob before concatetation:
              varchar2_column || to_clob(the_other_varchar2_column)
              SY.
              • 4. Re: convertion
                Etbin
                Good catch. Thanks

                Regards

                Etbin
                • 5. Re: convertion
                  user9542546
                  Thanks alot Etbin,

                  Now that data able to insert into target table.But When i write select query on target tables its showing clob..clob in the particualer not the migrated value.

                  when i click on this clob column for detailed recult then its showing the migrated data.How come its possible.

                  Thanks in advance.
                  • 6. Re: convertion
                    728534
                    Hi,
                    ..
                    When i click on this clob column for detailed recult then its showing the migrated data.How come its possible.
                    Let me guess, using PL/SQL developer (or may eb TOAD).
                    That is how they work.
                    However the valus of the column is the value that you see after clicking on that field.

                    REgards,
                    Bhushan
                    • 7. Re: convertion
                      user9542546
                      Hi Bhushan,

                      Thanks for your response,I am using pl/sql developer client tool.

                      Regards
                      Sunil
                      • 8. Re: convertion
                        728534
                        Hi,
                        ok..so there you go...as explained above.
                        Its is the way PL/SQL developer is.
                        No need to worry as long as you can see what you want ;)

                        Cheers!!!
                        Bhushan
                        • 9. replace  function
                          user9542546
                          Hi,

                          I am working one migration project ,So i need to convert #39 with apostrophie symbol.So i tried with replace function
                          replace(column name,'#39',' ' ')

                          but in a string its not accepting apostrophie symbol.Please help me how to write place function.
                          Your help would be appreciate.
                          Thanks
                          Sunil