Setting up OS X El Capitan for node development with node-oracledb

After stumbling through the setup of Oracle (for use with node and node-oracledb) on my new and first ever Macbook, I thought I'd put together a one pager on setup for those who may have had similar struggles. I was able to find everything I needed through existing pages, but I bounced around a lot to several different sites to find all the information and it took me several attempts.  I tried to cobble all the steps together in a fairly verbose way as I had to learn certain things along the way...

Here it is - https://github.com/gilcrest/OracleMacOSXElCapitanSetup4Node

Apex 5 - Minimize Left Sidebar Navigation by Default

So, you may find yourself in a situation where you proudly unveil your upgraded-to-Universal Theme app to your user-base after working on it for months with your beautiful wide screen monitor (page designer pretty much mandates this) and your user-base is not quite as happy as you thought they'd be as they don't all have super wide screens and they, for some reason, have low-resolution monitors (like 1280 x 768 low).  The users may think your super cool left sidebar navigation, which is maximized by default, is neat, but they'd actually prefer their space back...  If you should find yourself in this situation, there is an easy answer!  By default, always have that left sidebar nav minimized (I actually prefer this anyway).  I turned to the Oracle APEX forum recently for the best solution to do this and forum-master, fac586 gave me a simple solution I thought I'd share for others who may need/want to do this.  I'd actually love to see a declarative way of doing this in a future release of APEX (I'll log a feature request), but until then...

From fac586:

The simplest way to do this is with a dynamic action that simulates clicking on the button when the page is loaded with the menu expanded. Create a dynamic action as follows:

Event: Page Load

Condition: JavaScript Expression

Value: $('#t_Button_navControl').attr('aria-expanded') === "true"

True Action

Action: Execute JavaScript Code

Fire On Page Load: No

Code

1. $('#t_Button_navControl').click();

Raiser v0.5 - Committed to Github

I just released an early version of my "raiser" code for Oracle PL/SQL, leveraging OraOpenSource's logger utility.  I'd love to get anyone's input and help with it.  The Github project is https://github.com/gilcrest/raiser

As background, I've used the Quest Error Manager (QEM) in my codebase for years, but have recently switched primarily to using logger for most things. There are a few things that QEM does that logger does not...  In a highly distributed environment, you really need to be able to raise an exception to a user with a particular error message and unique identifier.  If an exception is thrown in one of my applications at 4 in the morning, I need that user to be able to contact support with a unique ID and I need to be able to get a full trace of that error for proper debugging and resolution.

Close to a year ago, I reached out to Martin D'Souza to ask if I could work with him to enhance the logger product to be able to achieve this type of implementation.  Martin, being a fantastic product owner, pushed back saying he didn't want logger to be a "raiser", but was soon to release a new version of logger that would have hooks in it (in the form of a plugin) that I could use to achieve my goals...  Great!  So, he did what he said he would - logger 3.0.0 and above has a really cool plugin facility that I've utilized to make a "raiser" out of logger.

Also, after reading Dan McGhan's excellent post about using the APEX_JSON package for creating JSON content, I got the idea to encapsulate multiple fields into the Oracle exception text using JSON...  I had been struggling to figure out a good way to be able to give end users multiple data points with just the one SQLERRM output, but I think this works out nicely.

I plan to add a lot more in the coming weeks to the code - for instance, an example of using the code within an APEX error handle function to return a unique logger identifier to end users.  Someday, I'd also like to eventually work through some clients (java, javascript, python, etc.).

The first order of business is to fix the current getErrorDetails function to parse the JSON SQLERRM and how to work with it...

My Path to node.js and Oracle Glory - Volume 3, node.js setup on ubuntu on chromebook

Next step - node.js setup on Ubuntu.  This was remarkably easy!  I went to https://nodejs.org/ and viewed their Downloads section, where I then went to "Installing from package managers".  That link takes you to this URL on GitHub.  If you read the first line of "Debian and Ubuntu based Linux distributions", you'll note you should go to nodesource's blog post for the most up to date instructions.  I did what I was told - read what they have to say on the page - helpful stuff!

The instructions to install are as follows:

Open a new crosh window or use ctrl+alt+t
type shell
type sudo startxfce4 to get into Ubuntu
click on the Terminal Emulator icon

