Thursday, January 12, 2017

How to add an NCSA style Access Log to ORDS Standalone

What ORDS Standalone is

     ORDS Standalone webserver which is Eclipse Jetty, .  For the standalone, ORDS sends output to STDOUT, it runs on the command line.  That means there's nothing like a control commands like startup, shutdown,status nor log files, access logs.  It's bare bones intentionally to get up and running fast.  Then it's recommended for anything with lots of volume more to a more full featured webserver like Tomcat or WLS.

Standalone Startup

    With all that said, it is quite simple to use the standalone for most cases.  When doing this the most important thing is just a startup script so that when the machine/db starts the ORDS instance starts also.  There's an init.d script in this blog post that can be used and adjusted as needed :

Standalone Access Logs

  The other thing much needed from a standalone instance of ORDS would be an access log to see what it's getting for traffic.  Here's where being Jetty based is a great thing.

  This is documented for some solaris workaround only so probably missed by most.  ORDS Standalone allows for customization of the Jetty server itself.  Looking at the ords configuration directory there's a <path>/standalone.  This will have a file named which looks like this which is the name/value pairs of what ORDS will configure.

#Thu Jan 12 09:56:19 EST 2017

Jetty is configurable by xml documents and the documentation has some really good example of what's possible.  This blog post is going to focus on the Access Log section here:

In that same directory, create a folder named "etc" and in there create a file named "jetty-http.xml" . Then drop this into the file

<?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "">
<Configure id="Server" class="org.eclipse.jetty.server.Server">
    <Ref id="Handlers">
      <Call name="addHandler">
          <New id="RequestLog" class="org.eclipse.jetty.server.handler.RequestLogHandler">
        <Set name="requestLog">
          <New id="RequestLogImpl" class="org.eclipse.jetty.server.NCSARequestLog">
        <Set name="filename"><Property name="jetty.logs" default="/tmp/"/>ords-access-yyyy_mm_dd.log</Set>
        <Set name="filenameDateFormat">yyyy_MM_dd</Set>
        <Set name="retainDays">90</Set>
        <Set name="append">true</Set>
        <Set name="extended">false</Set>
        <Set name="logCookies">false</Set>
        <Set name="LogTimeZone">GMT</Set>

Now when ORDS is started again you'll have a file that was specified in the xml file.  This example is  /tmp/ords-access-2017_01_12.log  with an normal NCSA/Apache looking access log.

0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:32 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:33 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:33 +0000] "GET /ords/klrice/test/morse?text=3362 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:33 +0000] "GET /ords/klrice/test/morse?text=5728 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx.%29%29%22%29.%28%2C%27%2C HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27jSwGPl%3C%27%22%3EbBKXcr HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%29%20AND%208651%3D9722%20AND%20%289950%3D9950 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%29%20AND%203741%3D3741%20AND%20%285130%3D5130 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%20AND%204853%3D4504 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%20AND%203741%3D3741 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27%29%20AND%201525%3D5863%20AND%20%28%27aKGY%27%3D%27aKGY HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27%29%20AND%203741%3D3741%20AND%20%28%27jcNS%27%3D%27jcNS HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27%20AND%204691%3D5794%20AND%20%27rhDV%27%3D%27rhDV HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27%20AND%203741%3D3741%20AND%20%27FJPb%27%3D%27FJPb HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%25%27%20AND%206423%3D5178%20AND%20%27%25%27%3D%27 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%25%27%20AND%203741%3D3741%20AND%20%27%25%27%3D%27 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%20AND%209677%3D8181--%20embz HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%20AND%203741%3D3741--%20ldng HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=%28SELECT%20%28CASE%20WHEN%20%284135%3D9257%29%20THEN%204135%20ELSE%204135%2A%28SELECT%204135%20FROM%20INFORMATION_SCHEMA.PLUGINS%29%20END%29%29 HTTP/1.1" 200 - 


Here's the simple steps:
1- mkdir <ords config directory>/standalone/etc
2- vi jetty-http.xml
3- paste the above xml file and adjust the log location
4- Restart ORDS

Wednesday, November 30, 2016

SQLcl custom Input prompt and validations

Another quick twitter inspired blog post inspired by the SQLcl account itself.

ACCEPT is a great way to get input but validation has to happen elsewhere as there's no hooks to do something like validate a number in a range without running a sql or plsql that does the validation.

As I've covered in many examples on this blog and in our github repo of examples, SQLcl has the ability to do scripting.  Using a few lines of javascript, I can prompt, validate, loop waiting for a better answer from the user.  This is a very simple example that loops waiting for the correct answer printing a message with each failure.  While this is trivial example it shows how easy it could be to have a custom script that prompts for values then with some scripting validate those values and put it into SQLcl's list of variable used for substitutions.

