13 Replies Latest reply on Aug 14, 2013 2:43 PM by marcusafs

    Error while exporting CLOB column

    84b09b47-0add-4854-b184-296b3903b009

      Hi Everyone,

       

      Using Oracle 10g,

      I have a table. One of the fields of output is sourced from a CLOB datatype (few data length around 7500).

       

      I'm attempting to export query results in csv format. It appears to me that the field is truncated during export to 4000 charactersl. When I view the query result set on screen, the full CLOB data appears. I'm checking a test case where there are about 9,000 characters.

       

           How can I export such CLOB data in csv/xls of more than 5000 data size?

       

      I tried searching this forum for similar posts amd found the few as below but could not find a solution.

      https://forums.oracle.com/thread/2436406


      Thanks.

        • 2. Re: Error while exporting CLOB column
          84b09b47-0add-4854-b184-296b3903b009

          When I try to perform the below as refered in the above forum link, this results in "NULL" column value for the CLOB datatype field:

           

          create or replace
          function printClob( p_clob in clob ) return varchar2
          as
               l_offset number default 1;
          begin
             loop
               exit when l_offset > dbms_lob.getlength(p_clob);
               dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );
               l_offset := l_offset + 255;
             end loop;
             return null;
          end;

           

          select filename, printclob(my_clob) from table where id = 1;

           

               1. Are there any settings/serveroutput I should be checking before performing the above.

               2. Is there any limitation to perform the above task?

           

          Let me know if any more info is required from my side.

          Thanks in advance.

          • 3. Re: Error while exporting CLOB column
            84b09b47-0add-4854-b184-296b3903b009

            I am using the "export wizard" of Oracle SQL developer by clicking on data (right-click / export).

            If I copy (Ctrl-C while cell is highlighted) or export the data (right-click / export), my Clob is truncated like in the hint at 4000th char!

            • 4. Re: Error while exporting CLOB column
              Karthick2003

              This is a SQL and PL/SQL forum. You need to post your question in SQL Developer forum.

              • 6. Re: Error while exporting CLOB column
                84b09b47-0add-4854-b184-296b3903b009

                Hi Karthick and everyone,

                 

                As refered by the below post, is it true that there is no alternative for this task?

                https://forums.oracle.com/thread/1013244

                • 7. Re: Error while exporting CLOB column
                  Karthick2003

                  Its very much possible in PL/SQL.

                   

                  Here is an example. Consider this sample table.

                   

                  SQL> desc t
                  Name                    Null?    Type
                  ----------------------- -------- ----------------
                  NO                               NUMBER(38)
                  LARGE_DATA                       CLOB

                   

                  SQL> select no, length(large_data) from t;

                   

                          NO LENGTH(LARGE_DATA)
                  ---------- ------------------
                           1              10000

                   

                  You can write a PL/SQL code like below to export your data in delimited file

                   

                  create or replace function  write_as_delimited_file_big
                  (
                          p_query     in dbms_sql.varchar2a,
                          p_separator in varchar2 default ',',
                          p_dir       in varchar2,
                          p_filename  in varchar2,
                          p_is_head   in boolean default false
                  )
                  return number AUTHID CURRENT_USER
                  is
                          l_output        utl_file.file_type;
                          l_theCursor     integer default dbms_sql.open_cursor;
                          l_columnValue   varchar2(2000);
                          l_columnValClob clob;
                          l_status        integer;
                          l_colCnt        number default 0;
                          l_separator     varchar2(10) default '';
                          l_cnt           number default 0;
                          l_col_desc      dbms_sql.desc_tab;
                          l_offset        integer;
                  begin
                          dbms_sql.parse
                          ( 
                              l_theCursor
                            , p_query
                            , p_query.first
                            , p_query.last
                            , true
                            , dbms_sql.native
                          );
                         
                          dbms_sql.describe_columns
                          (
                              l_theCursor
                            , l_colCnt
                            , l_col_desc
                          );
                         
                          for i in 1 .. l_colCnt loop
                              if l_col_desc(i).col_type = 112 then
                                  dbms_sql.define_column
                                  (
                                      l_theCursor
                                    , i
                                    , l_columnValClob
                                  );
                             else
                                  dbms_sql.define_column
                                  (
                                      l_theCursor
                                    , i
                                    , l_columnValue
                                    , 4000
                                  );
                             end if; 
                          end loop;

                          l_status := dbms_sql.execute(l_theCursor);
                         
                          if dbms_sql.fetch_rows(l_theCursor) > 0 then
                                  l_output := utl_file.fopen( p_dir, p_filename, 'w', 32767 );

                                  if p_is_head then
                                          for i in 1..l_col_desc.count
                                          loop
                                                  utl_file.put(l_output, l_separator || l_col_desc(i).col_name);
                                                  l_separator := p_separator;
                                          end loop;
                                          utl_file.new_line( l_output );
                                  end if;

                                  loop
                                          l_separator := '';
                                         
                                          for i in 1 .. l_colCnt
                                          loop
                                              if l_col_desc(i).col_type = 112
                                              then
                                                 l_offset := 1;
                                                 dbms_sql.column_value
                                                 (
                                                      l_theCursor
                                                    , i
                                                    , l_columnValClob
                                                 );
                                                 utl_file.put( l_output, l_separator );

                                                 loop
                                                       l_columnValue := dbms_lob.substr(l_columnValClob, 2000, l_offset);
                                                       dbms_output.put_line(l_columnValue);
                                                       l_offset := l_offset + 2000;
                                                       utl_file.put( l_output, l_columnValue);
                                                       exit when trim(l_columnValue) is null;
                                                  end loop;
                                              else
                                                 dbms_sql.column_value
                                                 (
                                                      l_theCursor
                                                    , i
                                                    , l_columnValue
                                                 );
                                                 utl_file.put( l_output, l_separator ||  l_columnValue );
                                              end if;
                                             
                                              l_separator := p_separator;
                                          end loop;
                                 
                                          utl_file.new_line( l_output );
                                          l_cnt := l_cnt+1;

                                          exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
                                  end loop;
                          end if;

                          dbms_sql.close_cursor(l_theCursor);

                          utl_file.fclose( l_output );
                         
                          return l_cnt;
                  end;
                  /
                  show err

                   

                  And you can call it like this

                   

                  declare
                    l_sql dbms_sql.varchar2a;
                    l_cnt integer;
                  begin
                    l_sql(1) := 'select * from t';
                    l_cnt    := write_as_delimited_file_big
                                (
                                     l_sql,
                                     '|',
                                     'KARDIR', -- This is my directory object pointing to physical directory in my server
                                     'KARTHICK_TEST',
                                     TRUE
                                );
                  end;
                  /

                  • 8. Re: Error while exporting CLOB column
                    84b09b47-0add-4854-b184-296b3903b009

                    Hi Karthik,

                     

                    Thanks for the solution. But when I try to run this, it gives the below warnings:

                     

                    FUNCTION write_as_delimited_file_big compiled

                    Warning: execution completed with warning

                    11/25          PLS-00201: identifier 'UTL_FILE' must be declared

                    11/25          PL/SQL: Item ignored

                    60/17          PLS-00320: the declaration of the type of this expression is incomplete or malformed

                    60/17          PL/SQL: Statement ignored

                    64/46          PLS-00320: the declaration of the type of this expression is incomplete or malformed

                    64/33          PL/SQL: Statement ignored

                    67/44          PLS-00320: the declaration of the type of this expression is incomplete or malformed

                    67/25          PL/SQL: Statement ignored

                    83/46          PLS-00320: the declaration of the type of this expression is incomplete or malformed

                    83/32          PL/SQL: Statement ignored

                    88/52          PLS-00320: the declaration of the type of this expression is incomplete or malformed

                    88/38          PL/SQL: Statement ignored

                    98/46          PLS-00320: the declaration of the type of this expression is incomplete or malformed

                    98/32          PL/SQL: Statement ignored

                    104/44         PLS-00320: the declaration of the type of this expression is incomplete or malformed

                    104/25         PL/SQL: Statement ignored

                    110/26         PLS-00320: the declaration of the type of this expression is incomplete or malformed

                    110/9          PL/SQL: Statement ignored

                     

                    Also, kindly help me in understanding the meaning of "directory object pointing to physical directory in my server".

                    Thanks for your help.

                    • 9. Re: Error while exporting CLOB column
                      Karthick2003

                      Make sure you have execute privilege granted to the following packages

                       

                      1. UTL_FILE

                      2. DBMS_SQL

                      3. DBMS_LOB

                       

                      And check the parameter PLSQL_WARNINGS. Set it to DISABLE:ALL if you want to avoid all the warning messages.

                       

                      Directory object can be considered as a alias that points to a location in your server. For example i have a directory in my Unix box "/home/karthick" I use this directory to do all my file operation from my DB. So to access this directory location i need to have a directory object in my DB that points to "/home/karthick". Using the CREATE DIRECTORY DDL command you can create your directory object.

                      • 10. Re: Error while exporting CLOB column
                        84b09b47-0add-4854-b184-296b3903b009

                        Cool.

                         

                        Thanks for info. I followed the below path and was successful to compile the Function:

                         

                        Logged in as sysdba

                        connect sys/password as sysdba

                         


                             Then I granted privilidges to the user I am going to use:

                        grant execute on utl_file to system;

                         


                        Now I can use UTL_FILE.


                        Moving ahead to use the function, will revert back soon.

                        • 11. Re: Error while exporting CLOB column
                          84b09b47-0add-4854-b184-296b3903b009

                          Hi Karthick,

                           

                          It worked perfectly for a local server, but if I need to run it on a client server is there a way to create a directory object pointing to client server physical directory which I can access?

                          • 12. Re: Error while exporting CLOB column
                            Karthick2003

                            No you can't access client location from DB. You can access only the directory in the server where the DB is installed.

                            • 13. Re: Error while exporting CLOB column
                              marcusafs

                              It is true that you cannot read from the client machine, you can write to it, at least on a Microsoft box.  Create a network share, say \\mycomputer\myshare and give the owner of the oracle database (we change the login for our service to a domain account, not a local system account) read/write share access and full control on the folder.  Then create an Oracle directory in the database and use that as the location to write to.  To create the directory:

                              CREATE OR REPLACE DIRECTORY

                              FILE_DIR_MY_MACHINE AS

                              '\\mycomputer\myshare';

                               

                               

                               

                              GRANT EXECUTE, READ, WRITE ON DIRECTORY FILE_DIR_MY_MACHINE TO <<proc owner>>;

                               

                              where <<proc owner>> is the schema that will be calling utl_file.

                              1 person found this helpful