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?
JoalkatAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.