13 Replies Latest reply: Aug 14, 2013 9:43 AM by marcusafs RSS

    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
              Karthick_Arp

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

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

                Ok. Thanks for redirecting.

                • 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
                    Karthick_Arp

                    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
                        Karthick_Arp

                        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
                              Karthick_Arp

                              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.