Solved

Create a file from a select

Posted on 2013-06-19
4
445 Views
Last Modified: 2013-06-19
HI experts, I need to create a file called puntos.txt from a select:
SELECT tag, pointnumber
FROM xpuntos_analog
WHERE estacion = 33;

Open in new window

Must be create a file puntos.txt in:
/cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_SUR_5MIN/SCRIPTS

Open in new window

The file puntos.txt must be contains columns between '||'
SUR_3AT1_2____I || 330061
SUR_3B_1______V || 330036
SUR_3B_2______V || 330037
SUR_3C_CEN____I || 330004
SUR_3C_CEN____P || 330005
SUR_3C_CEN____Q || 330006
SUR_3C_CEN____V || 330057
SUR_3ET_______P || 330096
SUR_3ET_______Q || 330097
SUR_3ET______FP || 330098
SUR_3L_ALV____I || 330011
SUR_3L_ALV____P || 330012
SUR_3L_ALV____Q || 330013
SUR_3L_GMO____I || 330008
SUR_3L_GMO____P || 330009
SUR_3L_GMO____Q || 330010
SUR_3L_GMO____V || 330059
SUR_3L_ROE2___I || 330003
SUR_3L_ROE2___P || 330034
SUR_3L_ROE2___Q || 330035
SUR_3L_ROE2___V || 330047
SUR_3TF1______I || 330042
SUR_3TF1______P || 330043
SUR_3TF1______Q || 330044
SUR_3TF1______R || 330046
SUR_3TF2______I || 330014

Open in new window


I tried with:
UTL_FILE.PUT_LINE

Open in new window

without success
Could you send me an examples?
Thankyou!
0
Comment
Question by:carlino70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 250 total points
ID: 39260915
Try sql*plus:
set echo off term off ver off pages 0 lin 100 trims on
def path='/cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_SUR_5MIN/SCRIPTS'
spo &&path./puntos.txt
SELECT tag || '||' || pointnumber
  FROM xpuntos_analog
 WHERE estacion = 33;
spo off

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39260918
CREATE OR REPLACE DIRECTORY SCRIPTS_DIR AS '/cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_SUR_5MIN/SCRIPTS';

DECLARE
    v_file UTL_FILE.file_type;
BEGIN
    v_file :=
        UTL_FILE.fopen(
            'SCRIPTS_DIR',
            'puntos.txt',
            'w',
            32767
        );

    FOR x IN (SELECT tag, pointnumber
                FROM xpuntos_analog
               WHERE estacion = 33)
    LOOP
        BEGIN
            UTL_FILE.put_line(v_file, x.tag || ',' || x.pointnumber);
        END;
    END LOOP;

    UTL_FILE.fflush(v_file);
    UTL_FILE.fclose(v_file);
END;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 39260923
alternatively, if your directory path is part of the init parameter utl_file_dir

you could use



CREATE OR REPLACE DIRECTORY SCRIPTS_DIR AS '/cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_SUR_5MIN/SCRIPTS';

DECLARE
    v_file UTL_FILE.file_type;
BEGIN
    v_file :=
        UTL_FILE.fopen(
            '/cots/oracle/TABLAS_HISTORICOS/VOLCADO_DATOS/TEST_SUR/DATOS_SUR_5MIN/SCRIPTS',
            'puntos.txt',
            'w',
            32767
        );

    FOR x IN (SELECT tag, pointnumber
                FROM xpuntos_analog
               WHERE estacion = 33)
    LOOP
        BEGIN
            UTL_FILE.put_line(v_file, x.tag || ',' || x.pointnumber);
        END;
    END LOOP;

    UTL_FILE.fflush(v_file);
    UTL_FILE.fclose(v_file);
END;



without needing to precreate the directory object.

however,  I recommend going the directory object route anyway.
They are more secure and more versatile
0
 

Author Closing Comment

by:carlino70
ID: 39260966
Excellent both solutions.

Thank you very much!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ER Diagram 3 52
error in my cursor 5 60
Oracle Distributed Transaction Lock Error ORA-01591 8 86
Age Calculation from specific date 19 55
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question