SQLcl: MCP Server & SQL Prompt

According to the github doc for SQLcl (https://github.com/oracle/oracle-db-tools/blob/master/sqlcl/SCRIPTING.md)  "util" is a global variable that the scripting engine feature of SQLcl initializes.  Since it is based on a connection, it must be getting initialized only after a connection is made and, as you point out, that initialization seems to get delayed until after the js script that makes the connection terminates.  It is not clear to me if that is a necessary limitation of the scripting engine or just a bug in SQLcl.  Perhaps someone with more knowledge in this area can comment.

For now, I suppose you could always use the technique described in http://krisrice.io/2016-01-07-tuning-sql-with-javascript-in-sqlcl/ to create a secondary connection and assign it to a "util2" variable to run your sql query.  You just need to figure out in advance the JDBC URL to pass into the DriverManager.getConnection call since in your case there is no existing connection to call getMetaData().getURL() from.

Cheers

As an alter native, you could try calling connect.js that does the connection and initialises util, then call the "rest of your code.js".

I couldn't find any information on connnect.js?  Do you have a link?

I re-wrote my script so that it could be called as follows:

• var dbConn = loadWithNewGlobal('myConnection.js');
• dbConn.init()

The dbConn.init() function will run the CONNECT command, then I have a second function that can continue where it left off dbConn.runSQLcheck().

But even if I wait and manually type the statement to run...

• SCRIPT
• dbConn.runSQLCheck()
• /

...I will still get the error

• "util" is not defined in file ...

But yet I can still run the follow and it works fine...

• var ret = util.executeReturnList(sqlStr, null);

So even though util is a global variable, my instance of dbConn has retained it's own copy of util?

I've come up with a work around to pass the util backwards by creating a function inside my dbConn...

•     function passbackControls(utilRef, connRef, ctxRef) {
          util = utilRef;
          conn = connRef;
          ctx = ctxRef;
      }

Then, immedicately after running the CONNECT statement, I call this function to update the references...

• sqlcl.setStmt('SCRIPT \n dbConn.passbackControls(util,conn,ctx); \n/');
• sqlcl.run();

Now the util is correctly set and can be used as required.

Should I be including anything else in the passback function?  I added conn and ctx just encase, but maybe they aren't needed?

Hi StuartH,
Could you share your complete example? Would be help a lot to adopt the solution for our problem.
Greetings

Hi User_J13V3
This was from a long time ago; so it's not fresh in my head. But looking at my current release of the code, I never went with the solution documented above that uses the "passbackControls" function".
I can't share you the complete code (as it's over a 1000 lines); but below I've removed a lot of application-specific code and tried to just stick to the problem identified on this ticket. It should hopefully get you going in the right direction

login.sql

--Prompt to show login.sql is called twice
PROMPT login.sql started . . .

--Install Connection functions
SCRIPT connection.js

--Database selection and connection
SCRIPT
dbConn.init();
/

connection.js

if (!dbConn) var dbConn = new function() {

  //Variables
  var existingConnection = false;
  var dbName;
   //Database lookup
   var databases = {
       SAMPLE1: {sid: 'SAMPLE1', host: 'server' , port: 1521}
       SAMPLE2: {sid: 'SAMPLE2', host: 'server' , port: 1521}
   };

  //Startup function
  this.init = function() {

       //Get current connected database (if /nolog switch was NOT used, or on second run-through after CONNECT statement)
       if (typeof conn != 'undefined') {
           existingConnection = true;
           var connStr = conn.getMetaData().getURL().split('/');
           dbName = connStr[connStr.length-1];

       } else {

           //CODE REMOVED that sets the dbName var from environment variables or user prompt
           //dbName = ;

       }

       //Database selection
       var dbo = databases[dbName]

       //CODE REMOVED to determine/prompt for username/password
       //var dbUser = 
       //var dbPass = 

       //Startup connection
       if (!existingConnection) {

           //NOTE: this will cause the login.sql to re-execute and call this dbConn.init() function again but where existingConnection = true
           print('Connecting to ' + dbo.sid + ' . . .');
           sqlcl.setStmt('CONNECT '  + dbUser 
                       + ((dbPass) ? '/' + dbPass : '')
                       + '@//' + dbo.host 
                       + ':'  + dbo.port 
                       + '/'  + dbo.sid);
           sqlcl.run();

           //Check it was successful
           var errCode = ctx.getProperty("sqldev.last.err.message.forsqlcode");
           if (errCode) {
               this.exitWithError(errCode);
               return;
           }

           //Confirm connected          
           existingConnection = true;

           //If successful the connect command will run another instance of login.sql. So kill this one
           return;
       }

       //Check for password expiry (E.G. the main reason for this ticket, because util is null)
       var sqlStr = 'SELECT account_status,expiry_date,FLOOR(expiry_date-TRUNC(sysdate)) expire_days FROM user_users WHERE username = user')
       var ret = util.executeReturnList(sqlStr, null);
       if (ret[0].ACCOUNT_STATUS != 'OPEN' || new Date(ret[0].EXPIRY_DATE) < new Date()) {
           var expireDays = ret[0].EXPIRE_DAYS.toString();
           print('Password will expire in ' + expireDays + ' days');
       }

       //Run config script (alternative to login.sql)
       sqlcl.setStmt('@connection.sql');
       sqlcl.run();

       //Various other application-specific code removed 

  }

   this.exitWithError = function(errMessage) {
       System.err.println(errMessage);
       this.pauseThenExit();
   }

   this.pauseThenExit = function() {
       disconnect();
       print('\n');
       sqlcl.setStmt('HOST PAUSE');
       sqlcl.run();
       quit();
   }

   function disconnect() {
       if (existingConnection) {
           sqlcl.setStmt('DISCONNECT');
           sqlcl.run();
           existingConnection = false;
       }
   }

   function quit() {
       print('\nClosing . . .');
       disconnect();
       System.exit(0); //Can't use the EXIT command while the login.sql is still processing
   }

}();

