Oracle SQL Tips

I often am in the middle of some off-the-cuff query and run into one of those technical "gotchas" like a failed compare between two text strings due to trailing spaces or that alter table syntax to modify a column name.

Instead of googling for the answer each time, I've consolidated the websites that I use to find the answers to these common SQL questions and have listed these below for my own and others reference.

Oracle FAQ
Oracle Cheat Sheet
Oracle User Guide

These are the most frequently used resources. I may add to this later but the above will solve most all of your Oracle SQL questions.

Formatting Query Results

I wanted to format a query result so it displayed as currency. To do this you need to use the TO_CHAR function with the following syntax:

SELECT TO_CHAR(A_NUMBER, 'L999,999,999.99')
FROM TABLE;

This gives you a formatted output in the local currency format.

How to Use Variables in PL/SQL or SQLPlus Scripts

I am researching how to set up my SQLPlus script to be able to output a text report using a generic script that reads in a variable.

I want to set variable on the command line and then have the script reference this throughout, giving me customized counts based on the variable entered. The results should output to a text file that can then be used as the actual report. I am familiar with how to do this in a PL/SQL script but not in a regular SQL script that I would use in SQL Plus.

The following link helped me with the setting up of my variable.

Detail on how to use variables in SQLPlus

Specifically here were the instructions:

9.1 Setting a Substitution Variable's Value

A substitution variable can be set in several ways. The common ways are given below.

1. The DEFINE command sets an explicit value:

define myv = 'King'

This creates a character variable "myv" set to the value "King".

2. The ACCEPT command:

accept myv char prompt 'Enter a last name: '

prompts you for a value and creates a character variable "myv" set to the text you enter.

3. Using "&&" before an undefined variable prompts you for a value and uses that value in the statement:

select first_name from employees where last_name = '&&myuser';

If the substitution variable "myuser" is not already defined then this statement creates "myuser" and sets it to the value you enter.

4. Using COLUMN NEW_VALUE to set a substitution variable to a value stored in the database:

column last_name new_value mynv
select last_name from employees where employee_id = 100;

This creates a substitution variable "mynv" set to the value in the "last_name" column.

When outputting to a file, I like to use include the date in the file name:

Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;

spool &mydate.report.txt
-- my report goes here
select last_name from employees;
spool off

Be sure to use the double period after the variable name in your file name:

If SET CONCAT is a period (.) and you want to append a period immediately after a substitution variable then use two periods together. For example:

define mycity = Melbourne
spool &mycity..log

is the same as:

spool Melbourne.log

Use the variable that you set like this:

define dept = '60'
ttitle left 'Salaries for department &dept'
select last_name, salary from employees where department_id = &dept;

Or pass in the variable on the command line like this:

You can pass parameters on the command line to a SQL*Plus script:

sqlplus hr/my_password @myscript.html employees "De Haan"

They can be referenced in the script using "&1" and "&2". For example, myscript.sql could be:

set verify off
select employee_id from &1 where last_name = '&2';

That handled my main concern and gives me a nice text output file that uses my generic script with a variable.

The next point I sought to resolve was how to have my text output be in a delimited format as I plan to read this from an Excel file.

Here is what I found to do this:

--sets sqlplus so output will be tab-delimited
set colsep ' ' /* that's a tab */
SET LINESIZE 3000
SET PAGESIZE 50000
SET ECHO OFF
SET FEEDBACK OFF
--SET HEADING OFF
set trimspool on
set newpage none
set underline off

Here is someone else's point of view on how this should be done.
Digital Voice

Instead of either of the above a simple handling would be to hard code into the generic script "|" between the columns. This would give me a standard pipe-delimited output.

That sounds like the easiest method and is what I commonly use.

Scientology Public Service Announcements

There are a number of public service announcements that have recently been shown on TV stations around the world. These PSAs are about Human Rights and the basic rights that all men have as based on the International Declaration of Human Rights first put forth over 50 years ago. Watch these and see what your human rights are.

Area Code Maps

I have recently been looking into how to track phone numbers and the constantly changing area codes. A great resource was found called NANPA. Still not sure what this stands for but these guys are the ones who set the phone numbers, so I would say it is a reliable source. This is the best area code map site I have seen as well.

Area Code Map

Free Java Books

Here is a link to a massive resource of free on-line Java books.

Free On-Line Java Books

Eclipse Plugins

I am diving head first into the world of Eclipse plugins. The first breakthrough was to clear up the concept of what a Plug-in really was. I went through the cheat sheets in Eclipse to get somewhat familiar with them and then realized this is exactly what I need.

The Eclipse Plugin headquarters can be found here.