Re: Avoiding spooling to empty files

From: ddf <oratune_at_msn.com>
Date: Fri, 30 Jan 2009 13:14:09 -0800 (PST)
Message-ID: <03ba6e58-d3c1-4089-bf80-b8daaa38909a_at_x16g2000prn.googlegroups.com>

On Jan 30, 2:53 pm, CenturionX <darwinbaldr..._at_gmail.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_at_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.

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>][_at_<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_at_database string:

$ $ORACLE_HOME/bin/sqlplus -silent
system/*&*&*&*&*&*&*&*&_at_smork

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

$ORACLE_HOME/bin/sqlplus -silent <<EOF

        user/pass_at_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 Received on Fri Jan 30 2009 - 15:14:09 CST