Hello,

I'm working with Oracle 10.2.0.4.0

I have an unix script that via command line executes a query

$ORACLE_HOME/bin/sqlplus -silent <<EOF
connect user/pass@databse
set pagesize 0
set heading off
set echo off
set term off
set feedback off
set linesize 126
spool /tmp/file_to_send
SELECT * FROM table;
EOF

The issue is that if there is not data it generates a file with a
control caracter (Don't know which one).

I want to know how to avoid file generation via SQLPLUS commands or at
least don't send any invisible caracter to the file.

Thanks.

On Jan 30, 2:53*pm, CenturionX <darwinbaldr... (AT) gmail (DOT) com> wrote:
Your syntax is wrong for silent mode as I consistently see errors from
your use of 'connect':

$ $ORACLE_HOME/bin/sqlplus -silent
connect bing/########
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_identifier>] | /

'Connect' works with sqlplus /nolog:

$ $ORACLE_HOME/bin/sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jan 30 15:10:00 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect bing/########
Connected.
SQL>

For silent mode all one needs is the user/pass@database string:

$ $ORACLE_HOME/bin/sqlplus -silent
system/*&*&*&*&*&*&*&*&@smork


The blank line following the login credentials indicates a successful
login. Modifying your script:

$ORACLE_HOME/bin/sqlplus -silent <<EOF
user/pass@databse
set pagesize 0
set heading off
set echo off
set term off
set feedback off
set linesize 126
spool /tmp/file_to_send
SELECT * FROM table;
EOF

connects successfully and returns a 1-line, 1 character file with no
control characters present.


David Fitzjarrell

Thanks David

I actually did the corrections. But i need the file zero lenght when
there is no data to spool. Do you know a way to do that. Be generate a
zero-lenght file or not generated it at all.

Thanks.

Is there a database configuration for this or maybe an Unix parameter
too???

Thanks.

On Jan 30, 2:50*pm, CenturionX <darwinbaldr... (AT) gmail (DOT) com> wrote:
Well, are you sure you want this? Here's one hint:

$ echo " " > xx
$ if [ `find . -size 1c -name xx` ]
$ echo "" > xx
$ if [ `find . -size 1c -name xx` ]
$ echo "\c">xyz
$ ll xyz
-rw-rw-rw- 1 jeg users 0 Jan 30 15:25 xyz

Do what you want instead of echo.

jg
--
@home.com is bogus.
http://www3.signonsandiego.com/stori...ion/?uniontrib

Thanks Joel,

Actually it is very usefull.

I'll try to implement it.

The only inconvinient is that i'm migrating a lot of scripts that
worked fine (generated zero-lenght files when no data found).

So i'll still need the trick for Oracle.

Thanks.

CenturionX (darwinbaldrich (AT) gmail (DOT) com) wrote:
: Hello,

: I'm working with Oracle 10.2.0.4.0

: I have an unix script that via command line executes a query

: $ORACLE_HOME/bin/sqlplus -silent <<EOF
: connect user/pass@databse
: set pagesize 0
: set heading off
: set echo off
: set term off
: set feedback off
: set linesize 126
: spool /tmp/file_to_send
: SELECT * FROM table;
: EOF

: The issue is that if there is not data it generates a file with a
: control caracter (Don't know which one).
^^^^^^^^^^^^^^^^^^^^

on usix, use od (octal dump) to examine the file.

I like
od -a YOUR-FILE

(-a = show bytes using character notation, which is often easier to read)

$0.10

On Jan 30, 6:27*pm, CenturionX <darwinbaldr... (AT) gmail (DOT) com> wrote:
set trimspool on

That causes SQL*Plus to 'trim' the trailing spaces, and it may just
fix your 'problem'.


David Fitzjarrell

Thanks everybody,

I tried the trimspool on but it didn't work.

I noticed i forgot the spool off, so i included it in the script. I
ran it again an it worked. I have my zero-lenght file.

Thanks.