What exactly are you doing with these text files? Are you reading from and writing to text files? Or are you just reading from text files? Or are you just trying to run scripts?


Andrew

To load text files into an Oracle database, the utility SQL*Loader is usually the fastest method.  If the tables do not exist, you need to create them first.  You also need to write a control file for each data file and/or table.

To create text files from data in Oracle, you can either use SQL*Plus and "spool" the output of a select statement, or you can use a PL\SQL procedure that uses utl_file to write to a text file.  For utl_file to work you need to have at least one "utl_file_dir" entry in your init*.ora file (or spfile).  Utl_file can also read from text files to insert and/or update data in Oracle.

If these suggestion do not get you what you were looking for, then please describe your question in more detail.  

Since you have 9i, you can also create an "external table" that points to the text file and just query it like you would any table.  If you want to perform more specialized writing to a file, then you have to use UTL_FILE. That is the reason why I was asking.


Andrew

I need to write to the text file any information not related to any table. I know How but how can i set up oracle to be able to work with text file.

After reading the very detailed copy-paste from OracleTune on External tables, you can also check out this URL on technet - this one takes the UTL_FILE approach suggested by Andrew earlier on. You need a technet account but it is free:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76936/utl_file.htm#998101

If you already know the syntaxes and usage, do note that you need to set UTL_FILE_DIR=* or UTL_FILE_DIR=<your directory>, since only the specified directories will be allowed for file I/O. Once you set this parameter in your init<sid>.ora you will need to restart the instance. Make sure that the directories are permissioned to the o/s account that Oracle is running as

sora

The standard solution is to use the package UTL_FILE as described in details below.

REMARK
Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed.
Accessible directories must be specified in the
instance parameter initialization file (INIT.ORA).

Specify the accessible directories for the UTL_FILE
functions in the initialization file
using the UTL_FILE_DIR parameter.
For example:
UTL_FILE_DIR = <directory name>
like in
UTL_FILE_DIR = C:\DATA\ORACLE\DATAFILES
------
Running UTL_FILE on Windows NT
Overview
========

From release 7.3 file input/output capabilities were added to PL/SQL on the
server. This is achieved through the supplied package UTL_FILE, generic
documentation on this feature can be found in :-

 - Oracle Server Application Developers Guide (8-28 to 8-40)
 - Note:44307.1 Using the UTL_FILE package
 - Note:33755.1 PLSQL Package: UTL_FILE (7.3)

The following text covers how to setup directories for access by the UTL_FILE
package on windows NT 3.51 and 4.0.


Directory Names
===============

For UTL_FILE's procedures to access server directories the directories to
be accessed must be specified in the init.ora file, for example :

UTL_FILE_DIR=E:\LOGDIR

Quotes and a trailing \ are not necessary.


1) Long filenames or directory names with mixed case
----------------------------------------------------

For this to work the directory specified for use in the UTL_FILE procedures
must exactly match (including case) that specified in UTL_FILE_DIR. Although
the UTL_FILE_DIR init.ora parameter does not have to exactly match the actual
directories case.

e.g. UTL_FILE_DIR=E:\LogDirectory  
     UTL_FILE.FOPEN('E:\LogDirectory','logfile.log','w');


2) Directories with space characters
------------------------------------

For this to work the directory must be delimited by double quotes in the
init.ora parameter file.

e.g. UTL_FILE_DIR="E:\LOG DIR".


3) Making multiple directories available
----------------------------------------

To specify multiple directories have a separate UTL_FILE_DIR line for each
directory, but make sure these are on consecutive lines otherwise only the
last directory will be accessible. Alternatively separate each directory
with a space on a single UTL_FILE_DIR line.


Directory Permissions
=====================

Directories on FAT partitions
-----------------------------

FAT does not support file or directory permissions.

Directories on NTFS partitions
------------------------------

When an oracle instance is created the services that support it are setup
to Log On As the SYSTEM (or operating system) account. For UTL_FILE's
procedures to access directories that do not have access granted to
everyone they must have "Change" privileges granted to the SYSTEM account.
If the OracleServiceXXXX service has been altered to log on as another
account, it is this account that must be given access to the directories.

Accessing Network Directories
=============================

Netware Networked Directories
-----------------------------

