Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create a file from a select

Posted on 2013-06-19
4
Medium Priority
?
450 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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