This discussion is archived
13 Replies Latest reply: Aug 14, 2013 7:43 AM by marcusafs RSS

Error while exporting CLOB column

84b09b47-0add-4854-b184-296b3903b009 Newbie
Currently Being Moderated

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.

  • 1. Re: Error while exporting CLOB column
    Karthick_Arp Guru
    Currently Being Moderated

    How are you exporting?

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

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Ok. Thanks for redirecting.

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

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points