connection.sql

--Display Format settings
SET echo OFF
SET verify OFF
SET timing OFF
SET termout ON
SET serveroutput ON
SET sqlblanklines ON
SET sqlformat ANSICONSOLE
SET pagesize 40

--Update Window Title
HOST TITLE &_USER&_CONNECT_IDENTIFIER

details posted above now

Thanks for the fast response.
Do you have an idea, why dbConn is undefined if I use it like you mentioned it before? It only works if I use it inside the same script call.

--Install Connection functions
SCRIPT connection.js

--Database selection and connection
SCRIPT
dbConn.init();
/

// Following works but that doesn't help to fix the problem
SCRIPT
load('connection.js');
dbConn.init();
/

undefined (0 Bytes)Not sure?
So does this first statement not run:?

--Install Connection functions
SCRIPT connection.js

I'm not familiar with the load() functon you've used? But if that's what works for you, have you tried it like this...

--Install Connection functions
SCRIPT
load('connection.js');
/

--Database selection and connection
SCRIPT
dbConn.init();
/

The first statement runs fine, but it looks like it "forgets" everything it did in the script call before.
Following loads the file without problems. At least I see the logs of the script. But I can't use the content in the next use of script.

SCRIPT connection.js

The problem is the same if I call connect inside a js script. It will just create an new dbConn object

sqlcl.setStmt('SCRIPT connection.js\n/');
sqlcl.run();

undefined (0 Bytes)Sorry; I'm not sure what the issue is. But might help if I explain what my setup is doing; as sounds like there might be a bit of a miss-understanding?
The first call to...

SCRIPT connection.js

... won't actually preform anything. It ONLY creates an instance of dbConn. You need the second statement to execuate the initiailsation code under .init() in the connection.js

SCRIPT
dbConn.init();
/

The .init() function is where YOU need to perform the CONNECT statement to your database and then exit the function, like so

      if (!existingConnection) {          
          sqlcl.setStmt('CONNECT '  + dbUser 
                       + ((dbPass) ? '/' + dbPass : '')
                       + '@//' + dbo.host 
                       + ':'  + dbo.port 
                       + '/'  + dbo.sid);
           sqlcl.run();

           //Confirm connected (for the next run of login.sql)
           existingConnection = true;

           //If successful the connect command will run another instance of login.sql. So kill this one
           return;
       }

Running CONNECT triggers the login.sql file to re-execute; this time with the util, ctx and conn all defined and ready for you to use however you want.
So then, on the second run of login.sql; that first statement (SCRIPT connection.js) does absolutely nothing, because dbConn is already defined

if (!dbConn) var dbConn = new function() {

Then the second statement will re-execute dbConn.init(), but this time the existingConnection variable will be set to true, so it will skip over the CONNECT statement, and move on to the execute the rest of the code that you want execute.
So maybe make use any use of util, ctx, or conn ONLY happens on the second run of login.sql and dbConn.init(). E.g. should only be run when existingConnection == true

Maybe I didn't describe my issue that well. Here is a smaller script that reproduces my error. I don't even change the connection. I just cannot run dbConn.init();
But if I merge both it works:
But this way the login.sql won't work because dbConn will always be undefined

Maybe something in my environment is wrong?
We use SQLcl 21.3 wirh Oracle DB 19c and JDK 8 with the Nashorn Engine.

ArrayIndexOutOfBoundsException is a bug of SQLcl when it parses the error text without <eval> substring.
Use try-catch to print the original error. This may help diagnose the problem.

Sounds like a different problem then to the one described in this thread. You'd be best to raise a separate thread on it to get an answer from the right people.