How do I add the colum'names in exporting SQL to text using Run as script

Toad for Oracle & DevOps Toolkit
1

Good morning everybody

I’m new in toad and I need an advice please.
I’m executing this query using the “run as script” command to export data in a text file.
But when I’m looking inside the text file generated, the data is there but there is no column headers.
Please would you help to correct de query so that the column headers appear in the text file generated.
Thanks to you.

***TOP OF THE QUERY ***
set pagesize 0
set trimspool on
set headsep on
set echo off
set embedded off
set feedback off
set heading on
set linesize 2000

spool D:\BK210.txt
Select d.cli,’;’, d.age,’;’, d.ncp,’;’, d.dev
from prod.bkhis d
group by d.cli, d.age, d.ncp, d.dev
order by d.cli asc
;
spool off
***END OF THE QUERY ***

2

You have 2 options

  1. I you are running this in toad with f5 (run as script)
  • remove stuff at the top
  • right click on data grid
  • select export delimited file
  1. This can be done from toad or sqlplus
  • keep stuff at top
  • add set serveroutput on
  • concatenate columns with ||','||
  • write your columns headings out with dbms.output.putline

On Sun, Jul 2, 2017 at 6:03 AM, mbellemax bounce-mbellemax@toadworld.com wrote:

How do I add the colum'names in exporting SQL to text using Run as script

Thread created by mbellemax
Good morning everybody

I'm new in toad and I need an advice please.
I'm executing this query using the "run as script" command to export data in a text file.
But when I'm looking inside the text file generated, the data is there but there is no column headers.
Please would you help to correct de query so that the column headers appear in the text file generated.
Thanks to you.

***TOP OF THE QUERY ***
set pagesize 0
set trimspool on
set headsep on
set echo off
set embedded off
set feedback off
set heading on
set linesize 2000

spool D:\BK210.txt
Select d.cli,';', d.age,';', d.ncp,';', d.dev
from prod.bkhis d
group by d.cli, d.age, d.ncp, d.dev
order by d.cli asc
;
spool off
***END OF THE QUERY ***

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

3

Hello Gene
Thanks really for your reply.

I don’t really unsderstand the last instruction (dbms.output.putline) but i’ve tried to modify the query like this adding what you have noticed

Is this correct (I’ve used the “;” separator) ?

Thanks

–QUERY TOP –

set pagesize 0
set trimspool on
set headsep on
set echo off
set embedded off
set feedback off
set heading on
set serveroutput on
set linesize 2000
dbms.output.putline

spool D:\BK210.txt
Select dbms.output.putline (d.cli||’;’||d.age||’;’||d.ncp||’;’||d.dev)
from prod.bkhis d
group by d.cli, d.age, d.ncp, d.dev
order by d.cli asc
;
spool off

–QUERY BOTTOM–

4

Evening All,

Welcome to Toad mbellemax, I’m sure you’ll enjoy your time here.

As Gene mentioned, you can export to a csv file after running the query with the F9 option. When the results are displayed in Toad, right click on the results grid, choose ‘export resultset’ and in the dialogue that appears, choose to export as a csv file, or any other delimited file type, and there’s an option to export with column headers. However, you do need to use F9 to execute the query, not F5 which is the execute as a script option. You won’t need all the set commands either, nor the spool commands, just the query by itself.

But let’s say that you absolutely must use F5. In this case you would leave all the set commands and the spool commands as you have them, but you would need to adjust the query as follows:

– PRINT THE CSV HEADING FIRST.

Print ‘CLI,AGE,NCP,DEV’

– NOW RUN THE QUERY.

Select cli || ‘,’ || age || ‘,’ || ncp || ‘,’ || dev as csv

from prod.bkhis

order by cli asc;

I’m not at a computer at the moment, I’m in the bath (don’t ask!), so the above is untested.

I’m not sure why you had the group by clause in your query because you are not aggregating anything, so I removed it.

Dbms_output.put line is a pl/sql procedure that prints text during out AFTER THE QUERY HAS FINISHED. So your headings would, had you got the syntax correct, be printed after the results, which is probably not what you expect.

You would use it as follows:

Set serverout on size unlimited

Begin

Dbms_output.put_line(‘Hello World!’);

End;

/

Then run that with F5.

Hth.

Cheers,

Norm. [TeamT]

5

PS. If any of the data in the columns in your query have spaces, commas then you will need to wrap those columns in double quotes for the csv to be valid, so:

Select ’ " ’ || cli || ’ ",’ || …

I’ve left a space before and after the double quotes so you can tell them apart from the single quotes.

Also, does any of the data have double quotes? If so, you must find them and double them up. Something like:

Select ’ " ’ || replace (cli, ’ " ‘,’ “” ‘) || ’ ",’ || …

Again, I’ve put spaces in where they do not need to be, just to separate single and double quotes.

Hth

Cheers,

Norm. [TeamT]

6

hi I'm not at the computer right now, but I ll send you the line and where it goes.

On Jul 2, 2017 10:04 AM, "mbellemax" bounce-mbellemax@toadworld.com wrote:

RE: How do I add the colum'names in exporting SQL to text using Run as script

Reply by mbellemax
Hello Gene
Thanks really for your reply.

I don't really unsderstand the last instruction (dbms.output.putline) but i've tried to modify the query like this adding what you have noticed

