Sunday, 10 February 2013

Oracle development environment


Intro
Below I'm describing a way to set up a developing environment for using an Amazon RDS instance of Oracle.

Having an Oracle instance up and running on RDS and have gotten SQL Developer connected (read more here) the next step was to set up a local environment for development. You can do everything described below in SQL Developer, however I prefer not to.

In brief
Using a database modelling tool I generate an SQL file for (re)creating the entire database (like "crebas.sql"). As I in a development phase constantly are doing changes to the database this file is updated and executed frequently. Next to this I like to write own PL/SQL code using Komodo Edit. These files are as well executed at all times. To execute I prefer using SQL*Plus from the command line. To avoid bloated software packages I'm using SQL*Plus with Oracle's Instant Client.

Oracle Instant Client and SQL*Plus
Download Oracle Instant Client (I use the Basic package) along with the SQL*Plus package. Unzip all these files into the same folder somewhere appropriate on your computer. Note that no installation program is needed. Unzipping is all that has to be done.

Oracle Instant Client.

Having unzipped these files you need to add the path to this folder to the PATH environment on your computer. This so you can start SQL*Plus from anywhere using the command prompt (cmd).

Control Panel.

Go to your control panel, search for "environment" and click the "Edit the system environment variables" link. Then click the "Environment Variables" button in the new window that shows. Locate "Path" under "System variables", click "Edit" and append the path to your Instant Client folder to the already existing string.

Adding the path to your Instant Client folder.

Having done this you should be able to execute the sqlplus.exe directly from the command prompt, no matter what folder you're starting it from.

Starting SQL*Plus from the command line / command prompt.

Starting SQL*Plus from a customised command prompt.

login.bat and login.sql
For each Oracle database related project I have a folder containing all PL/SQL code. In this folder I create a .bat file used for logging into the database. Not that you do NOT need a "tnsnames.ora" file for connecting to a database. In stead you can simply specify the full connection string from the command prompt:

sqlplus username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mydomain)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=servicename)))

What I do is creating a bat file with this information, to avoid having to type all this every time. Please note that you might not want to store the password in the clear.

Example connecting to a Amazon RDS hosted database from SQL*Plus without a "tnsnames.ora" entry:

sqlplus sleeptracker/thisispassword@//privyinstance.cdhzvkd198ux.us-east-1.rds.amazonaws.com:1521/PRIVYDB

You might also want to create a "login.sql" file in the same directory, for initiating SQL*Plus. By default I use the following two lines in "login.sql":

set hea off
set serveroutput on

Having created those two files development is a lot easier.

PL/SQL directory.

SQL*Plus made easy.

Komodo Edit execution
Execution of SQL statements directly from Komodo Edit might also come in handy. To do this all you now need is to create a command (note the extra @"%F" that is needed for executing current file):

sqlplus sleeptracker/thisispassword@//privyinstance.cdhzvkd198ux.us-east-1.rds.amazonaws.com:1521/PRIVYDB  @"%F"

Komodo Edit command.

You might as well have to add the Instant Client directory as a PATH here as well. See the above screenshot.

Execution of SQL directly from within Komodo Edit.

No comments:

Post a Comment

Allowed HTML tags:
<b>bold</b>
<strong>strong</strong>
<i>italics</i>
<em>emphasis</em>
<a href="">hyperlink</a>


Please, show the courtesy of identifying yourself when adding a comment. Anonymous comments will, most likely, be removed.