Link to home
Start Free TrialLog in
Avatar of Swadhin Ray
Swadhin RayFlag for United States of America

asked on

export text or csv or excel from oracle

Hello Experts,

I need help in modifying the current function on my database like:
1) This function is able to generate text , csv , xls file from oracle database.  But heading are missing where I also need to export the headings.
2) Attached the screen shot for the way required while exporting the text file from this function. As this function is generic , so need to add the logic for line terminator when the file has to be text file only not for csv and xls should be "Windows <CR><LF>" :

User generated image3) Issue while exporting in xls file:
When I execute the block for xls file then the data are been exported all on one column as like below:

OUTUT:
data1,data2,true

Open in new window


Function code is as below:

 CREATE OR REPLACE FUNCTION DUMP_FILE ( p_query        in varchar2,
                        p_separator in varchar2 default ',',
                        p_dir       in varchar2 ,
                        p_filename  in varchar2 )
    return number
    is
        l_output        utl_file.file_type;
        l_theCursor     integer default dbms_sql.open_cursor;
       l_columnValue   varchar2(2000);
       l_status        integer;
       l_colCnt        number default 0;
       l_separator     varchar2(10) default '';
       l_cnt           number default 0;
   begin
       l_output := utl_file.fopen( p_dir, p_filename, 'w' );
   
       dbms_sql.parse(  l_theCursor,  p_query,
                                            dbms_sql.native );
   
       for i in 1 .. 255 loop
           begin
               dbms_sql.define_column( l_theCursor, i,
                                       l_columnValue, 2000 );
               l_colCnt := i;
           exception
               when others then
                   if ( sqlcode = -1007 ) then exit;
                   else
                       raise;
                   end if;
           end;
       end loop;
   
       dbms_sql.define_column( l_theCursor, 1,
                               l_columnValue, 2000 );
   
       l_status := dbms_sql.execute(l_theCursor);
   
       loop
           exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i,
                                      l_columnValue );
               utl_file.put( l_output,
                             l_separator || l_columnValue );
               l_separator := p_separator;
           end loop;
           utl_file.new_line( l_output );
           l_cnt := l_cnt+1;
       end loop;
       dbms_sql.close_cursor(l_theCursor);
   
       utl_file.fclose( l_output );
       return l_cnt;
    end dump_file;
		

/

Open in new window

Avatar of Swadhin Ray
Swadhin Ray
Flag of United States of America image

ASKER

Also when I execute it for xls file it gives me the data as like below in one column:

OUTPUT:
data1,data2,True,
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Is this two question?  
1: how to export using Toad (or similar GUI)?
2: how to get headers in the output file using your UTL_FILE function?

To get the headers using your UTL_FILE function, just use another UTL_FILE.PUT_LINE with the header text outside the loop.
chubby_informer,

Interesting observation:  That is the first link in Google if I use the exact question subject :export text or csv or excel from oracle
@slightwv : I am only looking for an solution from PLSQL function not from Toad or other GUI.

As you said using UTL_FILE.PUT_LINE ??
Here how I can add the columns that will be there on my sql query ?

Here is my PLSQL block for how I am executing this function for csv file:


DECLARE
  l_rows NUMBER;
BEGIN
  L_ROWS := DUMP_FILE( 'select * from emp', ',', 'MY_DIR', 'test.csv' );
  dbms_output.put_line( TO_CHAR(l_rows) || ' rows extracted to ascii file' );
END; 

Open in new window


So the function should be modified in such a way that it should dynamically take the columns for what ever the query I am passing it through my PLSQL BLOCK .
I've not done much with DBMS_SQL but my guess is the for loop making calls to dbms_sql.column_value with a value of l_columnValue  is the actual column name?

If so, declare a varchar2 variable and build the header record in that loop.

Something like:

header_record := header_record || l_columnValue || ',';


then outside the loop, removethe trailing ',':
header_record := rtrim(header_record,',');
I have changed the function a bit to get the headers like as below code, but the result is not getting pupolated properly .

Here is the output I get after calling this function:

DECLARE
  l_rows NUMBER;
BEGIN
  L_ROWS := DUMP_FILE( 'select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY from EMP', '|', 'ORA_DIR', 'test.txt' );
  dbms_output.put_line( TO_CHAR(l_rows) || ' rows extracted to ascii file' );
END; 

Open in new window


OUTPUT:

EMPLOYEE_ID|FIRST_NAME|LAST_NAME|SALARY|100|Steven|King|24000
101|Neena|Kochhar|17000
102|Lex|De Haan|17000
103|Alexander|Hunold|9000

