Wednesday, October 7, 2015

Using CodeMirror in your APEX Application

I just had a look at a blog post by Connor McDonald where he's asking for opinions how code should be presented on the AskTom website.
In my opinion I would love to have the line numbers removed from the actually code and instead let the get generated by a code highlighter.
With APEX 5 the development team uses CodeMirror for the App Builder, meaning the needed library is already included with every installation of APEX 5.

Getting your code highlighted

First of all I'm assuming you put your code into some sort of a tag, otherwise it wouldn't possible to distinguish code from other text. I did a quick check on the AskTom website and they seem to use the <pre> tag, so I'll use the same to demonstrate. Let's prepare a single page for displaying nice highlighted code.

Update page JavaScript and CSS attributes

Add following to JavaScript File URLs:
#IMAGE_PREFIX#libraries/codemirror/4.4/codemirror-custom.min.js
#IMAGE_PREFIX#libraries/codemirror/4.4/mode/sql/sql.min.js

Add this to the "Execute when Page Loads" sections:
apex.jQuery(".code").each(function() 
{ 
  var myThis = apex.jQuery(this);
  var myCode = myThis.html();
  var myMIME = myThis.attr("data-code-mode");
  myThis.empty();
  CodeMirror(
    this,
    {
      value: myCode,
      mode: myMIME,
      lineNumbers: !myThis.is('.nolinenumbers'),
      readOnly: true
    }  
  );      
});
This code searches all elements having class "code" and uses the contained html as the code to highlight. The language is derived from an attribute called "data-code-mode", if you want to use something else than what is included in the sql.min.js file you need to include additional files from  the CodeMirror mode directory.

In CSS File URLs add the CodeMirror gloabl css file:
#IMAGE_PREFIX#libraries/codemirror/4.4/codemirror-custom.min.css

Finally specify this for Inline CSS:
.CodeMirror {
    height: auto!important;   
}

Adding code blocks

Now just add a new region to the page, I have used static content for now as this is just an example.
The content of the region is the following:
This is some text first...
Now show code:
<pre class="code" data-code-mode="text/x-plsql">DECLARE
  l_data VARCHAR2(32767);
BEGIN
  NULL;
END;</pre>
More text below code block.
now using a div for code:
<div class="code" data-code-mode="text/x-plsql">
SELECT user
  FROM dual;</div>
Nice, isn't it?

Finally some code without line numbers 
<span class="code nolinenumbers" data-code-mode="text/x-plsql">SELECT * FROM emp;</span>
where we just select everything from the famous emp table.

And the rendered result looks like this:

This is all more or less a quick hack, but I hope you get the idea and can make some good use of it.

Tuesday, October 6, 2015

Using Wallet Authentication with SQLcl

Ever since SQLcl came out I was waiting for it to support wallet authentication.
Today the waiting is over and all you need to do is grab the new SQLcl release from the SQL Developer download page. (Hint: The download link is at the bottom of the page.)
Now that you have the latest release let's prepare your client machine to use credentials stored in a wallet.

Preparing the for wallet authentication

First of all you need an Oracle client installed as wallet authentication is not possible using the thin driver. Make sure to have your client home on 12.1.0.2.x otherwise SQLcl can't make use of it.

Then update your sqlnet.ora file by adding following lines:
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = D:\ora_wallet)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE
Make sure to update the line where the directory is set to match the location where you want to store your wallet. The last line allows the wallet to override any existing OS authentication setting.

Now modify your tnsnames.ora and add another entry which points to the database you want to connect to later. Give it a recognizable name because you will need it again when saving the credentials in the wallet. I will use "wallet_test" as the tnsname in this post.
If you use different users on a single database you have to add one tns entry for each user. The reason is that there is a one to one relationship between a credential and a tns entry.

Next step is to create the directory where your wallet should be stored.
Afterwards create the wallet by using the orapki utility from the Oracle Client:
orapki wallet create -wallet "D:\ora_wallet" -pwd "test_1234" -auto_login_local
Now store the credentials in the wallet by using the mkstore tool with following syntax:
mkstore -wrl "D:\ora_wallet" -createCredential tnsname username password
You can also emit the password and then you'll be asked for it by the tool.
Enter the wallet password and wait for mkstore to exit.

Finally test your setup by using good old SQL*Plus:
sqlplus /@wallet_test

Using the wallet with SQLcl

SQLcl is a Java application and therefore uses jdbc to connect to the database.
The latest release added support for custom jdbc connect strings and that is what we actually need.
Connecting with SQLcl using the above created credential looks like this:
sql /@jdbc:oracle:oci8:@wallet_test
The syntax might look a bit strange compared to the SQL*Plus one but you need to put the "/@" before the custom jdbc connection string and still prefix the tnsname with an "@".

Enjoy connecting to your database without specifying a password on the command line.
The main use case will most probably be for batch scripts, but I think switching to wallet authentication as much as possible also helps when you have to work in public places.

Thanks to the SQLcl team for adding another great feature.

P.S.: @Kris, the next blog post is on you. ;-)