7 Replies Latest reply: Apr 25, 2014 11:55 AM by ddf_dba_ifox RSS

    simple shell script

      Hi all,

       

      11.2.0.1

       

      I have a simple shell script that has embedded sql statement inside.

       

      a.sh

      ====

      #!/bin/ksh

       

      export ORACLE_SID=PROD

      ORAENV_ASK=NO

      . oraenv

      sqlplus system/manager << EOD

      SPOOL a.log   <==== DOES NOT WORK HERE

      create or replace directory data_pump_dir as '/u07';

      exit

      EOD

       

       

      The good thing for this script is you do not have to call another .sql program.

      The bad thing is , I can not display spool logfile inside it, so you can check what happened to the command, as It does not spool any?

      Why is that so? Or how do I put spool command?

       

      Thanks,

      pK

        • 1. Re: simple shell script
          sb92075

          petra-K wrote:

           

          Hi all,

           

          11.2.0.1

           

          I have a simple shell script that has embedded sql statement inside.

           

          a.sh

          ====

          #!/bin/ksh

           

          export ORACLE_SID=PROD

          ORAENV_ASK=NO

          . oraenv

          sqlplus system/manager << EOD

          create or replace directory data_pump_dir as '/u07';

          exit

          EOD

           

           

          The good thing for this script is you do not have to call another .sql program.

          The bad thing is , I can not display spool logfile inside it? as I does not spool any?

          Why is that so? Or how do I put spool command?

           

          Thanks,

          pK

          use text editor so file contains SPOOL command

          • 2. Re: simple shell script

            I mean I already put SPOOL after sqlplus but it does not work

            Can you test it  yourself dear? Maybe there is no problem with your keyboard?

            • 3. Re: simple shell script
              sb92075

              petra-K wrote:

               

              I mean I already put SPOOL after sqlplus but it does not work

              Can you test it  yourself dear? Maybe there is no problem with your keyboard?

              I am no "dear" & you don't deserve any additional response.

              • 4. Re: simple shell script
                Lothar Flatz

                you don't need spool

                use a comand script for input that makes it easier. The output you can pipe and redirect.

                • 5. Re: simple shell script
                  Antonyro-Oracle

                  Hi


                  You can try as below


                  #!/bin/ksh

                  script spool.log <<<<<<<<<<<<<<<

                  export ORACLE_SID=PROD

                  ORAENV_ASK=NO

                  . oraenv

                  sqlplus system/manager << EOD

                  create or replace directory data_pump_dir as '/u07';

                  exit

                  EOD

                   

                  BR,

                  • 6. Re: simple shell script

                    Good answer dear

                    • 7. Re: simple shell script
                      ddf_dba_ifox

                      You really should not be passing the username and password on the sqlplus command line as it makes it visible from a ps -ef command.  You should be connecting after you call sqlplus:

                       

                      #!/bin/ksh

                       

                      export ORACLE_SID=PROD

                      ORAENV_ASK=NO

                       

                      . oraenv

                       

                      sqlplus /nolog <<EOF

                      connect system/manager

                      spool a.log

                      set echo on

                      create or replace directory data_pump_dir as '.u07';

                      exit

                      EOF

                       

                      I  have a number of shell scripts that use spool after the sqlplus call and they all work just fine, generating the requested file without issue.

                       

                       

                      David Fitzjarrell