OOS Utilities Package

This is a duplicate post originating from OraOpenSource. Normally I try not to duplicate content but in this case I really wanted to get the message out.

Just like any other programming language they're some common tools/features that are not offered natively within the Oracle PL/SQL APIs. As such, most developers start compiling a list of these common tools and create their own utility packages or code snippets.

We're looking to create a master utility package(s), currently called OOS Utils. Before we can do that we need your help! If you have a common snippet of code, procedure, or function that you think will be useful to the entire community, please go to: https://github.com/OraOpenSource/oos-utils/issues and submit a new issue with your suggestion. If you just have an idea but don't have the source code, please submit it anyways and we'll try to add it in.

Hint: If you want to format your code nicely when submitting a recommendation use the following template. 

```sql
your_code_here
```

Note that those are not apostrophes, rather they are backticks (key in the top left of your keyboard).

Once we have enough entries, we'll look at grouping all the suggestions into logical packages and start the development process.

We look forward to your recommendations and can't wait to launch this when it's ready! As always if you want to stay up to date with everything that's going on, please subscribe to our email list.

APEX Backup Script

A long time ago (almost 3 years ago to be exact), I blogged about command line backups for APEX. The script was originally for DOS and required configurations to be stored directly in the script.

Since making the script available I've had numerous requests to update it to support Linux and Mac users. I've done a major overhaul of the backup script which is now available on Github here.

The main changes are that all configurations can be stored in custom.properties and you won't lose your changes when you update the scripts from the repository.

If you have any comments, issues, or feedback please submit them in the project's issues page.

15 Minute Timer on Mac

I need to take a small break every 15 minutes when using my computer. I looked at various timers for OS X to help notify me when 15 minutes were up. I wasn't overly impressed with any of the options that I found.

Then I stumbled upon a neat feature in OS X that will announce the time every hour, half hour, or quarter hour. To enable it, go into System Preferences, then Date & Time. Select the Announce the time check box and the desired interval.

You can also change the voice for the time announcement by clicking on the Customize Voice button.

Though this solution isn't perfect it seems to be working well for me. The only catch is that in noisy environments or when I have the audio off I can't hear the notification.

APEX 5: Creating Sub-Menus

APEX 5 has gotten rid of the old APEX tabs concept and instead replaced it with the APEX Navigation Menu. This is a very positive change and, tied in with the new Universal Theme it, certainly makes life easier.

One thing that I really like is the simplicity to create sub-menus. They're various ways to create sub-menus. This post will cover creating sub-menus from both the page creation wizard and via the Shared Components > Navigation Menu options.  There will be three pages in this example: parent, child, and grand child.

To start, I'll assume that you have one page and one Navigation Menu item called Parent. Next, create a second page called Child using the wizard. In the wizard, configure the Navigation Menu options as shown below:

Finish the page creation wizard. When you run your app you'll now see the Parent/Child menu and sub-menu.

Create a third page, called Grand Child. This time when you get the the Navigation Menu wizard option you can't select the Child page as it's parent. It is shown in the list, but greyed out. To get around this issue, just select "No Parent Selected" and finish the wizard. 

To make the Grand Child page a sub-menu of Child:

• Go to Shared Components > Navigation Menu. 
• Edit the Grant Child list entry.
• Select Child for the Parent List Entry and click Apply Changes.

If you rerun the application you should now see the updated sub-menu structure.

The above example show the default Navigation Menu with the new Universal Theme, which is a side menu. To make it into a top navigation menu with a drop down sub-menus:

• Go to Shared Components > User Interface Attributes.
• Edit the Desktop theme.
• Click on the Navigation Menu tab, and make the following changes:

If you refresh the page the Navigation Menu will now be at the top of the page and has the drop down menu/sub-menu.

ODTUG Oracle Blogs - Now on Twitter

A while ago, ODTUG put a heavy emphasis on communities. As a result, 6 main communities were created: APEX, BI, EPM, Development, DBA, and ADF. Each of these communities have their own site dedicated to them along with a blog aggregator (click on each of their titles for the appropriate page).

The blogs contain an amazing amount of content from the entire Oracle community. The authors include Oracle ACE (Associates, ACEs, and Directors), Oracle employees, speakers from all the major conferences (including Kscope), book authors, etc.

Up until last week the only ways to take advantage of the blog aggregator were to go to the appropriate ODTUG community site or subscribe to the respective RSS feed. Starting today you can subscribe to the following Twitter accounts to get a message in your feed each time a new article is posted for your community.

• APEX: @orclapexblogs
• BI: @orclbiblogs
• EPM: @orclepmblogs
• Development: @orcldevblogs
• DBA: @orcldbablogs
• ADF: @orcladfblogs

Each tweet will also contain the community's appropriate hashtag. ODTUG has the complete hashtag listing here: http://www.odtug.com/hashtag If you're new to Twitter, following your appropriate hashtag can be a quick way to find some excellent and helpful content.

Some tweets will contain the author's Twitter handle (via a @mention). We're currently in the process of updating all the twitter handles for the authors in each community. If you write an article and don't see your Twitter handle show up, just reply to the appropriate Twitter account and we'll fix it.

As always, if you want to add your blog to the list, please email lauren@odtug.com.

Thanks to Adrian Png and Patrick Wolf for their ideas and patience getting this setup!

Installing SQLcl

SQLcl is the new command line tool from Oracle, more specifically from the SQL Developer team. It is currently an Early Adopter (EA) release and you can download it from: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-41ea-2372780.html

After testing it for a while I was hooked and plan on using it as a full time replacement for SQL*Plus (which I think is the intent of the product).

The only difficulty I had was where to store it on OS X so it was accessible everywhere. Here's how I "installed" it and hopefully this will be useful for others:

cd ~/Downloads
#Note: version/filename of file will change for each release
unzip sqlcl-4.1.0.15.067.0446-no-jre.zip

#Assuming you already have an /oracle directory (I had one for the instant client)
cp -r sqlcl /oracle

#Give sqlcl execute permission
cd /oracle/sqlcl/bin
chmod 755 sql

#rename to sqlcl so no confusion (optional)
mv sql sqlcl


#Add directory to path so can run anywhere in the command line:

#Temporary access:
PATH=$PATH:/oracle/sqlcl/bin

#Permanent access:
#Note: See Barry's comment below about storing in /etc/paths.d on Mac
vi ~/.bash_profile
#Add just above the export PATH line
PATH=$PATH:/oracle/sqlcl/bin

In the above example I installed SQLcl in the /oracle directory. You could also put it anywhere you want such as /usr/local/oracle etc. Just make sure that you reference the location in the PATH environment variable.

If you're looking for more info on SQLcl as well as some excellent examples check out Kris Rice's blog and Jeff Smith's blog.

Update: The following article covers how to create login scripts for SQLcl.