How to write into a file apart from UTL_FILE

Question

I have written a program to write a file into a directory using utl_file.

sample script :

PROCEDURE XX_FILE_TRANS (X_RETCODE VARCHAR2,X_ERRBUF VARCHAR2)
IS

CURSOR ORG_CUR IS
SELECT
'"'||XTRO.IDENTIFIER||'","'||
XTRO.ORGANIZATION_CODE||'","'||
XTRO.NAME ||'","'||
XTRO.PARENT_CODE ||'","'||
XTRO.INDUSTARY_NUMBER ||'","'||
XTRO.STATUS||'","'||
XTRO.SEQUENCE||'"' "ORG_DATA"
FROM abc XTRO;


ORG_REC ORG_CUR%rowtype;

begin
begin
         delete XXHCM.XXHR_TAL_REC_ORGANIZATION_hist
    where     1 = 1
          AND creation_date < TRUNC (SYSDATE - 60);

         COMMIT;
      EXCEPTION
         when OTHERS
         then
            null;
      end;


LOAD_archive_TABLE; -- call the history table procedure
DBMS_OUTPUT.PUT_LINE('start');
  fileHandler := UTL_FILE.FOPEN('INTF_DIR_INBOUND', LC_OLF_ORG, 'W');
  UTL_FILE.put_line(fileHandler, 'Identifier,OrgCode,OrgName,ParentCode,IndustryNumber,StatusDescription,Sequence');


  FOR ORG_REC IN ORG_CUR
  LOOP
             UTL_FILE.put_line(fileHandler,ORG_REC.ORG_DATA);
  END LOOP;

  UTL_FILE.FCLOSE(fileHandler);
dbms_output.put_line('end');
EXCEPTION
 WHEN UTL_FILE.INVALID_PATH THEN
           FND_FILE.PUT_LINE(FND_FILE.LOG,'    Invalid File Path');
            UTL_FILE.FCLOSE(fileHandler);
            dbms_output.put_line('1'||sqlerrm);
         WHEN UTL_FILE.WRITE_ERROR THEN
           FND_FILE.PUT_LINE(FND_FILE.LOG,'    Write Permission on  does not exist');
            UTL_FILE.FCLOSE(fileHandler);
            dbms_output.put_line('2'||sqlerrm);
         WHEN UTL_FILE.INVALID_MODE THEN
            FND_FILE.PUT(FND_FILE.LOG,'THE INVALID MODE OF DATA FILE');
            UTL_FILE.FCLOSE(fileHandler);
            dbms_output.put_line('3'||sqlerrm);
         WHEN UTL_FILE.INVALID_OPERATION THEN
            FND_FILE.PUT(FND_FILE.LOG,' THE FILE CANNOT BE OPENED AS REQUESTED.');
            UTL_FILE.FCLOSE(fileHandler);
            dbms_output.put_line('4'||sqlerrm);
         WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
            FND_FILE.PUT(FND_FILE.LOG,'THE SPECIFIED MAXIMUM LINE SIZE IS TOO LARGE OR TOO SMALL.');
            UTL_FILE.FCLOSE(fileHandler);
            dbms_output.put_line('5'||sqlerrm);
         WHEN UTL_FILE.ACCESS_DENIED THEN
            FND_FILE.PUT(FND_FILE.LOG,'ACCESS TO THE DIRECTORY OBJECT IS DENIED.');
            FND_FILE.PUT(FND_FILE.LOG,'OPERATING SYSTEM ERROR OCCURED DURING THE WRITE OPERATION.');
            UTL_FILE.FCLOSE(fileHandler);
            dbms_output.put_line('6'||sqlerrm);
         WHEN UTL_FILE.CHARSETMISMATCH  THEN
            FND_FILE.PUT(FND_FILE.OUTPUT,'THIS FILE IS OPEN FOR NCHAR DATA.');
            UTL_FILE.FCLOSE(fileHandler);
            dbms_output.put_line('7'||sqlerrm);
END;

Now the server i have is not db server because of whch i cannot use UTL_FILE. Is thee any another way than UTL_FILE to write into a file ?

How can't it be a db server? Where are you trying to do that? Are you using oracle forms perhaps? — pablomatico, Jul 01 '16 at 08:56
and where are you executing this pl/sql procedure? if you're using oracle forms, you can use the `TEXT_IO` package — pablomatico, Jul 04 '16 at 05:35
No.. its a pl/sql package. Bascially i will create a concurent program which will generate thefile from a stage table — sreekem bose, Jul 04 '16 at 05:42
`UTL_FILE` is the most common way to do this. If you want an alternative it would help to explain *why* you need an alternative. For example, is `UTIL_FILE` simply unavailable because some database hardening script revoked it from PUBLIC? If so, you can ask a DBA to grant it to your user or role. — Jon Heller, Jul 05 '16 at 16:41

score 0 · Answer 1 · answered Jul 01 '16 at 14:05

0

You can create an EXTERNAL TABLE instead...

https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm

answered Jul 01 '16 at 14:05

abrittaf

537
5
11

External table puts the data into the oracle db. I want to generate a file at a location from an oracle db – sreekem bose Jul 02 '16 at 08:50
If you are in version 10g+ you can use this technique using external tables in order to generate data from oracle db avoiding spooling or url_file. Take a look at this http://www.oracle-developer.net/display.php?id=325. – abrittaf Jul 02 '16 at 12:44
AFAIK, you cannot write text to external files? – wolφi Jul 05 '18 at 08:24
Since 10g et are rw – abrittaf Jul 05 '18 at 11:03

score 0 · Answer 2 · answered Jul 01 '16 at 14:47

Why not use sqlplus? Create a simple .sql file (my_script.sql) containing your SQL, with a few SET commands, something like:

SET FEEDBACK OFF;
SET ECHO OFF;
SET TERM OFF;
SET serveroutput on size unlimited;
SET linesize 30000
SET pagesize 0
SET head off
SET trims on
SET verify off

WHENEVER SQLERROR EXIT SQL.SQLCODE
SPOOL my_file.csv;

select 
'MY_NUMBER_FIELD1,'||
'MY_VARCHAR_FIELD2,'||
'MY_VARCHAR_FIELD3'
from dual
UNION ALL
select
COL1||','||
'"'||replace(COL2, '"','""')||'",'||
'"'||replace(COL3, '"','""')||'"'
from MY_TABLE
/

Modify the SQL as needed. Place that sql file in a directory on a server of your choice, login to Oracle from that directory using sqlplus, and run:

@my_script.sql

The output file (my_file.csv) will be in the directory you logged in from. A sqlplus login script can be used to automate things as well (along with cron or task scheduler or other scheduling software).

in that case, you should write a program in the language of your choice (java, C#, whatever you know and works in your environment) and pull the data. If you know the basics of the language its not a difficult task (and plenty of examples around). Use an existing CSV library, don't roll your own. — tbone, Jul 04 '16 at 11:59

How to write into a file apart from UTL_FILE

2 Answers2