For this to work ensure that the account that started the Oracle instance
has a mapped network drive connected using a fully qualified username/password
with the same drive letter and path as is specified by UTL_FILE_DIR.

NT Networked Directories
------------------------

When an oracle instance is created the services that support it are setup
to "Log On As" the SYSTEM (or operating system) account, this account has
very few privileges and no access to NT Domains. To access another NT machine
the OracleServiceXXXX must be setup to logon to the appropriate NT Domain as
a user who has access to the required location for UTL_FILE.

To change the default logon for the Oracle services, go to :

  -> Control Panel
     -> Services
        -> OracleServiceXXXX (where XXXX is the instance name)
           -> Startup
              -> Log On As

Choose the "This Account" radio button, then complete the appropriate
domain login information. Repeat this procedure for the TNS Listener :

  -> Control Panel
     -> Services
        -> OracleXXXXTNSListener (where XXXX is the Oracle Home Name)
           -> Startup
              -> Log On As

If the listener is not configured to start as the same user as the Oracle
Service user will fail to connect with "ORA-12500 TNS:listener failed to start
a dedicated server process". Once the services have been setup as a user with
the appropriate privileges the are two options for setting UTL_FILE_DIR :

a. Mapped Drive : To use a mapped drive, the user that the service starts as
must have setup a drive to match UTL_FILE_DIR and be logged onto the server
when UTL_FILE is in use.

b. Universal Naming Convention : UNC is preferable to Mapped Drives because
it does not require anyone to be logged on and UTL_FILE_DIR should be set to
a name in the form :

  \\\<machine name>\<share name>\<path>

  or

  "\\<machine name>\<share name>\<path>"

-- ************************************************************
-- CSV_INVOICES.PRC   Procedure to create text file for an
--                    invoice
--
-- Inputs:   BGWAPP_INVOICE
--           BGWAPP_INVOICE_LINE
--           BGWAPP_CUSTOMER
--           BGWAPP_PRODUCT
--           BGWAPP_ORDER
--
-- Parameters:  in_sequence     - file number
--              in_file_name    - file name
--              in_customer_id  - Customer Number
--              in_invoice_id   - Invoice Number
--              in_start_date   - Date Range - Start Date
--              in_end_date     - Date Range - End   Date
--
-- There will be headings at the top
--
-- Invoice Header information
--     Invoice #, Invoice Date, Customer #,
--     Customer Name, Order #, Order Date, Status,
--     Total Lines, Total Amount
--
-- Invoice Detail information
--     Invoice Line #, Product #, Description, Quantity,
--     Unit of Measure, Unit Price, Amount, Status
-- ************************************************************


PROCEDURE CSV_INVOICES
   (in_sequence      IN NUMBER,
    in_file_name     IN VARCHAR2,
    in_customer_id   IN NUMBER,
    in_invoice_id    IN NUMBER,
    in_start_date    IN DATE,
    in_end_date      IN DATE)
is

out_file UTL_FILE.FILE_TYPE;
out_counter   INTEGER := 0;
out_line      INTEGER := 0;
v_invoice_id  number(8);
v_file_name   varchar2(40);
out_record    varchar2(2000);

CURSOR crs_cust_inv_date (c_cust varchar2, c_inv number, c_start date, c_end date) IS
   SELECT a.customer_id,
          a.invoice_id,
          a.invoice_date,
          a.order_id,
          d.order_date,
          c.name,
          a.status,
          COUNT(*) total_lines,
          SUM(b.amount) total_amount
   FROM   bgwapp_order         d,
          bgwapp_customer      c,
          bgwapp_invoice_line  b,
          bgwapp_invoice       a
   WHERE  a.customer_id = nvl(c_cust,a.customer_id)
   AND    a.invoice_id  = nvl(c_inv,a.invoice_id)
   AND    a.invoice_date between
              nvl(c_start,a.invoice_date) and
              nvl(c_end,a.invoice_date)
   AND    a.invoice_id  = b.invoice_id
   AND    a.customer_id = c.customer_id
   AND    a.order_id    = d.order_id(+)
   GROUP BY
          a.customer_id,
          a.invoice_id,
          a.invoice_date,
          a.order_id,
          d.order_date,
          c.name,
          a.status;

