?
Solved

Create a file from a select

Posted on 2013-06-19
4
Medium Priority
?
449 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 1000 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 1000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

777 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