Tags: database, file, mysql, oracle, select, sql, table, text

Select data into text file.

On Database » Oracle

4,179 words with 4 Comments; publish: Sun, 10 Feb 2008 03:15:00 GMT; (25046.88, « »)

How can I select data from table and put them into a text file.

All Comments

Leave a comment...

  • 4 Comments
    • quote:<HR>=== Original Words ===

      Angel Luo (luoangel.oracle.itags.org.cmmail.com):How can I select data from table and put them into a text file.<HR>

      you can do it by utl_file package. it has different methods by which you can open a text file and write to file. you can see this package under sys user.
      #1; Sat, 23 Feb 2008 23:47:00 GMT
    • Also, for ad hoc, you can simply spool an output file from SQLPlus.
      #2; Sat, 23 Feb 2008 23:48:00 GMT
    • I found this code in this forum awhile back from another user in response to the same question:

      CREATE OR REPLACE FUNCTION dump_to_csv(p_query IN VARCHAR2,

      p_dir IN VARCHAR2,

      p_filename IN VARCHAR2,

      p_separator IN VARCHAR2 DEFAULT ',')

      RETURN NUMBER

      /***********************************************************************************************************

      This function sends the recordset of an SQL query to a delimited text file, ie .csv, and returns the number

      of records selected. Incoming parameters include the query, directory where file resides, filename, and

      separator symbol, ie comma, tab, etc. (the default delimitor is a comma). You can indicate write ('w') or

      append ('a') mode when opening the file at the first executable line of code. Following is an example of

      how to use this function:

      DECLARE

      l_rows NUMBER;

      BEGIN

      l_rows := dump_to_csv('select * from all_users where rownum < 5',

      'E:\KBZstuff\docs\sql\ImportDataFiles\SampleLoad\sample_datafiles',

      'csv_test_dump.csv');

      DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_rows)| |'rows extracted to ascii file');

      END;

      /

      --output on screen:

      4 rows extracted to ascii file

      PL/SQL procedure successfully completed.

      --output to csv_test_dump.csv text file:

      SYS,0,20-APR-99

      SYSTEM,5,20-APR-99

      OUTLN,11,20-APR-99

      DBSNMP,18,20-APR-99

      **********************************************************************************************************/

      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;

      EXCEPTION

      WHEN UTL_FILE.INVALID_PATH THEN

      DBMS_OUTPUT.PUT_LINE('You have invalid path');

      WHEN UTL_FILE.INVALID_MODE THEN

      DBMS_OUTPUT.PUT_LINE('You have invalid mode');

      WHEN UTL_FILE.INVALID_OPERATION THEN

      DBMS_OUTPUT.PUT_LINE('You have invalid operation');

      WHEN UTL_FILE.INTERNAL_ERROR THEN

      DBMS_OUTPUT.PUT_LINE('You have interenal error');

      WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLERRM);

      END dump_to_csv;

      /

      show errors;

      null

      #3; Sat, 23 Feb 2008 23:49:00 GMT