CURSOR crs_invoice_detail IS
   SELECT a.invoice_id,
          a.invoice_line_id,
          a.product_id,
          b.name,
          a.quantity_invoiced,
          a.unit_of_measure,
          a.unit_price,
          a.amount,
          a.status
   FROM   bgwapp_product b,
          bgwapp_invoice_line a
   WHERE  a.invoice_id = v_invoice_id
     and  a.product_id = b.product_id;

BEGIN

 BEGIN
    select machine_port, directory, app_identifier, file_location
      into c_mach_port, c_dir, v_identifier, v_location
      from ediapp_control;
 EXCEPTION
   when OTHERS then
      htp.p('Error in accessing EDIAPP_CONTROL!! - '||SQLERRM);
      goto skip;
 END;

--
-- Open Output File
--
       
 v_file_name := in_file_name;

 out_file := UTL_FILE.FOPEN ( v_location, v_file_name, 'W' );

 out_record := '"File Name","'||v_file_name||'",,,,,,,,,,,,,,,," "';
 UTL_FILE.PUT_LINE ( out_file, out_record );

 out_record := '" ",,,,,,,,,,,,,,,,," "';
 UTL_FILE.PUT_LINE ( out_file, out_record );


 out_record := '"INVOICE #","INVOICE DATE","CUSTOMER #","NAME","ORDER #","ORDER DATE",'||
               '"STATUS","TOTAL LINES","TOTAL AMOUNT",'||
               '"INVOICE LINE #","PRODUCT #","PRODUCT NAME","QTY","UOM",'||
               '"UNIT PRICE","AMOUNT","STATUS"';
 UTL_FILE.PUT_LINE ( out_file, out_record );

 out_record := '" ",,,,,,,,,,,,,,,,," "';
 UTL_FILE.PUT_LINE ( out_file, out_record );

 For crs_invoice in crs_cust_inv_date
        (in_customer_id, in_invoice_id, in_start_date, in_end_date)
        LOOP

--
-- INVOICE HEADER / DETAIL Output Line
--

     v_invoice_id := crs_invoice.invoice_id;
     out_counter  := out_counter + 1;
     out_line     := 0;      

     For crs_detail in crs_invoice_detail LOOP

        out_record := null;
        out_line   := out_line + 1;
        out_record := to_char(crs_invoice.invoice_id) ||',"'||
                      to_char(crs_invoice.invoice_date,'YYYY-MM-DD') ||'",'||
                      to_char(crs_invoice.customer_id) ||',"'||
                      crs_invoice.name ||'",'||
                      to_char(crs_invoice.order_id) ||',"'||
                      to_char(crs_invoice.order_date,'YYYY-MM-DD') ||'","'||
                      crs_invoice.status ||'",'||
                      to_char(crs_invoice.total_lines) ||','||
                      to_char(crs_invoice.total_amount)||','||
                      to_char(out_line) ||','||
                      to_char(crs_detail.product_id) ||',"'||
                      crs_detail.name ||'",'||
                      to_char(crs_detail.quantity_invoiced) ||',"'||
                      crs_detail.unit_of_measure ||'",'||
                      to_char(crs_detail.unit_price) ||','||
                      to_char(crs_detail.amount) ||',"'||
                      crs_detail.status ||'"';
        UTL_FILE.PUT_LINE ( out_file, out_record );
     END LOOP;
 END LOOP;
 UTL_FILE.FCLOSE ( out_file );
 DBMS_OUTPUT.PUT_LINE ( 'Total Invoices:  '||to_char(out_counter) );

<< skip >>
 null;

EXCEPTION
 when UTL_FILE.INVALID_PATH then
      DBMS_OUTPUT.PUT_LINE('Invalid Path:  '||v_location);
      htp.p('Invalid Path:  '||v_location);
 when UTL_FILE.INVALID_MODE then
      DBMS_OUTPUT.PUT_LINE('Invalid Mode:  '||SQLERRM);
      htp.p('Invalid Mode:  '||SQLERRM);
 when UTL_FILE.INVALID_FILEHANDLE then
      DBMS_OUTPUT.PUT_LINE('Invalid File Handle: '||v_file_name);
      htp.p('Invalid File Handle: '||v_file_name);
 when UTL_FILE.INVALID_OPERATION then
      DBMS_OUTPUT.PUT_LINE('Invalid Operation: '||SQLERRM);
      htp.p('Invalid Operation: '||SQLERRM);
 when UTL_FILE.READ_ERROR then
      DBMS_OUTPUT.PUT_LINE('Read Error: '||SQLERRM);
      htp.p('Read Error: '||SQLERRM);
 when UTL_FILE.WRITE_ERROR then
      DBMS_OUTPUT.PUT_LINE('Write Error: '||SQLERRM);
      htp.p('Write Error: '||SQLERRM);
 when UTL_FILE.INTERNAL_ERROR then
      DBMS_OUTPUT.PUT_LINE('Internal Error: '||SQLERRM);
      htp.p('Internal Error: '||SQLERRM);
 when OTHERS then
      DBMS_OUTPUT.PUT_LINE('Other '||SQLERRM);
      htp.p('Other '||SQLERRM);
