Working with multiple Trees on the same APEX Page (4.2/5.0)

Ever tried to put multiple Trees on the same page in APEX 4.2?

Here's my example:

On the left Tree the "Selected Node Link with" is set to hidden item P1_TREE1, on the right Tree the value that sets the tree is set to P1_TREE2. At least it should do that, but by default if you're values are not unique - in this case empno is the value behind both trees - it doesn't work...
Your first tree will be highlighted twice; one time for ALLEN and one time for SCOTT and not as it should be, on the left ALLEN selected and on the right SCOTT selected.

To fix this issue in APEX 4.2, you need to adapt your select statement so you have unique values for both trees. I typically put a meaningful character in front of the value; for example you can concatenate 'N' for new values (new tree) and 'O' for old values (old tree).

So that fixes the issue of the "Selected Node Page Item" issue with non unique values.
Behind the tree implementation in APEX 4.2, jsTree is used. APEX is not using the most recent version and the way the JavaScript is coded doesn't really work that nicely. If you really like jsTree and for example want to search in it, or you have a very large dataset, check out Tom Petrus' tree plugin and explanation.

So what about APEX 5.0? The tree implementation in APEX 5.0 has the same functionalities, but is a lot better. Behind the scenes jsTree is not used anymore, instead the tree that is also used in the Page Designer is used. The issue with unique values is off the table, so no worries anymore in APEX 5.0.

APEX 5.0: pimping the Login page

When you create a new application in APEX 5.0, the login page probably looks like this:

I love the build-in login page of APEX itself - luckily it's easy enough to build that in our own apps too. Thank you APEX Dev team!

The first step is to change the region type to be of Login Region Template:

We want to add a nice icon on top of the Login text. You can use the Icon CSS Class in the Region options - in this case I opted for fa-medkit:

Next up is making the Login button bigger and make it the complete width like the items.

In APEX 5.0 you can use the Template Options to do that:

Once we stretched the Login button it fits the entire size.

Next up is getting some icons in the username and password field.

For the username we use the "icon-login-username" css class.

Instead of the label we make that hidden and specify a placeholder, so before you start typing you see the word username and when you start typing the text disappears.

For the password field we do the same thing, but for the css class we specify "icon-login-password".

Finally your login screen looks like this:

Great? Absolutely - and so easy with APEX 5.0!

What's next? Is there anything better? Euh... yes, what about live validation?
Sure we can do that in APEX 5.0 without too much hassle :)) Thanks once again APEX Dev Team!

In the item make sure the item is set to Value Required and add in the Post Text following span:

That will give you a nice visual indication if you entered text:

Cool? Creating login pages in APEX 5.0 is ... (you fill in the word)

Interested in more? We're doing an APEX 5.0 UI Training in May.

Loading CSV files with special characters in Oracle DB

I often need to load the data of Excel or CSV files into the Oracle Database.

Ever got those annoying question marks when you try to load the data? or instead of question marks you just get empty blanks when the file is using special characters? Here's an example:

My database characterset is UTF-8, so ideally you want to load your data UTF-8 encoded.

With Excel I've not found an easy way to specify the encoding to UTF-8 when saving to a CSV file.

Although in Excel (OSX) - Preferences - General - Web Options - Encoding, I specified UTF-8, it still saves the file as Western (Mac OS Roman).

I've two workarounds I use to get around the issue. Open the file in a text editor e.g. BBEdit and click the encoding option and select UTF-8.

Another way is to open Terminal and use the iconv command line tool to convert the file

iconv -t UTF8 -f MACROMAN < file.csv > file-utf8.csv

If you get a CSV file and you want to import it in Excel first, the best way I found is to create a new Workbook and import the CSV file (instead of opening directly). You can import either by using File - Import or Data - Get External Data - Import Text File. During the import you can specify the File origin and you can see which data format works for you.

After the manipulations in Excel you can save again as CSV as outlines above to make sure you resulting CSV file is UTF-8 encoded.

Finally to import the data you can use APEX, SQL Developer or SQLcl to load your CSV file into your table.

Oracle Database Tools updated - check out SQLcl

Today Oracle released new versions of:

• SQL Developer 4.1 EA2
• SQL Developer Data Modeler 4.1 EA2
• SQLcl (below SQL Developer downloads)

Also Oracle REST Data Services 3 got a new EA2 version.

You may want to check Kris Rice's blog for new features.

I already blogged about all of the tools before, but not yet about SQLcl.

This is a command line tool, I call it "SQL*Plus on steroids" (or as Jeff calls it SQL Developer meets SQL*Plus). It's particularly useful when you're on your server and quickly need to run some queries. Or if you're a command line guy/girl all the time, this tool is for you.

Here's a screenshot how to connect to your database with SQLcl from Linux.

Typing help will show you a list of quick shortcuts.

For example if you type APEX you get a list of your APEX applications

What I really like about SQLcl is that it formats the output so nicely. With SQL*Plus you had to set column widths, page sizes etc. Not with SQLcl, it's smart and formats it nicely.

Next to that you can quickly output your query in JSON format by typing "set sqlformat json":

There're many more features - a good starting point is this presentation and video by Jeff Smith.