Solved

Create a file from a select

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

Industry Leaders: 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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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, 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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

707 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