Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

How do you create a .dat file from Oracle 10g? What is the best way to do so?

I'm trying to export large flat files out of Oracle into a .dat format.  Does anyone have a nice generic script for doing so?
0
Joalkat
Asked:
Joalkat
  • 3
  • 2
1 Solution
 
sdstuberCommented:
what format are you calling ".dat"

here's a function that will take an arbitrary query and write the results to a csv file
FUNCTION dump_sql(
    p_query          IN   VARCHAR2
   ,p_filename       IN   VARCHAR2
   ,p_dir            IN   VARCHAR2 DEFAULT dflt.utl_dir
   ,p_max_linesize   IN   NUMBER DEFAULT 32000)
    RETURN NUMBER IS
    l_output        UTL_FILE.file_type;
    l_thecursor     INTEGER            DEFAULT DBMS_SQL.open_cursor;
    l_columnvalue   VARCHAR2(4000);
    l_status        INTEGER;
    l_colcnt        NUMBER             DEFAULT 0;
    l_cnt           NUMBER             DEFAULT 0;
    l_line          LONG;
    l_desctbl       DBMS_SQL.desc_tab2;
  BEGIN
    l_output := UTL_FILE.fopen(p_dir, p_filename, 'w', p_max_linesize);
    DBMS_SQL.parse(l_thecursor, p_query, DBMS_SQL.native);
    DBMS_SQL.describe_columns2(l_thecursor, l_colcnt, l_desctbl);
 
    FOR i IN 1 .. l_colcnt
    LOOP
      DBMS_SQL.define_column(l_thecursor, i, l_columnvalue, 4000);
 
      IF (l_desctbl(i).col_type = 2)                                 
            /* number type */
                                    THEN
        l_desctbl(i).col_max_len := l_desctbl(i).col_precision + 2;
      ELSIF(l_desctbl(i).col_type = 12)                              
               /* date type */
                                       THEN
        /* length of my date format */
        l_desctbl(i).col_max_len := 20;
      ELSIF(l_desctbl(i).col_type = 8)                               
               /* LONG type */
                                      THEN
        l_desctbl(i).col_max_len := 2000;
      END IF;
    END LOOP;
 
    l_status := DBMS_SQL.EXECUTE(l_thecursor);
 
    LOOP
      EXIT WHEN(DBMS_SQL.fetch_rows(l_thecursor) <= 0);
      l_line := NULL;
 
      FOR i IN 1 .. l_colcnt
      LOOP
        DBMS_SQL.COLUMN_VALUE(l_thecursor, i, l_columnvalue);
        l_line := l_line || RPAD(NVL(l_columnvalue, ' '),
l_desctbl(i).col_max_len);
      END LOOP;
 
      UTL_FILE.put_line(l_output, l_line);
      l_cnt := l_cnt + 1;
    END LOOP;
 
    DBMS_SQL.close_cursor(l_thecursor);
    UTL_FILE.fclose(l_output);
    RETURN l_cnt;
  END dump_sql;

Open in new window

0
 
JoalkatAuthor Commented:
Thanks a lot.  I appreciate it.
0
 
JoalkatAuthor Commented:
You can close.
0
 
sdstuberCommented:
Since you're not really splitting, you can simply accept the answer yourself.
0
 
sdstuberCommented:
glad I could help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now