7 Replies Latest reply on Feb 25, 2010 7:43 AM by Herbert van den Bergh-Oracle

    output redirect to a file

    DOA
      Hi all,

      I am trying to write a shell script, from the script I connect with sql and execute some command.
      I also define a file within the script who stores the output from the script, now the problem is what ever the sql statement I run through that script is not storing to that file, only the OS level commond output is storing to that file.

      please suggest.
        • 1. Re: output redirect to a file
          684264
          Well, if you show us the script, maybe we can help.
          • 2. Re: output redirect to a file
            DOA
            Hi,
            here is sample.
            within this script all the command under SQL should be store in the file test as tail command is storing its output into the file test at the end of the script.



            sqlplus -s sys/sys as sysdba@TEST <<EOF
            set linesize 200
            set head off
            select OPEN_MODE from v\$database;
            select count(*) from dba_objects where status='INVALID';
            select log_mode from v\$database;
            EOF
            tail -100 /d11_testdbx/oracle/testdb/102/admin/TEST_tajorn3/bdump/alert_TEST.log > test
            exit;
            • 3. Re: output redirect to a file
              684264
              I guess you should use SPOOL command.
              For instance:
              sqlplus -s sys/sys as sysdba@TEST <<EOF
              spool output.txt
              set linesize 200
              set head off
              select OPEN_MODE from v\$database;
              select count(*) from dba_objects where status='INVALID';
              select log_mode from v\$database;
              spool off;
              EOF
              tail -100 /d11_testdbx/oracle/testdb/102/admin/TEST_tajorn3/bdump/alert_TEST.log > test
              exit;
              After you run the script, try "cat output.txt".
              If that works, then you can refine your script accordingly.

              kido
              • 4. Re: output redirect to a file
                506787
                or you could redirect the output of the sqlplus command:
                sqlplus -s sys/sys as sysdba@TEST <<EOF > sqloutput.txt
                spool output.txt
                set linesize 200
                set head off
                select OPEN_MODE from v\$database;
                select count(*) from dba_objects where status='INVALID';
                select log_mode from v\$database;
                spool off;
                exit
                EOF
                tail -100 /d11_testdbx/oracle/testdb/102/admin/TEST_tajorn3/bdump/alert_TEST.log > test
                exit
                please mind the last exit should be without a ';'!

                did you think about using oracle's enterprise manager? usage is free!
                most of the time the effort to create the scripts for monitoring do not weight up to just installing OEM...
                • 5. Re: output redirect to a file
                  645962
                  Hi,
                  you may want to try this one:

                  !tail -100 /d11_testdbx/oracle/testdb/102/admin/TEST_tajorn3/bdump/alert_TEST.log > test

                  note the " ! " before the tail command.

                  Regards,
                  Jay
                  • 6. Re: output redirect to a file
                    Herbert van den Bergh-Oracle
                    Jay A wrote:
                    Hi,
                    you may want to try this one:

                    !tail -100 /d11_testdbx/oracle/testdb/102/admin/TEST_tajorn3/bdump/alert_TEST.log > test

                    note the " ! " before the tail command.

                    Regards,
                    Jay
                    No, the tail command is after the "sqlplus <<EOF", "EOF" here document, so it's executed by the shell, not sqlplus. No ! needed.
                    • 7. Re: output redirect to a file
                      Herbert van den Bergh-Oracle
                      If you want the output of sqlplus and tail in the same file, you can do it like this:
                      (
                      sqlplus -s sys/sys as sysdba@TEST <<EOF
                      set linesize 200
                      set head off
                      select OPEN_MODE from v\$database;
                      select count(*) from dba_objects where status='INVALID';
                      select log_mode from v\$database;
                      EOF
                      tail -100 /d11_testdbx/oracle/testdb/102/admin/TEST_tajorn3/bdump/alert_TEST.log 
                      ) > test
                      exit;
                      Note the ( and ) brackets surrounding the two commands, grouping their output together, and then redirecting it to the file test.