tag:blogger.com,1999:blog-376210592024-09-04T14:05:46.378-07:00Kris' blogThis blog has moved to <a href="http://krisrice.io">http://krisrice.io</a>
Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comBlogger179125tag:blogger.com,1999:blog-37621059.post-21456390546848884002017-09-14T13:34:00.000-07:002017-09-14T13:34:02.322-07:00Demo 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" \
-HAnonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-27934678831716266192017-09-06T14:49:00.000-07:002017-09-06T14:49:00.127-07:00ORDS 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>.&Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-21999785381081024492017-07-12T07:57:00.002-07:002017-07-12T07:57:15.930-07:00Profiling 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
Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-28880300288794260512017-07-11T15:07:00.002-07:002017-07-11T15:07:30.960-07:00SQLcl 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.
Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-22013636497381519602017-06-29T12:56:00.000-07:002017-06-29T12:56:31.867-07:00Parameterizing 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-20840619092441913202017-06-01T05:49:00.000-07:002017-08-04T09:53:24.440-07:00Oracle REST Data Services and Docker
TL;DR
1) check out https://github.com/krisrice/docker-ords-sqlcl-apex
2) Download ORDS , SQLcl; optionally 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.10Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-89291865966395062462017-04-13T08:45:00.000-07:002017-04-13T08:45:00.514-07:00ORDS 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-14119052734960189322017-04-13T06:09:00.002-07:002017-04-13T06:09:35.326-07:00DB 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-14657135442094452432017-01-12T07:32:00.002-08:002017-01-12T07:32:37.425-08:00How 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.  Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-13476483893863600672016-11-30T15:52:00.000-08:002016-11-30T16:14:40.321-08:00SQLcl custom Input prompt and validationsAnother 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-30017677035075874002016-11-17T06:51:00.000-08:002016-11-30T16:14:40.334-08:00ECMA Script 6 / Nashorn / Java 9 and SQLclThis 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-62066439395240293402016-11-16T12:28:00.001-08:002016-11-30T16:14:40.354-08:00Import APEX apps now easier with SQLclHopefully 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-toolsAnonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-80025866492293875792016-11-15T07:55:00.000-08:002016-11-30T16:14:40.363-08:00SQLcl as a library in existing programsI 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 triedAnonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-58237908571720774272016-11-14T06:27:00.000-08:002016-11-30T16:14:40.349-08:00Adding 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-27511308496399608752016-10-10T17:21:00.001-07:002016-10-11T06:20:10.365-07:00Export 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-53388335372517582392016-09-28T08:50:00.002-07:002016-09-28T08:50:31.847-07:00ORDS 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-30391622727338220972016-06-22T12:37:00.000-07:002016-11-30T16:14:40.338-08:00Kill DB sessions the easy way with SQLclSeems 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#
Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-494799768387718132016-06-16T10:23:00.001-07:002016-06-16T10:23:38.655-07:00SQLcl and Query Change NotificationThe 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-17290166272197073762016-03-14T05:56:00.002-07:002016-03-14T05:56:53.383-07:00ORDS and PL/SQLSeems I've never posted about PL/SQL based REST endpoints other than using the OWA toolkit. Doing the htp.p manually can give the control over every aspect of the results however there is an easier way.
With PL/SQL based source types, the ins and outs can be used directly without any additional programming. Here's a simple example of an anonymous block doing about as little as Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-434225892906052632016-03-04T10:12:00.000-08:002016-06-10T13:03:58.829-07:00DIY SQCL Commands
As mentioned once or twice or 100 times, sqlcl exposes javascript scripting with nashorn to make things very scriptable. To learn more on Nashorn itself there's a lot of great write ups such as http://www.oracle.com/technetwork/articles/java/jf14-nashorn-2126515.html So far, the scripting examples have been along the lines of conditional or looping of existing sqlcl commands.
Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-47746744558224382142016-02-29T07:54:00.001-08:002016-06-10T13:03:58.834-07:00SQLCL Monitoring itself with LongopsLongops is a great way to monitor things that take some time to do work. There's an easy example of using longops on oracle-base here. I borrowed the script from there and put it into a file named my_slow_thing.sql. Now here's a nice example of what's possible with sqlcl.
The easy way to using this is add it to your login.sql which is what I did. Of course the script could Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-3980991168642024632016-02-01T14:22:00.002-08:002016-06-10T13:03:58.823-07:00Putting SQL in the corner with Javascript in SQLCLHere'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
Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-90122475018184285802016-01-07T11:22:00.001-08:002016-06-10T13:03:58.860-07:00Tuning SQL with Javascript in SQLCLIn 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 Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-91916480083860719952015-12-15T13:14:00.000-08:002016-06-10T13:03:58.862-07:00Carbonated SQLCL - aka SODA support Oracle Database 12.1.0.2 introduced JSON support. This is supported in REST Data Services already and now in the latest SQLCL. This means ORDS can serve up the access to client applications and developers can have a command line way to interact and test features/queries/... in a nut shell a shell for JSON document storage. To use this feature the database will be Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.comtag:blogger.com,1999:blog-37621059.post-18686292936533178242015-10-15T06:39:00.000-07:002016-06-10T13:03:58.800-07:00SQLCL - Blob loading ALL the files
The github repo for all the things the DB Tools team does will be posted here https://github.com/oracle/Oracle_DB_Tools This will include sqlcl , sqldev, modeler, ords , and who knows what other things we have up our sleeves to post. This repo is going to be made up of examples and getting started things like a new extention for sqldev, custom transformation for modeler, ords Anonymoushttp://www.blogger.com/profile/01592459412450086148noreply@blogger.com