You need curl (a tool for making http calls, etc. - http://en.wikipedia.org/wiki/CURL), so get it - type sudo apt-get install curl

The instructions from nodesource's blog post say to then enter the following:

# Note the new setup script name for Node.js v0.12
curl -sL https://deb.nodesource.com/setup_0.12 | sudo bash -

# Then install with:
sudo apt-get install -y nodejs

Type node to start node..

That's it!  node.js is installed in Ubuntu inside my chromebook!

There are a TON of node.js intros, etc. that I've been pouring over for a while now - I'm not going to bother to explain the basics of node as it's extremely well documented.  I will now start to write some small pl/sql and js programs using the node-oracledb driver and will continue to blog as things progress in that area...  That's it for today!

My Path to node.js and Oracle Glory - Volume 2, Linux setup on Chromebook

OK, so now having established my database server in Volume 1 of this series, I now need to move to the next step - setting up my chromebook with Linux in order to facilitate node.js development.  I chose a chromebook as my dev machine of choice as I wanted something that natively runs on Linux, but didn't want to pay as much as a Mac and didn't really like the Linux based machine options out there...  Google is doing a great job with these and after having my chromebook for a little over a week - I have to say I love it...

So, the first step is to put your chromebook into developer mode - this is a fairly frightening experience, but since everything on your chromebook is "in the cloud" and can be restored, fear not and hack on!

I bought a Toshiba Chromebook 2 and found instructions to put the machine into developer mode at this URL and this URL as well...

• To put it your chromebook into developer mode, you simply hit the esc+refresh+power keys at the same time after you've logged in.  When you do this, your machine will reboot and show an alert screen - at this point, hit Ctrl+D to go into Developer mode.  THIS WILL WIPE YOUR MACHINE!  You'll need to re-login and setup the machine again, but once done you won't need to do this again...  I bought this machine to develop, so this was basically the first thing I did with it - the data wipe didn't concern me...  Since most things you do are auto backed-up to Google drive, this probably won't concern most people.  The process for the laptop to transition to Developer Mode takes 15 minutes or so (one time only) - so go grab a beer...

Next up - Crouton!  ...What the hell does a crouton have to do with this?  Well, crouton stands for ChRomium Os Universal chrooT envirONment - and all the information you really need about it is at the GitHub location here.  This is now the point where I'm over my head, but I believe crouton is a set of scripts written by a prolific Google developer named David Schneider that allows you to create what's known as a chroot, which allows Linux distributions to run under a segregated file system as a guest OS -  more info about chroots here

So, basically in order to run Ubuntu (the Linux distribution I've chosen) alongside my ChromeOS, I need to run it within a chroot.  I need to use the crouton script bundle to generate this chroot.

You should go to https://goo.gl/fd3zc - it will download into your Downloads folder - leave it there.

So, in order to properly generate a chroot - you have to have some opinions.  You need to choose a Linux distribution and a desktop environment... 

I need to choose a Linux distribution -- I have no idea where to start even - there are over 600 distributions of Linux and 300 are actively being developed!!  Where do you even begin in this world?  My understanding of a distribution of Linux is that it is a packaging of software (libraries, desktop environment, utilities, GUI, etc.).  I have a lot to learn on this, but for now I'm going to be lame and choose a commercially backed option - Ubuntu.  I am choosing the most recent "Long Term Support" (LTS) version of this distro, code named Trusty Tahr.

Within Ubuntu, there are different flavors based on the desktop environment you prefer.  After some basic research, I want a basic desktop - I actually installed several different versions and thought the Unity desktop was awful...  So, I'm going with Xfce - a basic, yet comfortable desktop environment!  This flavor of Ubuntu is known as Xubuntu...

So, I have my opinions settled - I know what I want - next is to go to the Chrome web store and install a few items

1. Secure Shell
2. Crosh Window - opens a separate window from your browser
3. crouton extension - Allows for copy/paste to/from guest OS window (in my case Ubuntu)

So, now you have all your dependencies - you can either do Ctrl+alt+t to open a "Chrome Operating Shell" or crosh for short in a full screen window or you can use the Crosh Window app you just installed.  

• At the command prompt, type shell
• Next type sudo sh ~/Downloads/crouton -t xiwi,xfce -r trusty
• This is basically telling crouton that the target (-t) is the xfce desktop environment using the xiwi version (which allows for the copy/paste) and the distribution release (-r) should be Trusty Tahr (trusty)
• This takes a while to install - go get another beer...  At the very end of the install, you'll be asked "Please specify a username for the primary user:" - plug in your preferred username...  you'll then be asked for a password as well...

Once done, you'll be taken back to the shell prompt where you just type sudo startxfce4 and bam!  Ubuntu should boot!  It will boot in a full screen Chrome OS window - in order to minimize and size the window, just click at the top where it says "crouton integration" triggered full screen Exit full screen and then resize as necessary.

Each time you log out of Ubuntu, you want to ensure you logout properly - for the distro I chose, I just go to the upper right hand corner where my username is, choose it and select Log Out...  

Now that I've done this once, the next time is a breeze - open a new Crosh Window, type sudo startxfce4 and that's it - I'm up and running in seconds.

I learned a lot about this crouton extension jazz by reading this - the dude Francois Beaufort seems to be one of the chromebook leads and has a lot of great posts...  You probably want to follow him on Google + if you have a chromebook and are a developer.

OK - it worked - I have Linux up and running within a Chrome OS Window!  YAY!

... that's it for today - next up Volume 3, Setting up node.js on Ubuntu in my Chromebook!

My Path to node.js and Oracle Glory - Volume 1, Oracle XE Setup on Windows machine

I am starting a new personal project with the following goals in mind:

1. Learn JavaScript (I'm a beginner, but been reading/implementing for a while)
2. Learn node.js
3. Create a RESTful API using node.js calling a remote database server using Oracle's node driver (node-oracledb)
• API should be simple to understand
• API in the backend should use the HR sample schema
• When using node.js, goal is to form a service request object in javascript and pass that object to a stored procedure in Oracle as an IN parameter, the same stored procedure should return a response object that will be parsed in javascript and the results displayed
4. DISCLAIMER - When I actually have free time to develop - I am primarily a PL/SQL developer -- I love PL/SQL - there I said it!  I work with java and perl, but at the end of the day, I love the insulated, hassle-free world of developing within a database.  I know enough of command line to get by, but my 4th major goal is to become a command line "expert" (or at least know where all the relevant references I need are) for sqlplus and more importantly Linux, so bear with me as I toil through the basics along the way!

So, on to the actual work - I am going to setup two machines on my home network -- one machine (an old Windows 7 laptop) will act as a database server, the other machine (my new Chromebook) will act as the calling application (in this case node.js using Oracle's node-Oracledb driver).

Step 1.  Install Oracle XE on my windows machine using this URL - I won't go into depth on how to install XE as it's well documented.

Step 2. DO NOT DO THIS!  Live and learn - I had no idea that the HR and other sample schemas are already installed with XE and went through all the below steps to clone and install the schema, only to figure out that I had accidentally installed the HR objects in the SYSTEM schema (I've now corrected the steps)....  Anyway, if for some reason, you need to install Oracle's sample schemas in your XE instance using Christopher Jones' db-sample-schemas on Github at this URL, then follow these steps!

• If you're not familiar with Github, it's definitely worth your while to learn about it, though there is a learning curve (I still suck at it, actually, but getting there...)
• Install Github for Windows using this URL
• Once installed, you can just go to https://github.com/oracle/db-sample-schemas.git and choose "Clone in Desktop" and choose the folder location you wish to store the db-sample-schemas repository.  If you're not comfortable with Github yet - you can always get the same files by choosing "Download ZIP"
• After you've cloned or downloaded the files, navigate to the human_resources directory using the Windows command line interface (cmd.exe).
• When you're in the directory, type sqlplus - you'll then be asked to login - be sure to login as the HR schema.  I am assuming you've already created the HR schema.  If you have not, you should - you'll need to figure that out on your own though.  You can look at the hr_main.sql in the human_resources directory that you just downloaded for an example of creating HR.
• After you're logged in as HR in sqlplus, just type @hr_cre.sql - sqlplus will then execute the script and create all the necessary objects.  You can then type @hr_popul.sql to populate the tables you just created with data!
• Anyway, if you screw things up like I did, Christopher has nicely provided a script hr_drop.sql that you can run whenever necessary.

Step 3.  Open up Windows firewall for Port 1521:  

• To enable remote calls from my Chromebook, I need to enable Firewall rules on my Windows machine so that data can flow in and out of the machine through port 1521 (the standard port that Oracle is configured for)
• First, you want to be sure your listener is up and running for the Oracle XE instance you installed (it should be by default), but you can check in the Windows command line interface (cmd.exe) by typing lsnrctl status (you don't have to browse anywhere in particular for this command)
• You can find out some critical items from this status, ie your HOST and PORT (will be 1521 by default) which you'll need when connecting later
• Click here for more information on Managing Network Connections - doc shows you how to stop and start your db listener if you need me to...
• Assuming you are using the default port, next step is to open up connections (inbound and outbound) on it 
• Goto Start -> Control Panel -> System and Security -> Windows Firewall -> Advanced settings
• Once in Advanced Settings, you'll see a window titled "Windows Firewall with Advanced Security"
• click on Inbound Rules and choose the "New Rule..." action
• Choose "Port" when asked What type of rule would you like to create?
• Choose TCP when asked Does this apply to TCP or UDP?
• Add 1521 next to Specified Local Ports
• Choose Allow the connection
• Rule applies to whichever domain you choose...
• Give the rule a name - I chose OracleInboundPort, click Finish
• Rinse and repeat the above for the Outbound Rules, just name the rule appropriately, i.e. OracleOutboundPort

... that's it for today - next up Volume 2, Configuring Linux on my Chromebook

Name:Value Encryption Utility using PL/SQL

I recently ran into an issue where, as part of a webservice call I was making using the Oracle APEX_WEB_SERVICE API, I needed to pass the application id and password in the soap header, but I did not want those sensitive data elements to be exposed in my pl/sql code in cleartext.  I decided I wanted to encrypt the data and retrieve it at run time as a function call.  I tried to find a way to do this via Stack Overflow, Google, etc. but was unable to find a very simple way to do this....

I found many great posts on how to do encryption, but I was looking for a dead easy way to encrypt and persist random pieces of data and be able to retrieve it when necessary just as easily...  I decided to build the solution myself!  I have put a project up on GitHub, that I named Name:Value Encryption or NVE.  You can find the code on Github at https://github.com/gilcrest/nve

This is definitely v1 of the code and I'd love for the community to participate and give me pull requests to improve this!  My first mistake may be calling this Name:Value instead of Key:Value - not sure which is more accepted at this point...