Forum Stats

  • 3,750,303 Users
  • 2,250,155 Discussions
  • 7,866,922 Comments

Discussions

How can I run sql script in background?

macdoor
macdoor Member Posts: 4
edited Mar 4, 2020 7:03AM in SQLcl

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/[email protected]:8521/nmsb @t.sql

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

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

sql U/[email protected]:8521/nmsb @t.sql &

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

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

The only difference is '&' sign.

Thx.

user4275603
«1

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 23, 2017 5:23AM

    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/[email protected] @t1.sql > t1.log 2>&1

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

    $ cat t1.logSQLcl: Release 4.2.0 Production on Mon Jan 23 20:12:56 2017Copyright (c) 1982, 2017, Oracle.  All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionSYSDATE---------23-JAN-17Disconnected 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/[email protected] @1.sql > t1.log 2>&1$ cat t1.logSYSDATE---------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 0SELECT SYSDATE FROM dual;QUIT;

    $ sql -S -noupdates U/[email protected] @t1.sql > t1.log 2>&1$ cat t1.log23-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.

  • macdoor
    macdoor Member Posts: 4
    edited Jan 23, 2017 12:03PM

    Gaz,

    Thank you very much !

    But to redirect output  can not solve my problem. 

    sql U/[email protected] @t1.sql > t1.log 2>&1

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

    sql U/[email protected] @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

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 24, 2017 5:23AM

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

    sqlplus behaves as expected:

    [[email protected] bin]$ sqlplus -S gaz/[email protected] @t1.sql > t1.log 2>&1 &[1] 15811[[email protected] bin]$[1]+  Done                    sqlplus -S gaz/[email protected] @t1.sql > t1.log 2>&1[[email protected] bin]$

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

    [[email protected] bin]$ ./sql -S gaz/[email protected] @t1.sql > t1.log 2>&1 &[1] 15827[[email protected] bin]$[1]+  Stopped                ./sql -S gaz/[email protected] @t1.sql > t1.log 2>&1[[email protected] bin]$

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

    macdoor
  • macdoor
    macdoor Member Posts: 4
    edited Jan 24, 2017 10:35AM

    Gaz,

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

    cheers,

    macdoor

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 29, 2017 7:08PM

    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.

  • macdoor
    macdoor Member Posts: 4
    edited Jan 30, 2017 9:42AM

    Gaz

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

    macdoor

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 30, 2017 10:03AM

    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/[email protected]:8521/nmsb @t.sql &

    to

    sqlplus U/[email protected]: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.

  • user4275603
    user4275603 Member Posts: 2 Red Ribbon
    edited Dec 29, 2019 9:33AM

    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.

  • user4275603
    user4275603 Member Posts: 2 Red Ribbon
    edited Feb 8, 2020 3:39AM

    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!

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Feb 8, 2020 5:54AM

    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.