Is this correct (I've used the ";" separator) ?

Thanks

--QUERY TOP --

set pagesize 0
set trimspool on
set headsep on
set echo off
set embedded off
set feedback off
set heading on
set serveroutput on
set linesize 2000
dbms.output.putline

spool D:\BK210.txt
Select dbms.output.putline (d.cli||';'||d.age||';'||d.ncp||';'||d.dev)
from prod.bkhis d
group by d.cli, d.age, d.ncp, d.dev
order by d.cli asc
;
spool off

--QUERY BOTTOM--

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

7

--Try this, this should be really close, Gene

set pagesize 0
set trimspool on
set headsep on
set echo off
set embedded off
set feedback off
set heading on
set serveroutput on
set linesize 2000

spool D:\BK210.txt

dbms_output.enable(100000);
dbms_output.put_line('CLI;AGE;NCP;DEV'); --these are your column headings

end;

Select d.cli||';'||d.age||';'||d.ncp||';'||d.dev
from prod.bkhis d
group by d.cli, d.age, d.ncp, d.dev
order by d.cli asc
;

spool off

exit

On Sun, Jul 2, 2017 at 10:03 AM, mbellemax bounce-mbellemax@toadworld.com wrote:

RE: How do I add the colum'names in exporting SQL to text using Run as script

Reply by mbellemax
Hello Gene
Thanks really for your reply.

I don't really unsderstand the last instruction (dbms.output.putline) but i've tried to modify the query like this adding what you have noticed

Is this correct (I've used the ";" separator) ?

Thanks

--QUERY TOP --

set pagesize 0
set trimspool on
set headsep on
set echo off
set embedded off
set feedback off
set heading on
set serveroutput on
set linesize 2000
dbms.output.putline

spool D:\BK210.txt
Select dbms.output.putline (d.cli||';'||d.age||';'||d.ncp||';'||d.dev)
from prod.bkhis d
group by d.cli, d.age, d.ncp, d.dev
order by d.cli asc
;
spool off

--QUERY BOTTOM--

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

8

Hi Gene,

DBMS_OUTPUT is a Pl/SQL package, so it will need wrapping in begin-end commands in order that it will work in a script. Also, and most irritatingly, the output from dbms_output is retrieved at the end of the script’s execution, so the headings will sadly appear at the end of the csv rather than at the beginning. As I say, most irritating.

When I first came across dbms_output, I thought it would be great for progress messages in longer running scripts, sadly, I soon discovered that it wasn’t.

9

Yes I forgot the begin.
but i do this all the time, and it puts the headers at the top.

I also left out the / (continue).

I'll correct that and resend.

On Jul 3, 2017 3:08 AM, "Norm [TeamT]" bounce-NormTeamT@toadworld.com wrote:

RE: How do I add the colum'names in exporting SQL to text using Run as script

Reply by Norm [TeamT]
Hi Gene,

DBMS_OUTPUT is a Pl/SQL package, so it will need wrapping in begin-end commands in order that it will work in a script. Also, and most irritatingly, the output from dbms_output is retrieved at the end of the script's execution, so the headings will sadly appear at the end of the csv rather than at the beginning. As I say, most irritating.

When I first came across dbms_output, I thought it would be great for progress messages in longer running scripts, sadly, I soon discovered that it wasn't.

Cheers,

Norm. [TeamT]

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

10

--I hade some adjustments based on Norm's email . I am not at work / no access to Toad.

--I removed one set command and changed headings to off

--also you don't need a group by

set pagesize 0
set trimspool on
set echo off
set embedded off
set feedback off
set heading off
set serveroutput on
set linesize 2000

spool D:\BK210.txt

begin

dbms_output.enable(100000);

dbms_output.put_line('CLI;AGE;NCP;DEV'); --these are your column headings

end;

/

Select d.cli||';'||d.age||';'||d.ncp||';'||d.dev
from prod.bkhis d
group by d.cli, d.age, d.ncp, d.dev
order by d.cli asc
;

spool off

exit

On Sun, Jul 2, 2017 at 10:03 AM, mbellemax bounce-mbellemax@toadworld.com wrote:

RE: How do I add the colum'names in exporting SQL to text using Run as script

Reply by mbellemax
Hello Gene
Thanks really for your reply.

I don't really unsderstand the last instruction (dbms.output.putline) but i've tried to modify the query like this adding what you have noticed

Is this correct (I've used the ";" separator) ?

Thanks

--QUERY TOP --

set pagesize 0
set trimspool on
set headsep on
set echo off
set embedded off
set feedback off
set heading on
set serveroutput on
set linesize 2000
dbms.output.putline

spool D:\BK210.txt
Select dbms.output.putline (d.cli||';'||d.age||';'||d.ncp||';'||d.dev)
from prod.bkhis d
group by d.cli, d.age, d.ncp, d.dev
order by d.cli asc
;
spool off

--QUERY BOTTOM--

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

11

Hi Gene,

but i do this all the time, and it puts the headers at the top.

Yes, I sit corrected, sorry. Of course it will because the PL/SQL anonymous block ends before the plain SQL query starts, so the buffers are obtained and printed before the SQL is executed. My mistake, apologies.

You, we, one (!) could use prompt instead of calling out to PL/SQL with all it's context changes etc.

Prompt cli,age,dev,....

12

Hello Gene

I’ve just tried the last script corrected and it works perfectly.
It’s exactly what I am expecting.
I do thank you a lot for your assistance.

I do also thank you Norm for your contribution.

I wish to both of you a good friday and a nice weekend.

Max

13

No problem, glad to be of assistance! !!
Cheers,

Gene

On Jul 6, 2017 7:01 PM, "mbellemax" bounce-mbellemax@toadworld.com wrote:

RE: How do I add the colum'names in exporting SQL to text using Run as script

Reply by mbellemax
Hello Gene

I've just tried the last script corrected and it works perfectly.
It's exactly what I am expecting.
I do thank you a lot for your assistance.

I do also thank you Norm for your contribution.

I wish to both of you a good friday and a nice weekend.

Max

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.