HERE DOCUMENT - something wrong 2

[kasparov]

I'm writing a script which accesses an Oracle database - I'm using a here document to do a select to a spool file. But for some reason it's not going past the terminating label. Here's the code - obviously I've changed the id,pwd & instance name:

-------------------------------------------------------
sqlplus -s my_id/my_pwd@my_instance <<LABEL | tee -a ${LOGFILE}
set trimspool on
set heading off
set pagesize 0
set feedback off
spool list_dbfiles_\$ORACLE_SID;
select FILE_NAME from dba_data_files;
spool off
exit
LABEL

echo HERE !!!!!!!!!!!!!!!
exit
-------------------------------------------------------

When I run this the output is written to the spool file OK but I never see the "HERE !!!!!!!!!!!!!!" text. I'm writing the output to a log file but I've tried removing that bit of code & it makes no difference.

Any idea what I could be doing wrong?

Thanks, Chris

 

[feherke]

Hi

Is your shell [tt]bash[/tt] ? If yes, the exclamation marks ( ! ) are the problem.

Feherke.

http://rootshell.be/~feherke/

 

[hoinz]

Also if your shell is csh, ! will be a problem ...

 

[SamBones]

The backslash in the [tt]spool[/tt] statement shouldn't be there. It's causing the variable to not be decoded, so [tt]sqlplus[/tt] is "seeing" the dollar sign.

You can also add some [tt]prompt[/tt] commands to see where it gets in the SQL.

Try this...

sqlplus -s my_id/my_pwd@my_instance <<-LABEL | tee -a ${LOGFILE}
prompt In the HERE document
set trimspool on
set heading off
set pagesize 0
set feedback off
prompt About to SPOOL output
spool list_dbfiles_${ORACLE_SID}
select FILE_NAME from dba_data_files;
spool off
prompt SPOOL is off
prompt Exiting now
exit
LABEL

echo HERE !!!!!!!!!!!!!!!
exit

 

[kasparov]

Thaks for the replies but still no success. I'm running on Solaris 9 & just to make sure which shell is running have added the first line in the code below. I've removed the "!"s & also the "\" but still no success - I've now pared down the script to this (& called it test_here_doc):
-----------------------------------------------
#!/usr/bin/ksh
sqlplus -s my_id/my_pwd@cfft <<LABEL
prompt in HERE document

prompt about to SPOOL
spool list_dbfiles_$ORACLE_SID
prompt finished SPOOL

select FILE_NAME from dba_data_files;

prompt about to SPOOL OFF
spool off

prompt about to exit
exit

LABEL
echo HERE
-----------------------------------------------
this is what happens when I run it:
-----------------------------------------------
$ test_here_doc
in HERE document
about to SPOOL
finished SPOOL

FILE_NAME
-------------------------------------
/oradata/cfft/system01.dbf
/oradata/cfft/undotbs01.dbf
/oradata/cfft/users01.dbf
/oradata/cfft/IDS_TS.dbf
/oradata/cfft/IDSINDEX_TS.dbf
/oradata/cfft/indx01.dbf
/oradata/cfft/LOB_TS.dbf
/oradata/cfft/tools01.dbf
/oradata/cfft/IDX_REBUILD.dbf

9 rows selected.

about to SPOOL OFF
about to exit
$
-----------------------------------------------

I am using other HERE DOCs in the script this snippet comes from & they work OK - any more ideas?

Thanks

 

[kasparov]

And if I run it without the -s option on sqlplus I get this:

-----------------------------------------------------------
$ test_here_doc

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Apr 27 09:59:55 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> in HERE document
SQL> SQL> about to SPOOL
SQL> SQL> finished SPOOL
SQL> SQL>
FILE_NAME
--------------------------------------------------------------------------------
/oradata/cfft/system01.dbf
/oradata/cfft/undotbs01.dbf
/oradata/cfft/users01.dbf
/oradata/cfft/IDS_TS.dbf
/oradata/cfft/IDSINDEX_TS.dbf
/oradata/cfft/indx01.dbf
/oradata/cfft/LOB_TS.dbf
/oradata/cfft/tools01.dbf
/oradata/cfft/IDX_REBUILD.dbf

9 rows selected.

SQL> SQL> about to SPOOL OFF
SQL> SQL> SQL> about to exit
SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
$
-----------------------------------------------------------

 

[p5wizard]

My $0.02

Perhaps there is some whitespace after the end "LABEL"

In this case the here-doc doesn't finish, sqlplus does finish because of the exit it reads, but all the rest of your code vanishes into the unread/unused portion of the here-doc.


HTH,

p5wizard

 

[Ogzilal]

Hi,

Be sure there is no trailing blank or tab chars after LABEL that delimits the end of here file.
I think, it is the end of script file that stopped the here document, not your LABEL. That is why ksh did not interpret the echo command.

 

[p5wizard]

Beat you to it, aau ;-)

p5

HTH,

p5wizard

 

[kasparov]

Hurrah!

White space white space white space

 

[p5wizard]

You can find whitespace noise in scripts with this grep command

grep '[[:blank:]]$' *

Whitespace at the end of line in a shell script is almost always unnecessary and sometimes (obviously) harmful...


HTH,

p5wizard

 

[Ogzilal]

Hi p5wizard
Because of my fat finger, now I type slowly and always read twice.

 

[kasparov]

p5wizard - Your grep looks useful but I can't get it to work. I put the space back into my file (at the end of the LABEL line) but when I run

grep '[[:blank:]]$' my_file_name

I don't get anything back. I've randomly tried all types of variations but haven't got anything out yet. (I'm running ksh)

Any ideas? Chris

 

[kasparov]

But I can get this to work:

grep "[ ]$" my_file_name

where I have a tab & a space in square brackets.

 

[p5wizard]

try

egrep '[[:blank:]]$' *

or

grep -e '[[:blank:]]$' *

But perhaps your os version / grep flavour doesn't know about character classes. You may try also with double quotes instead of single, see if that helps.



HTH,

p5wizard

 

[PHV]

try also with double quotes instead of single, see if that helps
It shouldn't as the quoting is handled by the shell, not by the command ...