UTL_FILE - writiing out rows from a cursor

[DavidSh]

Dear all,
I am trying to output rows written to a cursor by using the UTL_FILE utility and fflush procedure (see below). However, no rows are being written to the output file:

CREATE OR REPLACE PROCEDURE PR_TEST_PROCEDURE
AS
v_dir VARCHAR2(30):='/home/reports';
v_name VARCHAR2(60);
fil UTL_FILE.FILE_TYPE;
v_Row t_test%ROWTYPE;
CURSOR cursor_test IS SELECT * FROM t_test;
BEGIN
OPEN cursor_test;

v_name:= 'Test_Cursor_File.csv';
fil:= UTL_FILE.fopen(v_dir, v_name, 'w');

WHILE cursor_test%FOUND LOOP
FETCH cursor_test INTO v_Row;
UTL_FILE.FFLUSH(fil);
END LOOP;
CLOSE cursor_test;
UTL_FILE.FCLOSE(fil);
END;
/

Any help appreciated.

Thanks

 

[Dagon]

I may be mistaken, but you don't actually seem to be calling a function to write to the file anywhere. You need something like utl_file.put_line. Fflush doesn't actually write to the file - it just ensures that whatever is in the output buffer has actually been written to the file.

 

[SantaMufasa]

David,

Is your objective to figure out how to correctly use the syntax, above, or is your objective to create simple, efficient PL/SQL code to output data from from a table to a flat file?

We (Dagon, others, and I) can help, with either objective...please advise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[DavidSh]

Thanks for the replies - I'll explain a little further what I'm trying to achieve.

Basically, I have a table e.g. t_test which can potentially have a differing number of columns each time it is created.

Therefore, I'm trying to write each row of the table in it's entirety to the cursor and then create the flat file.

It's this that is causing me problems - is there an easy way of writing out a table to a flat file when the column structure of a table changes?

Hope my explanantion makes sense.

 

[SimonSellick]

I wrote this for a similar task, which you could adapt to fit your application (sorry it's a bit wide and long):

create or replace package body extracts is
-----------------------------------------------------------------------------------------------
/* extracts.pkb - package body to dump data to files.
-----------------------------------------------------------------------------------------------
   Procedure extract_table dumps one table into the requested directory on the dbms server.

   All exceptions are thrown back to the caller.

   The file created is as per spec:

   - pipe-separated, including a trailing '|' after the last column
   - IF NO DATA, NO FILE!  This is as specified, but easily changed if required
   - if split column is specified, one file is created for each value in that column
   - if not split, the file is named as requested, or by default, <p_table>.txt
   - if split, each file is named as above, prefixed with the split column value and '_'
   - column headings can be requested via the p_headings argument

   LIMITATIONS:
   - only tables can be dumped; this hasn't been tested with views
   - the table must have at least two columns, or the order by will fail
   - if split, the p_split_by column must be alpha and contain no null values
-----------------------------------------------------------------------------------------------
*/

-----------------------------------------------------------------------------------------------

  procedure extract_table(                                      -- DUMP ONE TABLE
    p_table cols.table_name%type                                --  table name
  , p_dir varchar2                                              --  utl_dir location to put it
  , p_file varchar2 := null                                     --  file name required
  , p_headings boolean := false                                 --  column headings required
  , p_split_by cols.column_name%type := null                    --  one file per value if not null
  ) is
    
  cursor table_columns is                                       -- column specs in table order
    select column_name, data_type
    from cols
    where table_name = upper(p_table)
    order by column_id;

  b varchar2( 2000);                                            -- break processing
  d varchar2( 1000);                                            -- declarations
  f varchar2( 1000);                                            -- output file
  h varchar2( 3000);                                            -- column headings
  o varchar2( 3000);                                            -- output line
  p varchar2(13000);                                            -- whole procedure
  q varchar2( 1000);                                            -- query
  t varchar2( 2000);                                            -- trailer processing

  begin

    d := 'declare';                                             -- build declarations
    d := d || ' fh utl_file.file_type;';                        -- file handle
    d := d || ' fn varchar2(100);';                             -- file name
    d := d || ' nr number(9) := 0;';                            -- number of records processed
    d := d || ' sv varchar2(50) := ''.'';';                     -- split_by column saved value
    
    q :=      'select * from ' || p_table;                      -- build main query
    q := q || ' order by ';
    if p_split_by is not null then
      q := q || p_split_by || ', ';                             -- if split, split_by column must be first
    end if;
    q := q || '1, 2';                                           -- mprn, msn or mprn, primary supplier attribute
    
    f := nvl(p_file, upper(p_table) || '.txt');	                -- build constant part of filename

    o := '';                                                    -- build output line..
    h := '';                                                    -- ..and column headings
    for c in table_columns loop
      if length(o) > 0 then
        o := o || ' || ';
      end if;
      if c.data_type = 'DATE' then
        o := o || 'to_char(r.' || c.column_name || ', ''DD-MON-YYYY'')';
      else
        o := o || 'r.' || c.column_name;
      end if;
      o := o || ' || ''|''';
      if p_headings then
        h := h || c.column_name || '|';
      end if;
    end loop;

    if p_headings then                                          -- finish column headings
      h := 'utl_file.put_line(fh, ''' || h || ''');';
    end if;
    
    if p_split_by is not null then                              -- build break handling, incl file open/close
      b :=      ' if r.' || p_split_by || ' != sv then';
      b := b || '  sv := r.' || p_split_by || ';';
      b := b || '  if nr > 1 then';
      b := b || '   utl_file.fclose(fh);';
      b := b || '  end if;';
      b := b || '  fn := sv || ''_'' || ''' || f || ''';';
      b := b || '  fh := utl_file.fopen(''' || p_dir || ''', fn, ''w'', 10000);';
      b := b ||    h;
      b := b || ' end if;';
    else                                                        -- just one file
      b :=      ' if nr = 1 then';
      b := b || '  fn := ''' || f || ''';';
      b := b || '  fh := utl_file.fopen(''' || p_dir || ''', fn, ''w'', 10000);';
      b := b ||    h;
      b := b || ' end if;';
    end if;

    t :=      ' if nr > 0 then';                                -- build trailer block
    t := t || '  utl_file.fclose(fh);';
    t := t || ' end if;';

    p := d;                                                     -- assemble entire procedure
    p := p || ' begin';
    p := p || '  for r in (' || q || ') loop';
    p := p || '   nr := nr + 1;';
    p := p ||     b;
    p := p || '   utl_file.put_line(fh, ' || o || ');';
    p := p || '  end loop;';
    p := p ||    t;
    p := p || ' end;';

    execute immediate p;

  end extract_table;

-----------------------------------------------------------------------------------------------

end extracts;

/

I have changed a few things to protect the innocent, so apologies if it won't immediately compile.

 

[SantaMufasa]

David,

Must the code you use to produce a flat file of a table's contents be in PL/SQL, or can the code be in plain SQL? I have a simple SQL script (a single SELECT) that produces a flat-file with Comma Separated Values (.csv) of any table you choose. If your code must be in PL/SQL, I could easily modify the script to run as a PL/SQL block, as well. Let us know. (Also, I've widened the screen display for this thread to (hopefully) make Simon's code easier to read by eliminating the line wraps.)
[tt]
***********************************************************************************************[/TT]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Oops...The "widener" needs to be a tad wider.
[tt]
****************************************************************************************************************
[/tt]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.