Thursday, November 17, 2016

ECMA Script 6 / Nashorn / Java 9 and SQLcl

This blog post brought to you by the letter M as in Martin .  . He asked me on twitter if SQLcl via Nashorn could use ECMA Script 6 features yet.  The answer is yes.  So, for the brave that want to try out the latest greatest ECMA Script 6 features it can be done with SQLcl.

This is a good reference for what's in version 6 that could be useful.  The only caution is not everything is built into nashorn quite yet.  For Example destructuring is not there.

The Template Literals are probably to me the most interesting thing since it cleans up all things like string concats and multi line string.

Here's how to get it working:
Step 1: Get Java 9
Step 2: Set the enviroment variable : APP_VM_OPTS="-Dnashorn.args=--language=es6"
Step 3: Go.

Complete side note as you can see in my default PS1, apple doesn't and has never understood you don't add an 's to a noun ending in a s.

kriss-MacBook-Pro:bin klrice$ export APP_VM_OPTS="-Dnashorn.args=--language=es6"
kriss-MacBook-Pro:bin klrice$ ./sql klrice/klrice

SQLcl: Release 4.2.0 Production on Thu Nov 17 09:22:35 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Nov 17 2016 09:22:36 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production

SQL> script /Users/klrice/workspace/raptor_common/examples/sql.js
Using Binds:[object Object]

  *****    SIMPLE LOOP OF LIST OF LIST                ***********

  *****    SIMPLE LOOP OF LIST OF NAMES WITH BINDS     **********

Now this probably looks just like the script from my first blog on how to use javascript

However the script itself is much nicer to read ( to me anyway ) . What will stand out is multi line string with using a back tick.  Also is the evaluating of variables inside the quoted string. No more string concatenations!

Wednesday, November 16, 2016

Import APEX apps now easier with SQLcl

Hopefully by now everyone knows there's the ability in SQLcl to use Javascript and the same javascript can be used to make custom commands extending what SQLcl offers out of the box.

Also, I hope everyone knows we are posting examples of this to github as a base for anyone to learn and try it out.  Just in case here's the link to the GitHub location

The last example I did was for ApexExport which took the tried and true ApexExporter java utility that ships with Apex and converted it to JavaScript.

One of first questions was from Vito about an import counterpart.

Here's that counterpart which is leveraging apex_application_install for setting options to be used. This screenshot is showing loading the command straight from github which I wouldn't recommend if there is ever a need to be offline using the command.  There's a Download/Clone button right on the github space home page then all the file are local and be used anytime regardless of network connectivity.

When the command is called it echos out all the options being used. In the case of multiple workspaces and none being specified right or wrong it's going to use this sql to determine which to use:
  select min(workspace_id) wsID  from  apex_workspace_schemas where schema = user

If that's not correct, no problems just pass in -workspaceid

KLRICE@orcl >apximp  -file f102.sql -offset 123 -name Kris -alias rice  -installSupportingObjects
*** KLRICE *** 
** Importing with the following options **
Application Alias :rice
Workspace ID :1930920493850173
Application Name :Kris
Offset :123

The script is here:

Tuesday, November 15, 2016

SQLcl as a library in existing programs

I got a question over the weekend if SQLcl could be leveraged as library from inside a Jython program. This may seem like it's the same thing as adding jython to SQLcl to use instead of Javascript but it's a significant difference. This method allows for the use of SQLcl in any existing program. That means when the program needs to interact with the database, all the code in SQLcl that is tried and true can be leveraged.

This is a simple example to prove it's possible.  First the jar files need to be added to Jython's command line.

# all files in sqlcl/lib/*
# adjust to your install ( unzip ) of sqlcl
for  f in $(ls $LIB/*.jar); do
 echo $f
echo --- $CP ---

#Start up jython 
jython -J-cp $CP 

Now that the needed libraries are in the path. It's quite easy to start using the same methods and classes that have been shown in various javascript examples.  This example gets a connection on it's own then instantiates SQLcl for programmatic use.

#import various things
from java.sql import DriverManager
from oracle.dbtools.db import DBUtil
from oracle.dbtools.raptor.newscriptrunner import *

#plain ol jdbc connection
conn  = DriverManager.getConnection('jdbc:oracle:thin:@//localhost:1521/orcl','klrice','klrice');

#get a DBUtil but won't actually use it in this example
util  = DBUtil.getInstance(conn);

#create sqlcl
sqlcl = ScriptExecutor(conn);

#setup the context
ctx = ScriptRunnerContext()

#set the context

#change the format
sqlcl.setStmt('set sqlformat json');;

#run the sql
sqlcl.setStmt('select * from emp');;

The output of this is simply the json output.

Disqus for Kris' Blog