OraFAQ Forum: SQL & PL/SQL » Spooling out of order

Home » SQL & PL/SQL » SQL & PL/SQL » Spooling out of order

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Spooling out of order [message #223625]

Fri, 09 March 2007 18:37

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.

Attachment: SUPP_DELETE_02282007.TXT
(Size: 8.01KB, Downloaded 448 times)

[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

Frank
Messages: 7901
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

William Robertson
Messages: 1643
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)?

Report message to a moderator

Re: Spooling out of order [message #223724 is a reply to message #223699]

Sat, 10 March 2007 13:09

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.

Report message to a moderator

Re: Spooling out of order [message #223725 is a reply to message #223653]

Sat, 10 March 2007 13:13

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 05:38:58 CST 2024