Quick Tip: Oracle Sqlplus - changing prompt

Oracle SQL*Plus has several settings that can be customized using SET command. When you run SQL*Plus, typically the following (boring?) prompt greets you.

SQL>

If you ever want to change this to something more interesting, there is a SET option for that.

SET sqlprompt 'sqlplus:&_user@&_connect_identifier > '

This will be shown as,

sqlplus:<user>@<SID>>

For e.g.,

sqlplus:scott@hr>

Where scott is a user id logged into hr database.

References

http://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12040.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12017.htm#i2675128

Java Stored Procedures

I am sure you are familiar with Stored Procedures. Almost every major database vendor supports it. They are program units that are precompiled and stored inside the database. Since the program is inside the database, it is tightly coupled with the database SQL language constructs. This and the precompiled nature of these programs, make the database access faster. So, even if you are writing external programs that access the database, you will benefit from coding and combining the database operations in stored procedures. This saves a lot of back and forth and thus network traffic. Since version 7, Oracle has support for Stored Procedures. These programs are coded a in native language called PL/SQL.

Since Oracle 8i, Oracle supports coding and running similar procedures (program units) written in Java. Like PL/SQL procedures, these programs are precompiled and stored inside the database and are known as Java Stored Procedures. The JAVASPs(1) are stored as classes (in blob fields) and when invoked they are run inside a JVM that runs within Oracle database. Prior to Oracle 10g, they needed to be wrapped inside a PL/SQL procedure or package. Since Oracle 10g, you can actually invoke Java classes directly from SQL (just the same way you call a PL/SQL function in a SQL).

JavaSPs are different from regular java programs in that they actually run inside a VM within the database. There are a few restrictions while coding a JAVASP.

Building and Deploying Java SP

To write and build the Java stored procedures, you can use your standard Java development environment. I use eclipse to develop and Ant to build it

build_LATAXSP.xml has steps to compile java classes and build jar file.

Deploying Java SP

At this point the jar file is ready to be deployed to Oracle. Typically we pass this step onto the DBA who will then load the jar into Oracle Database instance. But during development, developer can load these themselves using the loadjava utility. This is typically available on the machine where the database is running. (Remember, it is run by DBAs?). In our case we have the Oracle databases running on Unix boxes, so we have loadjava utility available there. I upload the file to Unix and run loadjava. While uploading make sure it’s in binary mode.

Below screenshot shows a sample run of loadjava command on Unix.

In this example, I loaded all the classes in a jar file, to the database. As shown there were 24 classes and 2 resources loaded and there were no errors. If the command failed to load the Java classes, you will see an error message here. The first time around, all the classes files are loaded. Next successive load of the same jar file, will load only classes that have been modified since the last load.

Verifying the load

To view the objects in Oracle, following SQLs can be used:

select * from all_objects where object_type = 'JAVA CLASS' and owner = <owner>;

or

select * from user_objects where object_type = 'JAVA CLASS';

To see a little more detail about the Java stored procs, use

SELECT * FROM user_java_classes; -- this lists java procs for the user.

Earlier I posted about displaying contents of the resources files (text files) loaded above.

Notes:
(1) Oracle actually refers to Java Stored Procedure as JSP. To avoid confusion with Java Server Pages, I prefer JavaSP.

[gallery include="5064,5065"]

SQLite Database

I've been working with different types of Databases over the years from dBase to B-Trieve to Oracle enterprise database. Along the way, I looked at or picked up quite a few - Integra SQL, Informix, MS Access, MySQL, SQL Server(s), Watcom SQL/Sybase SQL Anywhere, DB2, DB2/400 etc. While SQL databases are good, I miss the simplicity of dBase or B-Trieve files. After many years now, I think I found the lost love now!! SQLite!

In my intro I talk about "beautiful software". This is how I felt about this software. Installation is simple, you just download and unzip 1 EXE - sqlite3.exe. (or install in Linux using apt-get or yum). Done! When you are ready to create a database, you type sqlite3 <database name>. That's it!!! You want to work with the same database again, just type,

sqlite3 <database name>

The file size is what amazes me - it's a mere 50k. They've really packed a lot inside it. For one, it supports almost full (basic) SQL. I was able to actually run SQLs meant for Oracle in Sqlite. The command line utility achieves what other big databases didn't. SQL*Plus can learn a lot from this tool. And it's highly portable - in fact, I created a database in windows at work and brought home to Linux. It works like a charm.

For one, they differentiated the tool commands by .prefixing with period (.). So, you want help, just type .help. You want to see the tables in the database you just opened, just type .tables.

What impressed me with the tool, other than the simplicity is, that it provides table export imports naturally. Commands?

.import <filename> <table>

What about exporting table or query output? Just use,

.dump [<tablename>]

.output <filename>

Don't you want to export to CSV or HTML etc.? Sure, just use .mode. Then, you want a different separator? Use .separator. (Though HTML mode only dumps the tags for table rows, as it's output is often embedded in CGI outputs, but you can always add the missing tags, right?). Have you tried exporting a table from Oracle SQL*Plus? It's like extracting teeth, so we end up writing programs/procedures to do it.

True, Oracle (and other databases) is much larger and has a lot more bells and whistles, but for a simple (personal) database task, SQLite is the natural choice. Granted, this is just file based database, but smaller tasks this is a great tool to have in your toolbox. My specific task this time, is to import the log files into a database table, so I can query and procedure usage reports and charts for our recently re-factored web app. In the past, I used MS Access for this type of activity, but with a slow machine I have, Access doesn't fair well with hundreds of thousands of log records.

I've only scratched the surface with SQLite. It seems to be used in embedded applications. There is an API available to work with it C/CPP. Python (Perl, PHP etc) come with native support for SQLite. It's a pleasure to work with SQLite in Python. More on this later. Apart from these, SQLite offers extensibility by allowing to load (.load) external modules. You can also use Virtual Tables (like Oracle External table) to access a CSV file directly.

I can go on and on, but I will stop ranting and raving about the greatness of this software. If this post made you want to look up more on SQLite, please try the links below.

• http://www.sqlite.org


• http://en.wikipedia.org/wiki/SQLite


• http://www.sqlite.org/whentouse.html


• http://www.sqlite.org/selfcontained.html


• http://www.askyb.com/sqlite/learn-sqlite-in-1-hour/


• http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles


• http://code.btbytes.com/2010/09/16/sqlite-csv-loading-using-virtual-table-extension.html


• http://docs.python.org/2/library/sqlite3.html


• http://www.sqlite.org/vtab.html


• http://www.drdobbs.com/article/print?articleId=202802959&siteSectionName=database


• http://www.yolinux.com/TUTORIALS/SQLite.html


• http://robert.accettura.com/blog/2008/02/26/and-the-winner-for-most-embedded-db-is-sqlite/