Kris Rice
This blog has moved to http://krisrice.io
Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comBlogger179125
Updated: 4 hours 54 min ago
Demo App for REST enabled SQL
Getting Started
The new Oracle REST Data Services SQL over REST. How to enable that is on my last blog post here: http://krisrice.blogspot.com/2017/09/ords-173-beta-introducing-rest-enabled.html
cURL Examples
The simplest way to test this new feature out is with a curl command sending over the SQL.
$ curl -X "POST" "http://localhost:9090/ords/hr/_/sql" \
-H "Content-Type:
ORDS 17.3 Beta - Introducing REST enabled SQL
Download
Got get it on the normal ORDS download page
http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html
Versioning
First and most obvious is ORDS is now on the same versioning scheme as SQL Developer, SQLcl and Oracle Cloud. That is <year>.<quarter>.<patch> and the same tail we've always had which is <julian day>.<HH24>.<MI>. That makes this beta
Profiling a Java + JDBC Application
NetBeans
First, there's NO Java coding needed nor Java source code needed to profile a Java program this way. NetBeans added this a while back up I just found it recently. The ability to attach to any Java program and profile the SQL going across JDBC. The dev team's blog on it is here: http://jj-blogger.blogspot.nl/2016/05/netbeans-sql-profiler-take-it-for-spin.html
SQLcl
SQLcl is our
SQLcl 17.2
New Versioning Scheme
Starting with this release the numbering scheme is changed. All releases will now be the YEAR<period>Quarter<period>build numbers.
So the new SQLcl is 17.2.0.184.0917.
Breaking that down.
17 - Year
2 - Quarter
0 - Patch number
184 - Day in Julian
0917 - hour and minute the build was done.
New Features
Securing Literals which was introduced
Parameterizing Jmeter for testing APEX
A while ago we needed to stress a system by using the APEX Brookstrut demo application. The obvious choice for this was Jmeter. How to setup Jmeter to record web traffic by becoming a web proxy is very known and well written process. Anyone that hasn't seen it, check this PDF and you can see how easy it is. There were a couple issues to get around. First importing the application again and
Oracle REST Data Services and Docker
TL;DR
1) check out https://github.com/krisrice/docker-ords-sqlcl-apex
2) Download ORDS ; optionally SQLcl,APEX
3) Build w/DB connection details
docker build -t krisrice/ords:3.0.10 --build-arg DBHOST=192.168.3.119 --build-arg DBSERVICE=orcl --build-arg DBPORT=1521 --build-arg DBPASSWD=oracle .
4) Run the image
docker run -d -p 8888:8888 -p 8443:8443 --name=ords krisrice/ords:3.0.10
5) Access
ORDS Standalone and URI Rewrites
My last post How to add an NCSA style Access Log to ORDS Standalone explained what the ORDS standalone is and that is based on Eclipse Jetty. Jetty offers far more than ORDS exposed in it's standalone. There's a long list of all the features and configuration options listed in the documentation, http://www.eclipse.org/jetty/documentation/9.2.21.v20170120/
A recent question came up for doing
DB Auditing and ORDS
There seems to be some confusion around how ORDS works with it's connection pooling yet running the REST call as the specified schema.
The connection pool
Consider a 50 PDB env and concurrent users per PDB running some REST stuff. Using a connection pool per PDB would be 50 connection pools. Then if a JET app ( or any HTML5/JS/.. ) is making REST calls Chrome will do this with 6 concurrent
How to add an NCSA style Access Log to ORDS Standalone
What ORDS Standalone is
ORDS Standalone webserver which is Eclipse Jetty, https://eclipse.org/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
SQLcl custom Input prompt and validations
Another quick twitter inspired blog post inspired by the SQLcl account itself.
Asked and answered: Do you support the ACCEPT command? YES. pic.twitter.com/NchmQ6Eegs
— SQLcl (@oraclesqlcl) November 29, 2016
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
ECMA Script 6 / Nashorn / Java 9 and SQLcl
This blog post brought to you by the letter M as in Martin . Follow @martindsouza . 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
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 https://github.com/oracle/oracle-db-tools/tree
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
Adding Reserved command in SQLcl
I saw Stephen's example of checking reserved words in the database from Vertan's day and figured I'd do the same in SQLcl.
#595 #plsql Is it a reserved word? PL/SQL procedure to help you sort that out. Dyn PLSQL example! @oraclelivesql https://t.co/M10kVnsQ3y pic.twitter.com/XFFHOVzNCK
— Steven Feuerstein (@sfonplsql) November 11, 2016
Checked if something is reserved seems like a nice add
Export APEX application with SQLcl
APEXExport has been around a long time for exporting an application and anything else like images, feedback,websheets,.. into a file system so that they can be version controlled. This is a must if there is ever a need to rollback or see what the application was X days ago. This is a java program that is part of the apex distribution. The catch for some folks is that it's a java program and
ORDS 3.0.7 more secure by default
Defaulting PL/SQL Gateway Security
Oracle REST Data Services 3.0.7 went out yesterday. There's an important change that went in to better secure installations by default. It has always been the case that we recommend customers set the validations for the plsql gateway. There has always been a validation configuration option to lock down what procedures are accessible which was outlined in
Kill DB sessions the easy way with SQLcl
Seems I can not tweet these animated GIFs anymore. So this is another very short blog post to show real case for adding a new command into SQLcl which can be downloaded here.
It's hard annoying tedious to find SID and SESSION then alter to kill that when needed. What if there was a simple kill command.
Here a sample one that takes in the 2 options:
kill sid serial#
Example : kill 1 2
OR
SQLcl and Query Change Notification
The database has had Query Change Notification for a while but to use it required a custom program. Such as Tim outlines on his blog https://oracle-base.com/articles/10g/dbms_change_notification_10gR2
Since SQLcl has Nashorn, now it can be integrated with a few lines of javascript to get notified when changes happen. The script is below and posted. The catch is QCN only works on Varchars and
Putting SQL in the corner with Javascript in SQLCL
Here's a pretty small javascript file that allows for running sql in the background of your current sqlcl session. This is a trivial example of a sql script that has a sleep in it to simulate something taking time. It also prints the SID to show it's a different connection than the base.
select 'DB SID ' ||sys_context('USERENV','SID') || ' is going to sleep' bye from dual;
begin
Tuning SQL with Javascript in SQLCL
In case anyone missed it, #sqlcl has the ability to run javascript. This opens a lot of options. Here's a simple example that shows how to using javascript.
Open a new Database Connection
Collect stats on the base connection
Do work on the main connection
Collect stats again
Only Print the stats that changed
In SQL Developer, the autotrace feature has for a long time selected the session