Scribd Logo
Upload

Welcome to Scribd!

  • 673 views

    Create ASCII Files Using Utl - File Package

    Uploaded by

    vrbala
    This function takes a SQL query as input, executes it, and dumps the results to a CSV file. It opens a file, executes the query, fetches the results row by row, writes each row to the file with columns separated by a provided delimiter, closes the file, and returns the number of rows dumped. The function can be called by passing a query, delimiter, directory, and filename.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as TXT, PDF, TXT or read online from Scribd

    Create ASCII Files Using Utl - File Package

    Uploaded by

    vrbala
    673 views2 pages
    This function takes a SQL query as input, executes it, and dumps the results to a CSV file. It opens a file, executes the query, fetches the results row by row, writes each row to the file with columns separated by a provided delimiter, closes the file, and returns the number of rows dumped. The function can be called by passing a query, delimiter, directory, and filename.

    Original Description:

    Create ASCII files using utl_file package

    Original Title

    Create ASCII files using utl_file package

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This function takes a SQL query as input, executes it, and dumps the results to a CSV file. It opens a file, executes the query, fetches the results row by row, writes each row to the file with columns separated by a provided delimiter, closes the file, and returns the number of rows dumped. The function can be called by passing a query, delimiter, directory, and filename.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    673 views2 pages

    Create ASCII Files Using Utl - File Package

    Uploaded by

    vrbala
    This function takes a SQL query as input, executes it, and dumps the results to a CSV file. It opens a file, executes the query, fetches the results row by row, writes each row to the file with columns separated by a provided delimiter, closes the file, and returns the number of rows dumped. The function can be called by passing a query, delimiter, directory, and filename.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 2

    create or replace function dump_csv(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;
    dbms_application_info.set_client_info(l_cnt || ' rows are generated') ;
    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose(l_output);
    return l_cnt;
    end dump_csv;

    -------------------------------------------------------------------------------
    Usage:
    -------------------------------------------------------------------------------

    declare
    l_rows number;
    begin
    l_rows := dump_csv( 'select * from dba_users',
    ',',
    '/home/demouser/output',
    'users.txt' );
    dbms_output.put_line( to_char(l_rows) ||
    ' rows extracted to ascii file' );
    end;

    You might also like