Skip to Main Content
Forums

SQLcl: MCP Server & SQL Prompt

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I run sql script in background?

macdoorJan 23 2017 — edited Mar 4 2020

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.

Comments

Gaz in Oz
Answer

In answer to your questions:

|  Variables from js scripts live on in SQLcl past execution of the script?

Yes. Of course.

is there a way to 'clear' all previous variables etc at the end of a script, or start of the next script, without having to exit and restart?

Yes.

Use javascript delete operator to remove the variable.

For example:

SQL> $type test_1.js
test_variable = "this is a test";
delete test_variable;
SQL> $type test_2.js
ctx.write(test_variable + "\n");
SQL> script test_1.js
SQL> script test_2.js
javax.script.ScriptException: ReferenceError: "test_variable" is not defined in <eval> at line number 1
        at jdk.nashorn.api.scripting.NashornScriptEngine.throwAsScriptException(NashornScriptEngine.java:470)
...
Caused by: <eval>:1 ReferenceError: "test_variable" is not defined
        at jdk.nashorn.internal.runtime.ECMAErrors.error(ECMAErrors.java:57)
...etc.
Marked as Answer by ptfl · Sep 27 2020
1 person found this helpful
ptfl

Interesting, this is standard behaviour?

Is there no way to reset the entire environment, rather than variable by variable?

I'm curious about situations where you might be running scripts from different developers, any of which might clash on a variable name in an unexpected way.

ptfl

Gaz in Oz 
Interesting, this is standard behaviour?

Yes. A UI session that gets a variable defined, I would expect it to stay defined until it is explicitly undefined or the UI session is terminated/ended.

Is there no way to reset the entire environment, rather than variable by variable?

Yes, there is a way, which is also standard, terminate and restart the UI session.

Instead of keeping your single session "running" and worrying about how the "developers" have defined javscript variables as "local" or "global", review the script/s before running, approve them, then run  them up in their own sqlcl instances:

I'm curious about situations where you might be running scripts from different developers, any of which might clash on a variable name in an unexpected way.

If a developer "uses" a variable before defining it, then the script is run in a UI session that already had that variable defined and NOT undefined, then sure, it will pick up the value that is already defined. That is a bug in the developers code that used a variable before defining it (and thus not setting/resetting/changing its value). Not the UI's fault, the developers.

To make sure that sort of bug is picked up, run all scripts in their own sqlcl instance. No big deal.

If there were two different UI sessions running at the same time, then those variables would be exclusive to the individual UI session.

For example, if you had 5 scripts to run, from different developers, who's code you haven't reviewed and so can not trust, you could loop through each, in a shell script and run them one by one.

1 person found this helpful
1 - 3

Post Details

Added on Jan 23 2017
11 comments
25,196 views