sql select with concate of columns SP2-0734: unknown command beginning

[Ratzie]

I am running a ksh script that does some dumpster diving and pull records out.

It is basically reading a file does a select and at the end, you will see $i.
AND ORD_ORDER.ADV_ORDER_TEXT_FIELD_568='${i}';

I can run the script manually. (without the while loop)
But, when I run it thru the script I get a whack of errors.
SP2-0734: unknown command beginning "FROM ADVXR..." - rest of line ignored.
SP2-0734: unknown command beginning "ADVXRT_HDB..." - rest of line ignored.
SP2-0734: unknown command beginning "ADVXRT_HDB..." - rest of line ignored.
SP2-0734: unknown command beginning "ADVXRT_HDB..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.

What I need it to do is pull the selected records out and place in a file.


##################
Script

cat $TICKETS_MISMATCH |while read i
do
$SQLPLUS -silent "advxrt_hdb/$LOGONID@hdb" <$TICKETS_MISMATCH_FULL
set feedback off
set verify off
set echo off
set heading off
set pagesize 7010

SELECT ORD_ORDER.ADV_ORDER_TEXT_FIELD_568
|| ','
|| ORD_ORDER.ORDER_NUM
|| ','
|| ORD_ORDER.ADV_ORDER_SUMMARY_FIELD_012
|| ','
|| LAB_USER.NAME
|| ','
|| LAB_USER.LOGON_ID
|| ','
|| LAB_USER.ADV_EXTENDED_USER_FIELD_001
|| ','
|| TO_CHAR(MOBILECOMP1_SR.ADV_COMPLETION_TIME_FIELD_001, 'HH24:MI:SS')
|| ','
|| TO_CHAR(MOBILECOMP1_SR.ADV_COMPLETION_DATE_FIELD_001, 'DD-MON-YYYY')
|| ','
|| MOBILECOMP1_SR.ADV_COMPLETION_NOTES_FIELD_001
|| ','
|| MOBILECOMP1_SR.ADV_COMPLETION_TEXT_FIELD_003
|| ','
|| MOBILECOMP1_SR.ADV_COMPLETION_TEXT_FIELD_013
|| ','
|| MOBILECOMP1_SR.ADV_COMPLETION_TEXT_FIELD_014
|| ','
|| MOBILECOMP1_SR.ADV_COMPLETION_TEXT_FIELD_023
|| ','
|| ORD_JOB_CODE.NAME
|| ','
|| ROUND(ASN_ASSIGNMENT.TOTAL_TIME_EN_ROUTE / 60,0)
|| ','
|| ROUND(ASN_ASSIGNMENT.TOTAL_TIME_ON_SITE / 60,0)

FROM ADVXRT_HDB.LAB_USER,
ADVXRT_HDB.SR_REPORT,
ADVXRT_HDB.ASN_ASSIGNMENT,
ADVXRT_HDB.ORD_ORDER,
ADVXRT_HDB.MOBILECOMP1_SR,
ADVXRT_HDB.ORD_JOB_CODE
WHERE (ASN_ASSIGNMENT.ASSIGNMENT_ID = SR_REPORT.ASSIGNMENT)
AND (ASN_ASSIGNMENT.FOR_ORDER = ORD_ORDER.ORDER_ID)
AND (MOBILECOMP1_SR.MOBILE_COMP1_ID = SR_REPORT.RECORD_NUMBER)
AND (ORD_ORDER.JOB_CODE = ORD_JOB_CODE.JOB_CODE_ID)
AND (SR_REPORT.USER_ID = LAB_USER.USER_ID(+))
AND ORD_ORDER.COMPLETED BETWEEN ((TO_DATE('${date_entered} 00:00:00','YYYY-MM-DD HH24:MI:SS')) - ((SELECT DECODE
(UTC_OFFSET, NULL, 0, UTC_OFFSET) FROM TZ_UTC_TO_LOCAL TZ WHERE (TO_DATE
('${date_entered} 00:00:00','YYYY-MM-DD HH24:MI:SS') BETWEEN TZ.STARTS_UTC AND
TZ.ENDS_UTC)AND TZ.TIME_AUTHORITY = 1000264000000000000000000013))/(24*60*60))
AND
((TO_DATE('${date_entered} 11:59:59','YYYY-MM-DD HH24:MI:SS')) - ((SELECT DECODE
(UTC_OFFSET, NULL, 0, UTC_OFFSET) FROM TZ_UTC_TO_LOCAL TZ WHERE (TO_DATE
('${date_entered} 11:59:59','YYYY-MM-DD HH24:MI:SS') BETWEEN TZ.STARTS_UTC AND
TZ.ENDS_UTC)AND TZ.TIME_AUTHORITY = 1000264000000000000000000013))/(24*60*60))
AND ORD_ORDER.ADV_ORDER_TEXT_FIELD_568='${i}';
exit;
EOF
done

[Shibin_2]

It more looks like SQL or Oracle error. You may read some similar post in Oracle forums.

http://forums.oracle.com/forums/thread.jspa?threadID=414726

[Jean-Luc Oudart]

Hi

you have not indicated the Oracle version but you may investigate external table.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm

http://www.orafaq.com/node/848

I believe you could reduce the overall number of Oracle sessions

Regards
jean-Luc