END CSV_INVOICES;

... in the INIT.ORA, here is the exact statement:

UTL_FILE_DIR = C:\DATA\ORACLE\DATAFILES

Hope you can use this...
--------------------------------------------------------------------
I am writing a simple sample procedure:

CREATE OR REPLACE
Procedure     TEST_CSV IS

tst_file UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN('F:\UTL_FILE_DIR', 'TESTCSV.CSV', 'w');
invalid_path exception;
invalid_mode exception;
invalid_filehandle exception;
invalid_operation exception;
read_error exception;
write_error exception;
internal_error exception;

cursor cur_csv IS
select replace(tname, ',','')||','||  -- Use replace to
      replace(tabtype,',','')||','   -- get rid of commas
      replace(clusterid,',','')||',' ln -- in text fields.
FROM tab;

rec_csv cur_csv%rowtype;


BEGIN

for rec_csv in cur_csv loop
   utl_file.put_line(tst_file, rec_csv.ln);
end loop;

   utl_file.fclose(tst_file);
EXCEPTION
   when invalid_path then
      dbms_output.put_line('invalid path: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when invalid_mode then
      dbms_output.put_line('invalid mode: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when invalid_filehandle then
      dbms_output.put_line('invalid filehandle: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when invalid_operation then
      dbms_output.put_line('invalid operation: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when read_error then
      dbms_output.put_line('invalid read error: '||to_char(sqlcode)||' - '||sqlerrm) ;
  when write_error then
      dbms_output.put_line('write error: '||to_char(sqlcode)||' - '||sqlerrm) ;
   when internal_error then
      dbms_output.put_line('internal_error: '||to_char(sqlcode)||' - '||sqlerrm) ;
   WHEN others THEN
      dbms_output.put_line('Error Code: '||to_char(sqlcode)||' - '||sqlerrm) ;
END; -- Procedure TEST_UTL
/

In addition to the UTL_FILE approach (see below URL for more info(

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76936/utl_file.htm#998101

you can also use External routines if you feel UTL_FILE doesn't serve your needs. Examples of External routines are C programs that are callable from within PL/SQL.

On how to work with them, see below:

http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/appdev.901/a88876/adg11rtn.htm#1656


sora

"How can I set up oracle to be able to work with text file?"  The comments you've gotten already describe the most common ways of working with text files in Oracle (utl_file, external procedures, SQL*Loader, etc.).  Oracle9i also supports working with XML files.

What exactly do you want Oracle to do with text files: create them from data in Oracle?  read them to load data into Oracle? something else?  Please explain how you want to use text files with Oracle.

This question is current, others below are not and have had previous requests to you to update and finalize them.  Please do.  ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if they are still open in 14 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20148843.html
https://www.experts-exchange.com/questions/Q.20248307.html
https://www.experts-exchange.com/questions/Q.12047939.html
https://www.experts-exchange.com/questions/Q.20288189.html
https://www.experts-exchange.com/questions/Q.20286654.html
https://www.experts-exchange.com/questions/Q.20294143.html
https://www.experts-exchange.com/questions/Q.20294144.html

To view your locked questions, please click the following link(s) and evaluate the proposed answer.
https://www.experts-exchange.com/questions/Q.20177545.html
https://www.experts-exchange.com/questions/Q.20166217.html

*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643 
POINTS FOR EXPERTS awaiting comments are listed in the link below
https://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @14 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange

Sorry for wating

Thank you for returning and finalizing this.
:) Moondancer - EE Moderator