11 Replies Latest reply on Mar 4, 2020 12:03 PM by Turloch O'Tierney-Oracle

    How can I run sql script in background?

    macdoor

      I can run my sql script in foreground smoothly. But when I run same script in background, it hang up in background, nothing output. After I switch the process to foreground, it run again .

       

      SQL script file: t.sql

      ----------------------------

      select sysdate from dual;

      quit;

      -------------------------------------

       

      I can get result when run this command :

      ---------------------------------------

      sql U/P@10.224.141.137:8521/nmsb @t.sql

       

      I can not get any result and hung up in background , when run this command

      ---------------------------------------

      sql U/P@10.224.141.137:8521/nmsb @t.sql &

      ---------------------------------------

      After I input "fg" command in same terminal. The command can continue.

       

      The only difference is '&' sign.

       

      Thx.

        • 1. Re: How can I run sql script in background?
          Gaz in Oz

          Putting things  "in the background", you usually want to redirect any output to a file. Redirect the screen output to a file via something like:

          sql U/P@nmsb @t1.sql > t1.log 2>&1

          ...and then take a look in t1.log

           

          $ cat t1.log

           

          SQLcl: Release 4.2.0 Production on Mon Jan 23 20:12:56 2017

           

          Copyright (c) 1982, 2017, Oracle.  All rights reserved.

           

          Connected to:

          Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

           

           

          SYSDATE

          ---------

          23-JAN-17

           

           

          Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

          ...and if you are just after the date, then:

          $ sql -S -noupdates U/P@nmsb @1.sql > t1.log 2>&1

          $ cat t1.log

           

          SYSDATE

          ---------

          23-JAN-17

          ...and for the bare minimum, no heading or underline, just the date,  add "SET PAGESIZE 0" to you t1.sql file

          t1.sql:

          SET PAGESIZE 0

          SELECT SYSDATE FROM dual;

          QUIT;

           

          $ sql -S -noupdates U/P@nmsb @t1.sql > t1.log 2>&1

          $ cat t1.log

           

          23-JAN-17

           

          To see the commandline options available for sqlcl,

          $ sql -?

          -S by the way means "silent", suppress sql login /logout banners and do not echo commands.

           

          Cheers,

           

          Gaz.

          • 2. Re: How can I run sql script in background?
            macdoor

            Gaz,

             

            Thank you very much !

            But to redirect output  can not solve my problem. 

            sql U/P@nmsb @t1.sql > t1.log 2>&1

            This command still run in foreground. I want the command like this

            sql U/P@nmsb @t1.sql > t1.log 2>&1 &

            The sql script I pasted here is a sample. In my project I want to use sqlcl to load and update a lot of data. It may take quite long time. So I wang to run it in background. I also want to schedule the job with crontab. But Sqlcl hung when I put it into background.

             

            Cheers,

             

            macdoor

            • 3. Re: How can I run sql script in background?
              Gaz in Oz

              ok, so this actually looks like a sqlcl bug...

               

              sqlplus behaves as expected:

              [gaz@x3200 bin]$ sqlplus -S gaz/gaz@xenix @t1.sql > t1.log 2>&1 &

              [1] 15811

              [gaz@x3200 bin]$

              [1]+  Done                    sqlplus -S gaz/gaz@xenix @t1.sql > t1.log 2>&1

              [gaz@x3200 bin]$

               

              sqlcl, as you have pointed out, and replicated here, does not:

              [gaz@x3200 bin]$ ./sql -S gaz/gaz@xenix @t1.sql > t1.log 2>&1 &

              [1] 15827

              [gaz@x3200 bin]$

               

              [1]+  Stopped                ./sql -S gaz/gaz@xenix @t1.sql > t1.log 2>&1

              [gaz@x3200 bin]$

              possible problem with the sql shell script perhaps, or the sqlcl java class...

              1 person found this helpful
              • 4. Re: How can I run sql script in background?
                macdoor

                Gaz,

                 

                Thx. I really love sqlcl. Will this bug be fixed in near future?

                 

                cheers,

                 

                macdoor

                • 5. Re: How can I run sql script in background?
                  Gaz in Oz

                  You're welcome.

                   

                  Will this bug be fixed in near future?

                  I don't know, raise it as a bug with Oracle support.

                   

                  sqlplus works,  use that for background tasks.

                   

                  Cheers,

                   

                  Gaz.

                  • 6. Re: How can I run sql script in background?
                    macdoor

                    Gaz

                    thx. I have switched to sqlldr. It is more complex and less bug.

                     

                    macdoor

                    • 7. Re: How can I run sql script in background?
                      Gaz in Oz

                      sqlldr is used to insert data into Oracle database tables. Did you mean sqlplus?

                       

                      Using sqlplus instead of sql is very straight forward and not complicated at all.it is as simple as changing your example

                      sql U/P@10.224.141.137:8521/nmsb @t.sql &

                      to

                      sqlplus U/P@10.224.141.137:8521/nmsb @t.sql &

                      ...as long as you have the sqlplus client available.

                       

                      instant client install, read the documentation included.

                      You can download the latest "instant client" from here if you don't:

                      Oracle Instant Client Downloads

                      Choose your OS, 32bit or 64bit and download

                      instant-client-basic... and

                      instant-client-sqlplus...

                       

                      install instant-client-basic into a directory and install instant-client-sqlplus over the top.

                       

                      Cheers,

                       

                      Gaz.

                      • 8. Re: How can I run sql script in background?
                        user4275603

                        I have registered error: SR 3-21935891971

                        Let's hope Oracle Support will help - as SQLcl was not even in list of applications and I have to choose SQL Developer as a product.

                        • 9. Re: How can I run sql script in background?
                          user4275603

                          Everyone for information - Oracle Support does not have a competence to understand the issue. No progress since December. Just asking to add pictures, asked to read Oracle FAQ ... stay away from Oracle products as support is very very bad!

                          • 10. Re: How can I run sql script in background?
                            Gaz in Oz

                            it sounds like you were unfortunate with that SR... that does not mean others will experience that. For example, I have been using Oracle products for a while and will not be stopping using their products. Support with any company can be hit and miss but realistically how can you right off all Oracle products?... That's just ludicrous.

                             

                            If you read the whole thread in its entirety you will see there is a simple work around by using sqlplus for batch jobs. Good luck with what ever other company's products you choose to use instead.

                            • 11. Re: How can I run sql script in background?
                              Turloch O'Tierney-Oracle

                              Bug open - sqlcl currently needs a tty - tty dependency/assumption is in a couple of places - one awkward workaround (among many) is to use 'tcl expect' (Linux/UNIX/Mac only) to provide a tty while either 1/ running in the background, or 2/ running from a cron job.

                               

                              -Turloch

                              SQLDeveloper Team