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. ;-)

Using conditional compilation for APEX dependent code

I'm currently working on a new release of my PPTX Creator Package.
While adding parts which would only compile if APEX is installed in the database I thought maybe it's a bad idea to introduce a hard dependency as a lot of the code doesn't need APEX installed.
So I needed to come up with a way to allow installation without APEX, but also enable additional functions when APEX is installed.
The options I checked where:

1. Split into APEX-specific and generic package
2. Offer two versions of the package
3. Use conditional compilation

From the titel you might already know which option I chose, but let's briefly look at the implications of the options.

Option 1:

Splitting into two packages seemed like an immediate win as it feels natural for the use case. However when looking a bit further it could lead to the need of exposing internal parts of the generic package.
Running on 12c you could use the ACCESSIBLE BY clause to mitigate, but not everyone is on 12c (yet). A good blog post on the ACCESSIBLE BY clause can be found here.

Option 2:

I immediately moved over that option, just imagine the maintenance effort:
It would be a like fixing two different packages with a lot of copy-and-paste code back and forth.
No I don't want that!

Option 3:
I decided to use conditional compilation for the APEX specific features. For those of you who don't know conditional compilation yet, have a look at this Oracle Magazine Article by Steven Feuerstein from 2006 and also check out the links in that article.
In short conditional compilation allows you to exclude or include parts of the package during compilation based on conditions specified. A typical use case for conditional compilation is having one package which conditionally compiles with different code based on the Oracle Release.

So I had decided to use conditional compilation, but then I realized that I hadn't thought of how to determine if APEX is installed. Unlike the version use case, for which the database provides the DBMS_DB_VERSION package, there is no such thing for verifying installed components.
So let's start with how to generally check if APEX is installed.
If you have DBA rights you can use following query:

SELECT comp_name, version, status
  FROM dba_registry
 WHERE comp_name = 'Oracle Application Express'
;

Without DBA rights it's a bit different, as there is no ALL_REGISTRY table in the dictionary. There is a USER_REGISTRY table but that doesn't help either.
However there is something called ALL_REGISTRY_BANNERS and we can use that one with any user. To check if APEX is installed simply run the following query:

SELECT banner
  FROM all_registry_banners
 WHERE banner LIKE 'Oracle Application Express%'
;

Now let's move on to finding a means to use the query result for controlling the conditional compilation. You can also use user-defined flags for controlling the compilation process, below show how to set a flag called apex_installed for your session.

ALTER SESSION SET plsql_ccflags = 'apex_installed:1';

You can reference this flag in conditional compilation blocks within your code.
To recap, we now have a means to check whether APEX is installed and we are able to set a flag to be used by the conditional compilation.
Putting everything together I ended up with an install script similar to the code code.

SET define '^'
define HASAPX = '0'
column apx new_val HASAPX

SELECT '1' AS apx
  FROM all_registry_banners
 WHERE banner LIKE 'Oracle Application Express'
   AND ROWNUM = 1
;

ALTER SESSION SET plsql_ccflags = 'apex_installed:^HASAPX';
@@install_package.sql

Within the package you surround the code blocks with something like

$IF $$apex_installed = 1 $THEN
-- Put APEX specific code here
$END

To catch the case when the flag isn't set before compilation include an error clause like below

  $IF $$apex_installed IS NULL $THEN
    $ERROR 'Set CCFLAG apex_installed to either 0 or 1.' $END
  $END

That's all for now folks, but stay tuned for the next release of my PPTX Creator package with much enhanced functionality.

APEX Dictionary - Who uses a column in filters?

I just had the requirement to find out if some users have filters defined on a certain column of an interactive report.
First try was looking at the saved reports view in the application builder, but that didn't help.
And again the APEX Dictionary Views to the rescue.
Following query can be used to check if a column is used in a column filter or highlight.

SELECT r.created_by
     , condition_type
     , cond.condition_name
     , condition_column_name
     , cond.condition_operator
     , cond.condition_expression
     , cond.condition_expression2
     , cond.condition_display
  FROM apex_application_page_ir_cond cond JOIN
       apex_application_page_ir_rpt r
         ON r.application_id = cond.application_id
        AND r.page_id = cond.page_id
        AND r.report_id = cond.report_id
 WHERE cond.application_id = :application_id
   AND cond.page_id = :page_id
   AND cond.condition_column_name = :column_name
;

Note: It will not find column usage in a row filter.

May the APEX Dictionary be with you!

APEX Dictionary - Comparing two Interactive Reports

Recently I again had the requirement to compare two Interactive Reports from different applications.
Using the power of the APEX Dictionary this can be done quite easily.

WITH prod AS
(
SELECT column_alias, application_id, report_label, help_text
  FROM apex_application_page_ir_col
 WHERE page_id = :page_id_prod
   AND application_id = :app_id_prod
), dev AS
(
SELECT column_alias, application_id, report_label, help_text
  FROM apex_application_page_ir_col
 WHERE page_id = :page_id_dev
   AND application_id = :app_id_dev
)
SELECT NVL(prod.column_alias, dev.column_alias) column_alias,
       prod.report_label prod_label,
       dev.report_label dev_label,
       CASE WHEN prod.column_alias IS NULL THEN 'Only DEV'
            WHEN dev.column_alias IS NULL THEN 'Only PROD'
            WHEN prod.report_label != dev.report_label 'Differs'
            ELSE NULL
       END status
  FROM prod FULL OUTER JOIN dev ON (prod.column_alias = dev.column_alias)
ORDER BY NVL(prod.column_alias, dev.column_alias)
;

I used "prod" and "dev" as identifiers for the different settings.
This time I was mainly looking for the report labels, but you can easily extend the query to include additional fields from the APEX_APPLICATION_PAGE_IR_COL view.

This has been in my stash for a while but I didn't publish it before.
Oliver Lemm just started a series of APEX Metadata related posts and made me think let's share this snippet.

May the APEX Dictionary be with you!