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/[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.

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/[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

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

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

Gaz,

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

cheers,

macdoor

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.

macdoor

Gaz

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

macdoor

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/[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

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

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

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.