Home » SQL & PL/SQL » SQL & PL/SQL » Spooling out of order
Spooling out of order [message #223625] Fri, 09 March 2007 18:37 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
I created a script to spool the output in the order of header record, records of data.
I don't know why I'm getting the reverse of it in my output file.

set linesize 4000

set pagesize 0 newpage 0

set setspace 0

set feedback off

set echo off 

set heading off

set verify off

set termout off

set trimspool on


SPOOL 'C:\Documents and Settings\dpong\My Documents\POST_SUPP\supp_delete_02282007.txt'


				    
SELECT 
'"INDIVIDUAL_ID"'||chr(124)||
'"ORG_ID"'||chr(124)||
'"DATA_SOURCE_NAME"'||chr(124)||
'"DATA_SOURCE_ID"'||chr(124)||
'"COUNTER"'||chr(124)||
'"SOURCE_COLUMN_NAME"'||chr(124)||
'"LANGUAGE"'||chr(124)||
'"COMPANY_TYPE"'||chr(124)||
'"LINE_OF_BUSINESS"'||chr(124)||
'"EMPLOYEE_FLG"'||chr(124)||
'"NAME_PREFIX"'||chr(124)||
'"FIRST_NAME"'||chr(124)||
'"MIDDLE_NAME"'||chr(124)||
'"LAST_NAME"'||chr(124)||
'"NAME_SUFFIX"'||chr(124)||
'"PREFERRED_NAME"'||chr(124)||
'"GENDER_TYPE"'||chr(124)||
'"COMPANY_GIVEN"'||chr(124)||
'"KEY_TYPE"'||chr(124)||
'"KEY_CODE"'||chr(124)||
'"CODE_TYPE"'||chr(124)||
'"CODE"'||chr(124)||
'"TITLE_GIVEN"'||chr(124)||
'"JOB_ROLE_FUNCTION_GIVEN"'||chr(124)||
'"HOME_PHONE_NO"'||chr(124)||
'"WORK_PHONE_NO"'||chr(124)||
'"MOBILE_PHONE_NO"'||chr(124)||
'"PHONE_PERMISSION"'||chr(124)||
'"FAX_PHONE_NO"'||chr(124)||
'"EMAIL_ADDRESS"'||chr(124)||
'"EMAIL_PERMISSION"'||chr(124)||
'"MAIL_PERMISSION"'||chr(124)||
'"NEWSLETTER_PERMISSION"'||chr(124)||
'"MARKETING_STATUS"'||chr(124)||
'"CONTACT_STATUS_CODE"'||chr(124)||
'"LAST_CONTACT_DATE"'||chr(124)||
'"CONTACT_NOTES"'||chr(124)||
'"COMPANY_GIVEN_LOCAL_1"'||chr(124)||
'"COMPANY_GIVEN_LOCAL_2"'||chr(124)||
'"TITLE_GIVEN_LOCAL"'||chr(124)||
'"CONTACT_COUNTRY_PREFERENCE"'||chr(124)||
'"CONTACT_COUNTRY_DEFINABLE_1"'||chr(124)||
'"CONTACT_COUNTRY_DEFINABLE_2"'||chr(124)||
'"CONTACT_COUNTRY_DEFINABLE_3"'||chr(124)||
'"CONTACT_COUNTRY_DEFINABLE_4"'||chr(124)||
'"CONTACT_COUNTRY_DEFINABLE_5"'||chr(124)||
'"CONTACT_LOCAL_CHECK1_FLG"'||chr(124)||
'"CONTACT_LOCAL_CHECK2_FLG"'||chr(124)||
'"CONTACT_MMD_STATUS"'||chr(124)||
'"CONTACT_COUNTRY_OWN"'||chr(124)||
'"POSTAL_ADDRESS_TYPE_ID"'||chr(124)||
'"ADDR1"'||chr(124)||
'"ADDR2"'||chr(124)||
'"ADDR3"'||chr(124)||
'"CITY"'||chr(124)||
'"STATE_GIVEN"'||chr(124)||
'"COUNTRY_GIVEN"'||chr(124)||
'"COUNTRY_CODE_2"'||chr(124)||
'"COUNTRY_CODE_3"'||chr(124)||
'"POSTAL_CODE"'||chr(124)||
'"MAILSTOP"'||chr(124)||
'"UNDELIVERABLE_FLG"'||chr(124)||
'"ADDRESS_COUNTRY_DEFINABLE_1"'||chr(124)||
'"ADDRESS1_LOCAL"'||chr(124)||
'"ADDRESS2_LOCAL"'||chr(124)||
'"ADDRESS3_LOCAL"'||chr(124)||
'"ADDRESS_CITY_LOCAL"'||chr(124)||
'"ADDRESS_POSTAL_CODE_LOCAL"'||chr(124)||
'"ADDRESS_COUNTRY_LOCAL"'||chr(124)||
'"ADDRESS1_CHECK_FLG"'||chr(124)||
'"ADDRESS2_CHECK_FLG"'||chr(124)||
'"ADDRESS3_CHECK_FLG"'||chr(124)||
'"ADDRESS_CITY_CHECK_FLG"'||chr(124)||
'"ADDRESS_POSTAL_CODE_CHECK_FLG"'||chr(124)||
'"ADDRESS_COUNTRY_CHECK_FLG"'||chr(124)||
'"ADDRESS_COUNTRY_OWN"'||chr(124)||
'"IS_ADDRESS_CERTIFIED"'
FROM DUAL

UNION 
	
SELECT 
'"'||INDIVIDUAL_ID||'"'||chr(124)||
'"'||ORG_ID||'"'||chr(124)||
'"'||DATA_SOURCE_NAME||'"'||chr(124)||
'"'||DATA_SOURCE_ID||'"'||chr(124)||
'"'||COUNTER||'"'||chr(124)||
'"'||SOURCE_COLUMN_NAME||'"'||chr(124)||
'"'||LANGUAGE||'"'||chr(124)||
'"'||COMPANY_TYPE||'"'||chr(124)||
'"'||LINE_OF_BUSINESS||'"'||chr(124)||
'"'||EMPLOYEE_FLG||'"'||chr(124)||
'"'||NAME_PREFIX||'"'||chr(124)||
'"'||FIRST_NAME||'"'||chr(124)||
'"'||MIDDLE_NAME||'"'||chr(124)||
'"'||LAST_NAME||'"'||chr(124)||
'"'||NAME_SUFFIX||'"'||chr(124)||
'"'||PREFERRED_NAME||'"'||chr(124)||
'"'||GENDER_TYPE||'"'||chr(124)||
'"'||COMPANY_GIVEN||'"'||chr(124)||
'"'||KEY_TYPE||'"'||chr(124)||
'"'||KEY_CODE||'"'||chr(124)||
'"'||CODE_TYPE||'"'||chr(124)||
'"'||CODE||'"'||chr(124)||
'"'||TITLE_GIVEN||'"'||chr(124)||
'"'||JOB_ROLE_FUNCTION_GIVEN||'"'||chr(124)||
'"'||HOME_PHONE_NO||'"'||chr(124)||
'"'||WORK_PHONE_NO||'"'||chr(124)||
'"'||MOBILE_PHONE_NO||'"'||chr(124)||
'"'||PHONE_PERMISSION||'"'||chr(124)||
'"'||FAX_PHONE_NO||'"'||chr(124)||
'"'||EMAIL_ADDRESS||'"'||chr(124)||
'"'||EMAIL_PERMISSION||'"'||chr(124)||
'"'||MAIL_PERMISSION||'"'||chr(124)||
'"'||NEWSLETTER_PERMISSION||'"'||chr(124)||
'"'||MARKETING_STATUS||'"'||chr(124)||
'"'||CONTACT_STATUS_CODE||'"'||chr(124)||
'"'||LAST_CONTACT_DATE||'"'||chr(124)||
'"'||CONTACT_NOTES||'"'||chr(124)||
'"'||COMPANY_GIVEN_LOCAL_1||'"'||chr(124)||
'"'||COMPANY_GIVEN_LOCAL_2||'"'||chr(124)||
'"'||TITLE_GIVEN_LOCAL||'"'||chr(124)||
'"'||CONTACT_COUNTRY_PREFERENCE||'"'||chr(124)||
'"'||CONTACT_COUNTRY_DEFINABLE_1||'"'||chr(124)||
'"'||CONTACT_COUNTRY_DEFINABLE_2||'"'||chr(124)||
'"'||CONTACT_COUNTRY_DEFINABLE_3||'"'||chr(124)||
'"'||CONTACT_COUNTRY_DEFINABLE_4||'"'||chr(124)||
'"'||CONTACT_COUNTRY_DEFINABLE_5||'"'||chr(124)||
'"'||CONTACT_LOCAL_CHECK1_FLG||'"'||chr(124)||
'"'||CONTACT_LOCAL_CHECK2_FLG||'"'||chr(124)||
'"'||CONTACT_MMD_STATUS||'"'||chr(124)||
'"'||CONTACT_COUNTRY_OWN||'"'||chr(124)||
'"'||POSTAL_ADDRESS_TYPE_ID||'"'||chr(124)||
'"'||ADDR1||'"'||chr(124)||
'"'||ADDR2||'"'||chr(124)||
'"'||ADDR3||'"'||chr(124)||
'"'||CITY||'"'||chr(124)||
'"'||STATE_GIVEN||'"'||chr(124)||
'"'||COUNTRY_GIVEN||'"'||chr(124)||
'"'||COUNTRY_CODE_2||'"'||chr(124)||
'"'||COUNTRY_CODE_3||'"'||chr(124)||
'"'||POSTAL_CODE||'"'||chr(124)||
'"'||MAILSTOP||'"'||chr(124)||
'"'||UNDELIVERABLE_FLG||'"'||chr(124)||
'"'||ADDRESS_COUNTRY_DEFINABLE_1||'"'||chr(124)||
'"'||ADDRESS1_LOCAL||'"'||chr(124)||
'"'||ADDRESS2_LOCAL||'"'||chr(124)||
'"'||ADDRESS3_LOCAL||'"'||chr(124)||
'"'||ADDRESS_CITY_LOCAL||'"'||chr(124)||
'"'||ADDRESS_POSTAL_CODE_LOCAL||'"'||chr(124)||
'"'||ADDRESS_COUNTRY_LOCAL||'"'||chr(124)||
'"'||ADDRESS1_CHECK_FLG||'"'||chr(124)||
'"'||ADDRESS2_CHECK_FLG||'"'||chr(124)||
'"'||ADDRESS3_CHECK_FLG||'"'||chr(124)||
'"'||ADDRESS_CITY_CHECK_FLG||'"'||chr(124)||
'"'||ADDRESS_POSTAL_CODE_CHECK_FLG||'"'||chr(124)||
'"'||ADDRESS_COUNTRY_CHECK_FLG||'"'||chr(124)||
'"'||ADDRESS_COUNTRY_OWN||'"'||chr(124)||
'"'||IS_ADDRESS_CERTIFIED||'"'



		FROM DM_METRICS.dm_supp_delete_final@TDS_DM_METRICS.US.ORACLE.COM;

/

SPOOL OFF	


Attached is the output file.

[Updated on: Sat, 10 March 2007 13:14]

Report message to a moderator

Re: Spooling out of order [message #223653 is a reply to message #223625] Sat, 10 March 2007 02:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First remove all the whitelines within a single sql-statement (e.g. the whitelines around the UNION) and remove the commented parts. Your script is quite unreadable as it is, without having to find out that half of it is just comments.
Why would you need to concatenate chr(124) ? Why not simply do
SELECT '"INDIVIDUAL_ID"|"ORG_ID"|"DATA_SOURCE_NAME"| etc..
from dual


And finally, add an order by to your union, because without it you cannot be certain that your 'header' will be selected first.

[Updated on: Sat, 10 March 2007 02:23]

Report message to a moderator

Re: Spooling out of order [message #223699 is a reply to message #223625] Sat, 10 March 2007 09:16 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Maybe add an ordering key, and have it non-displayed:
SQL> col seq noprint
SQL> set hea off
SQL> 
SQL> SELECT 2 AS seq, 'This is some data' FROM dual
  2  UNION ALL
  3  SELECT 1, 'HEADER ROW' FROM dual
  4  ORDER BY seq;

HEADER ROW
This is some data

2 rows selected.

btw did you need UNION, or could it be just UNION ALL (avoiding the implicit DISTINCT)?
Re: Spooling out of order [message #223724 is a reply to message #223699] Sat, 10 March 2007 13:09 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Thanks for the hint. I think it'll be about the same as the number of records that are spooled out are fairly small... yep. I should change it to union all.
Re: Spooling out of order [message #223725 is a reply to message #223653] Sat, 10 March 2007 13:13 Go to previous message
dpong
Messages: 73
Registered: January 2007
Member
Frank,
Okay, thanks...
I think what happened was everything was spooled out in a first in, last out order.
I just didn't want to do it manaully... instead I had a copy of the columns from a query I had retrieved from user_tab_columns directly.

Yep. Eventually the problem was solved by adding ORDER BY 1 desc in the end.

[Updated on: Sat, 10 March 2007 13:15]

Report message to a moderator

Previous Topic: AUTHID
Next Topic: How to know the status of sql batch processing trhough pl/sql?
Goto Forum:
  


Current Time: Fri Dec 02 23:02:26 CST 2016

Total time taken to generate the page: 0.08443 seconds