SQLcl: MCP Server & SQL Prompt

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.

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

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

Gaz,

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

cheers,

macdoor

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.

Gaz

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

macdoor

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.

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.

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!

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