SPOOL Problem 2

[SeAL]

Hi all,

I want to create a text file using the SPOOL command. Here's my sample:

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET TERMOUT OFF
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET COLSEP ','

SPOOL toto.txt
select CODECLIENT, BRANCHE, LIBELLEBRANCHE from bytl.BT_CLIENT;
SPOOL OFF

SET TERMOUT ON
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON

And here's my results:

select CODECLIENT, BRANCHE, LIBELLEBRANCHE from bytl.BT_CLIENT;
0104000404,PRO ,RESEAU BOUTIQ. BYTL
0104000405,PRO ,RESEAU BOUTIQ. BYTL
0104000406,PRO ,RESEAU BOUTIQ. BYTL
0104000407,PRO ,RESEAU BOUTIQ. BYTL
SPOOL OFF

How can I take out the first and the last line (in red) 'cause I don't want it in my text file

 

[carp]

I don't believe you can. Spooling writes everything you see on your screen out to the file; consequently, once you type "spool off", it gets written to your file.

I think what you'll have to do is use a PL/SQL approach that involves using the UTL_FILE package to open, write to, and close your file.

 

[SeAL]

thanks carp but do you know where can I find a correct sample of using UTL_FILE?

 

[carp]

The following should give you some idea; it will write out a comma-delimited file for you. By changing what's concatenated in the 'utl_file.put_line' command, you can change the output to suit your needs:

DECLARE
v_file UTL_FILE.FILE_TYPE;
CURSOR my_cursor IS select CODECLIENT, BRANCHE, LIBELLEBRANCHE from bytl.BT_CLIENT;
BEGIN
-- OPEN THE FILE; PROVIDE THE PATH, FILE NAME, AND OPEN MODE <W FOR WRITE>
-- THE ALLOWABLE PATH IS DETERMINED BY THE PARAMETER UTL_FILE_DIR. IF THIS
-- PARAMETER IS NOT SET, YOU WON&quot;T BE ABLE TO USE UTL_FILE!!
v_file := utl_file.fopen('c:\my_dir','my_flat_file','W');

-- NOW OPEN THE CURSOR AND WRITE THE CONTENTS TO YOUR FILE
FOR i IN my_cursor LOOP
utl_file.put_line(v_file,i.codeclient||','||i.branche||','||i.libellebranche);
END LOOP;

-- BE SURE TO CLOSE THE FILE SO IT WON'T BE LOCKED AFTER THE PROCEDURE ENDS!
utl_file.fclose(v_file);
END;

 

[kindus]

Hang on Guys,
Actually &quot;SET ECHO OFF&quot; should do the trick for you. But what is bothering me is I could see that line in your script. I donot know whether ECHO doesnot go along with some set commands you specified, but I think you echo may be on. You may check by puttig only the ECHO OFF statment. Please let us know if you find anyhting weird.

 

[carp]

I think setting echo off keeps the system from repeating commands found in a file.
So yes, I think that might be the key to the problem:

1. Put all of your statements into a text file (including the spool, select, and spool off lines).
2. Run your text file from the SQL prompt:
SQL>@my_file

3. Your file should now just have the output you were seeking.

Kindus - thanks for the sanity check!

 

[SeAL]

Thanks guys
I choose to use UTL_FILE and it works like a charm

Hey peeps, those guys are GURU, don't they! ;-)

 

[sem]

Try to use sql*plus with Kindus's advice: check your file again and add set echo off. It MUST work, because sql*plus is used by milions of people for creating text reports. Can you imagine that all bosses read select statements? UTL_FILE works fine only untill you have access to server fs. Or in the case you don't want to read the result at all (output is generated for some program that also resides on server).
What will you do if trying to get result on client machine?
You'll have to download it from server or mount your local drive to server via samba or nfs or something else. Sql*plus is a STANDARD and EASY way to get a text report.

 

[SeAL]

I don't have to get result on client machine, it's just to put all my Oracles data in SAP so better is to use flat files and batchs ...

 

[sem]

Oops. The task was not clear to me.
You may also do your work on a regular basis using dbms_job - an advantage of utl_file as a server-side package. As a drawback - you have to format your output manually, though for flat files it does not matter. Sql*plus also uses bind arrays so sometimes provides better throughput, then explicit looping through cursor. But for your task utl_file is quite good decision.