Solved

Create a file from a select

Posted on 2013-06-19
4
442 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

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!

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

740 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