Thursday, May 29, 2008

Select INTO!

When coding in PL/SQL, remember that if you want to add a query's returned value into a variable, you must use the format:

SELECT value INTO variable FROM table
/*The rest of the query goes here*/

I made the mistake of doing it this way:

variable = SELECT value FROM table ...

Don't make the same mistake I made!

Query to display duplicate values

This query will display how many times a certain value occurs in a table.

SELECT a, COUNT(a) AS numoccurences FROM table WHERE a IS NOT NULL
GROUP BY a;

It's useful to create a view with this query and then use the values in other queries.

Oracle Database Schema Viewer

Schemester is a sweet little tool that allows Oracle database schema discovery in case you don't have the database diagram handy (which is usually the case). After connecting to the database through Schemester, you'll have a list of all the tables in the database, and you're able to drag a table into the "page" and you'll have a diagramatic representation of this table, displaying all its columns and appropriately identifying keys. Awesome!

http://www.download.com/Schemester/3000-2210_4-10225039.html

Wednesday, May 28, 2008

PL/SQL on Oracle 9i

I came across a problem or two at work in which I was forced to use PL/SQL commands to solve it. I researched a bit and found some cool information that'll help those of us that are a bit rusty on Oracle and need a refresher on PL/SQL.

Here's a pretty good tutorial for creating and using PL/SQL cursors.

http://www.exforsys.com/tutorials/oracle-9i/oracle-cursors.html

A good explanation of several PL/SQL commands:

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-plsql.html#variables%20and%20types

The PL/SQL FAQ

http://www.orafaq.com/wiki/PL/SQL_FAQ

Thursday, May 22, 2008

Self-modifying code

You can do awesome things by using Java's Reflection APIs and combining it with the Javac and JVM utility classes. You can make objects of the Java compiler and compile and run code AT RUNTIME. I'll update this later.