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!

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

ptflMay 11 2018 — edited May 16 2018

I just noticed, thanks to a typo, that variables I created in one js script stay available in SQLcl after execution and can be (in my case accidentally) referenced by subsequent scripts.

If I exit SQLcl and restart it again, it looks like variables are cleared -- 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?

SQLcl version is 18.1.1, Oracle Server is 11g

My simple test of this is:

create "test_1.js" with single line of:

test_variable = "this is a test";

create "test_2.js" with single line of:

ctx.write(test_variable + "\n");

run the two scripts:

script "test_1.js"

script "test_2.js"

Output should be "this is a test"
This post has been answered by Gaz in Oz on May 11 2018
Jump to Answer

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 May 11 2018
3 comments
203 views