After salary the values should come in next line but it is not coming.
Need help in fixing this.
 
Function Code:

CREATE OR REPLACE
  FUNCTION DUMP_FILE(
      p_query     IN VARCHAR2,
      p_separator IN VARCHAR2 DEFAULT ',',
      p_dir       IN VARCHAR2 ,
      p_filename  IN VARCHAR2 )
    RETURN NUMBER
  IS
    l_output utl_file.file_type;
    l_theCursor   INTEGER DEFAULT dbms_sql.open_cursor;
    l_columnValue VARCHAR2(2000);
    l_status      INTEGER;
    l_colCnt      NUMBER DEFAULT 0;
    l_separator   VARCHAR2(10) DEFAULT '';
    l_cnt         NUMBER DEFAULT 0;
    l_descTbl dbms_sql.desc_tab;
  BEGIN
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );
    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
    --dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
    FOR i IN 1 .. 255
    LOOP
      BEGIN
        dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
        l_colCnt := i;
        utl_file.put( l_output, l_separator || l_descTbl(i).col_name || '|' );
      EXCEPTION
      WHEN OTHERS THEN
        IF ( SQLCODE = -1007 ) THEN
          EXIT;
        ELSE
          raise;
        END IF;
      END;
    END LOOP;
    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
    l_status := dbms_sql.execute(l_theCursor);
    LOOP
      EXIT
    WHEN ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
      l_separator                           := '';
      FOR i                                 IN 1 .. l_colCnt
      LOOP
        dbms_sql.column_value( l_theCursor, i, l_columnValue );
        utl_file.put( l_output, l_separator || l_columnValue );
        l_separator := p_separator;
      END LOOP;
      utl_file.new_line( l_output );
      l_cnt := l_cnt+1;
    END LOOP;
    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_output );
    RETURN l_cnt;
  END dump_file;

Open in new window

DBMS_OUTPUT.PUT does not add a newline.  DBMS_OUTPUT.PUTLINE does.
I dont see anything on DBMS_OUTPUT.PUT on my code but on "UTL_FILE.PUT " when replaced to UTL_FILE.PUT_LINE then I get the below result on my output file :

EMPLOYEE_ID|
FIRST_NAME|
LAST_NAME|
SALARY|
100|
Steven|
King|
24000

101|
Neena|
Kochhar|
17000
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot slightwv I missed to put the put_line after headers loop.

Here is the final code which is giving me the perfect result :

create or replace
FUNCTION DUMP_FILE(
      p_query     IN VARCHAR2,
      p_separator IN VARCHAR2 DEFAULT ',',
      p_dir       IN VARCHAR2 ,
      p_filename  IN VARCHAR2 )
    RETURN NUMBER
  IS
    l_output utl_file.file_type;
    l_theCursor   INTEGER DEFAULT dbms_sql.open_cursor;
    l_columnValue VARCHAR2(2000);
    l_status      INTEGER;
    l_colCnt      NUMBER DEFAULT 0;
    l_separator   VARCHAR2(10) DEFAULT '';
    l_cnt         NUMBER DEFAULT 0;
    l_descTbl dbms_sql.desc_tab;
  BEGIN
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );
    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
    --dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
    FOR i IN 1 .. 255
    LOOP
      BEGIN
        dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
        l_colCnt := i;
        utl_file.put( l_output, l_separator || l_descTbl(i).col_name || '|' );
        
      EXCEPTION
      WHEN OTHERS THEN
        IF ( SQLCODE = -1007 ) THEN
          EXIT;
        ELSE
          raise;
        END IF;
      END;
    END LOOP;
    [b]utl_file.new_line( l_output ); -- Missed to put here [/b]
    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
    l_status := dbms_sql.execute(l_theCursor);
    LOOP
      EXIT
    WHEN ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
      l_separator                           := '';
      FOR i                                 IN 1 .. l_colCnt
      LOOP
        dbms_sql.column_value( l_theCursor, i, l_columnValue );
        utl_file.put( l_output, l_separator || l_columnValue);
        l_separator := p_separator;
      END LOOP;
      utl_file.new_line( l_output );
      l_cnt := l_cnt+1;
    END LOOP;
    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_output );
    RETURN l_cnt;
  END dump_file;

Open in new window


PLSQL block to run this:
DECLARE
  l_rows NUMBER;
BEGIN
  L_ROWS := DUMP_FILE( 'select * from emp', '|', 'ORA_DIR', 'final.txt' );
  dbms_output.put_line( TO_CHAR(l_rows) || ' rows extracted to ascii file' );
END;

Open in new window

Thanks a ton ...