OUTPUT TO DATAFILE.....SELECT STATEMENT APPEARS??

[Vandy02]

I HAVE THE FOLLOWING OUTPUT INTO MY DATA FILE...
OF WHICH IS PART OF THE SCRIPT..I DO NOT WANT THE
set...through the select .... to appear in my output file...refer to script...as I have tried all of these to prevent...it works fine in TOAD...

#set echo off;
#set heading off;
#set feedback off;
#set pagesize 50000;
#set termout off;
#set ttitle off;
#set embedded on;
#set verify off;

SQL> set linesize 110;
SQL> select WONUM||'|'||
2 STATUS||'|'||
3 to_char(CHANGEDATE, 'yyyy-mm-dd hh24:mi')||'|'||
4 CHANGEBY||'|'||
5 MEMO||'|'||
6 GLACCOUNT||'|'||
7 FINCNTRLID||'|'||
8 ROWSTAMP
9 from WOSTATUS
10 #Get smallest count....then pull by these individually
SP2-0423: Illegal GET command
10 WHERE TO_CHAR(changedate, 'yyyy') NOT IN ('1998', '1999', '2000', '2001', '2002', '2003', '2004');
3881333|COMP|0200-01-16 10:55|JORDAND||06090T06082V||8867859
3880831|COMP|2015-03-01
-------------------------------------
SCRIPT:
-------------------------------------
#!/bin/ksh

set -x

# add the path to unl
#
export PATH=:/opt/apps/maximo/bin:${PATH}

# This script will unload the wostatus table from a Maximo 4i database

ERRTMP=/tmp/err.$$

MINSQL="SELECT NVL(MAX(TO_NUMBER(last_rowstamp)),0) FROM esmaximo.wdw_incr_rowstamp_pull
WHERE table_name = 'WOSTATUS' AND pull_status = 'C'"

MAXSQL="SELECT esmaximo.maxseq.nextval FROM dual"

# Get the lowest rowstamp to start from
#
MINRS=$(unl -c esmaxbat/imagine "${MINSQL}")
if [ "$?" -ne 0 ]
then
exit $?
fi

MINRS=${MINRS:-' '}

# Get the highest rowstamp to stop at
#
MAXRS=$(unl -c esmaxbat/imagine "${MAXSQL}")
if [ "$?" -ne 0 ]
then
exit $?
fi

DT=$(date +%Y%m%d%H%M%S)

# Add a new control record for this incremental pull of longdescription
#
sqlplus esmaxbat/imagine >${ERRTMP} 2>&1 <<-sulplqs


#set echo off;
#set heading off;
#set feedback off;
#set pagesize 50000;
#set termout off;
#set ttitle off;
#set embedded on;
#set verify off;

SET HEADING OFF;
SET PAGESIZE 0;
SET FEEDBACK OFF;
SET ECHO OFF;

spool /var/opt/oracle/mp4/maximart/wostatus.dat;
set linesize 110;
select WONUM||'|'||
STATUS||'|'||
to_char(CHANGEDATE, 'yyyy-mm-dd hh24:mi')||'|'||
CHANGEBY||'|'||
MEMO||'|'||
GLACCOUNT||'|'||
FINCNTRLID||'|'||
ROWSTAMP
from WOSTATUS
#Get smallest count....then pull by these individually
WHERE TO_CHAR(changedate, 'yyyy') NOT IN ('1998', '1999', '2000', '2001', '2002', '2003', '2004');

spool off;

-- get counts use same where clause as above.


spool off;
sulplqs

exit;

 

[LKBrwnDBA]

Try this:

sqlplus -s esmaxbat/imagine >${ERRTMP} 2>&1 <<-sulplqs
set lin 110 trims on pages 0 feed off
...etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[greek01]

Vandy02,

The problem is # is not a valid comment marker in sqlplus
us -- instead.

sqlplus thinks your comment ( unix comment ) is part of the sql script.

Mike

 

[LKBrwnDBA]

Also, to prevent the SQL statement from appering in the spool file use the SET TERM OFF statement.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb