Wednesday, June 19, 2013

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.


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,


For e.g.,


Where scott is a user id logged into hr database.


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>;

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.

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

[gallery include="5064,5065"]

Tuesday, June 18, 2013

Gotcha: Accessing Oracle 11g with older SQL*Plus

Oracle 11g has several enhancements over the previous versions. Some of these changes are in the client tools like SQL*Plus. To be able to use these you actually need to upgrade the client tools as well. If you are using old clients like me (I use Oracle 10g SQL*Plus), then you won't see those changes in Oracle 11g. For e.g., the EXITCOMMIT option I mentioned in my earlier post cannot be used in my scripts!

(I couldn't upgrade my client because we use Powerbuilder 10.2 with EA Server 5.5 which doesn't support Oracle 11g drivers. So, until we upgrade those tools, we are stuck with 10g client!)

Quick Tip: Oracle - Commit on Exit

Oracle by default does not automatically commit a transaction (Autocommit is typically off). Transaction Management (Commit or Rollback) is left to the client as this askTom post explains it nicely. The tool you use may provide this option for you. For e.g., SQL*Plus commits any open transaction upon exit. So, even if you don't have COMMIT at the end of a script, when SQL*Plus will issue a COMMIT when the SQL is exited.

This was the default behavior for a long time and was generally accepted. Some developers wanted to have more control over it, and according to this stackoverflow post there was even a bug (633247) opened for it in 1998! Recently, in Oracle 11g this has finally been changed. Now, as of Oracle 11g R2, the user actually has an option (EXITCOMMIT) to tell SQL*Plus whether to COMMIT or ROLLBACK upon EXIT. Following statement makes SQL*Plus roll back a transaction upon exit.
set exitcommit off

See this Oracle Post for a good example.


  • One poster in the Stackoverflow post above, actually mentions about Autocommit option in SQL*Plus. I just want to clarify:

SQL*Plus does have an option to set Autocommit on or off. This is actually meant for every statement that you issue, not just the last statement before exit. Prior to Oracle 11gR2, this did not have an impact on the Commit on Exit!

  • Also, this change is actually in client tool (SQL*Plus) not in the database. So, if you are using an older client (like I am), you will be disappointed not to find this option!


Thursday, June 13, 2013

Gotcha: PB Deployment - SYSTEM var not supported error

When you work on code deployed to n-tier, you have to be little more careful while defining the interface for the objects. An n-tier application typically runs in an heterogeneous environment, meaning objects in different languages/platforms may talk to each other through a common interface. If you are not careful, your object may end up being passed to the wrong tier that doesn't know how to interpret it. You have to make sure the data types of the interface functions exist on all sides. Otherwise, you will get unexpected errors at compile or run time.

This is true for PB also, if you are deploying your code to EA Server. Mixing system objects and types in the object's interface will cause errors while deploying to EA Server as shown below.

[caption id="attachment_5001" align="aligncenter" width="536"]1. CORBA IDL Error on PB System objects 1. CORBA IDL Error on PB System objects[/caption]

While deploying this project (p_d_adm_roles_lh) to EA Server, PB throws the following error message:

---------- Deploy: Deploy of p_d_its_adm_roles_lh
Doing Incremental Rebuild...
Generating IDL for Selected Components...
Generation Messages:
Deployment Warning: SYSTEM Variables Not Supported. The following bypassed for component 'im_adm_roles_impl': n_txn
Deployment Warning: SYSTEM Variables Not Supported. The following bypassed for component 'im_adm_roles_impl': n_txn
---------- Finished Deploy of p_d_its_adm_roles_lh

Here is what Sybase document says:

Deployment error or warning (from PowerBuilder): SYSTEM variables not supported
Public instance variables and arguments to public functions can be any of:      Standard datatypes, Structures, Custom class user objects that have been deployed as EAServer components, ResultSets.

If you are using system datatypes (transaction, data store, and so on) as instance variables, declare them as protected or private. If you are using system datatypes as function arguments, declare the function as protected or private.

The real problem was in the CORBA interface. I declared a public instance variable called n_txn of type Transaction which is known to PB but not to CORBA IDL  (interface definition language). So, this cannot be in the public interface of the object. CORBA IDL only allows types that can be mapped to it's own. User defined objects are allowed, as long as they themselves are composed of simple types that IDL allows. More on these in a separate post.

While deploying a component to EA Server, PB generates IDL and related proxy and stubs/skeletons for the CORBA interface. It checks to make sure generated IDL complies with the rules of IDL definitions. In the above example since PB's Transaction object cannot be mapped to any IDL type, PB prevents deployment and throws the error. Thus avoiding surprises at runtime.

Ideal solution is to avoid non-standard types in the instance/shared variables in objects to be deployed to n-tier. But, if you really have to define instance/shared variables of types that are not translatable to IDL, then make them private or protected. IDL interface is generated only for the public attributes and methods.


Wednesday, June 12, 2013

Oracle: Commit and Forward Slash

Further to my post earlier about DDL and Forward Slash, I would like to explain Forward slash's role (or lack thereof) in other scenarios. One such scenario people seem to get confused with is COMMIT. They seem to want to find some type of connection between the two. Let me say it upfront, there is no connection between COMMIT and "/".

Forward Slash is simply a SQL Executor and it happens to show up in various situations. This combined with SQL*Plus's own quirks seem to imply certain other functions for this character. Let me repeat: it's just an executor - short for RUN command in SQL*Plus.

One reason it could be construed as connected with COMMIT is that it may often be the last character in a file. When you run SQL*Plus in batch mode (running a script in a file), it typically exits after the last line (on *nix. It doesn't DoS/Windows). And the SQL*Plus's own behavior is to COMMIT a transaction when exiting. (Why? See here for a nice discussion about this.) So, it's the exit that committed not the "/". But. you can see why some newcomer to Oracle who just inherited some scripts, may think that "/" (being the last statement) actually committed the transaction!!!

The other reason could be DDL. DDLs are implicitly committed. (Actually it commits before and after the DDL statement itself, so beware if you are mixing DMLs and DDLs).

Now, typically a DDL could be ended with "/". For e.g.,
INSERT INTO department(100, 'HR');
CREATE TABLE employee(
emp_id NUMBER,
emp_name VARCHAR2(40)

In the above case, department  table will have the entry, even if the CREATE TABLE below failed. In generla, any DML (like INSERT/UPDATE/DELETE) before a DDL, DML would have been committed, even if the DDL itself failed. Since "/" is the last statement here, one could think, "/" did it!!! Another strike against "/".

So, trust me folks! "/" doesn't do anything else, except sending a SQL to Oracle in SQL*Plus. It's just a shortcut for Run Command. But, depending on the situation, it may look like it's doing something more. In such cases, analyze the SQL and try to rearrange them. Try to keep DMLs separate from DDLs to avoid surprises. And of course, set some standards for punctuations and stick to it to avoid any undesired effects.

Oracle: DDL and Forward Slash

In this blog,  I write about various technologies I come across. Only a few topics seem to get a lot of attention. One of them happens to be Forward slash in (Oracle) SQL. I've already posted about this here and here. When I look at the search terms that lead to my site, I sense a lack of understanding of it's usage. A search through the web quickly reveals that forward slash has been a source of confusion even among some of the experienced Oracle SQL developers 1. Based on the search terms, I can see the newcomers are often getting confused by the SQLs left for them by their predecessors and are looking for a clear explanation. "/" is often mixed in various statements, such as DDL, COMMIT etc and this leads to confusion about the statement itself. I will try to explain the role of "/" (or lack thereof) in each different scenario in separate posts.

One of the search term that I get a lot is "DDL and Forward slash" or some variation of it. Forward slash has nothing to do with DDL per se. If you saw my other posts, it is just a statement executor (RUN command for both SQL and PL/SQL). Unfortunately, semi-colon doubles as statement terminator and executor for SQLs (poor choice of shortcut) in SQL*Plus. There lies the confusion. Thus, the CREATE TABLE or DROP VIEW statements can be terminated by a semi-colon as well as "/".

For e.g.,
CREATE TABLE employee(
emp_id NUMBER,
emp_name VARCHAR2(40));

CREATE TABLE employee(
emp_id NUMBER,
emp_name VARCHAR2(40))

are identical as for SQL*Plus is concerned. But, as a matter of preference, people tend to use "/" in DDL.

But this is not the case in other DDL statements that involve PL/SQL:
CREATE OR REPLACE FUNCTION f_get_employee(a_emp_id NUMBER)
v_emp_name VARCHAR2(40);
SELECT emp_name
INTO v_emp_name
FROM employee
WHERE emp_id = a_emp_id;

Here "/" is a must, as the above statement is one PL/SQL statement (from CREATE to END;). Remember, PL/SQL block ends with END; and it has to be followed by "/" to be executed 2 (see notes below).  Thus some DDL statements (those that create programming constructs like packages, procedures, triggers) require "/" at the end. This, I think, led the newcomers to believe there is a link between "/" and DDL!!! There is no connection! You cannot generalize the use of "/" for all DDLs. For e.g., if you tried to do this (semi-colon followed by "/") in a CREATE TABLE statement, you will have unexpected behavior:
2 emp_id NUMBER,
3 emp_name VARCHAR2(40));
Table created.SQL> /
CREATE TABLE employee(
ERROR at line 1:
ORA-00955: name is already used by an existing objectSQL>

In this case, the semi-colon followed by slash is a bad idea.

In a nutshell, use only semi-colon or slash at the end of a SQL (DML or DDL) and semi-colon followed by slash at the end of a PL/SQL. See here for the difference between SQL and PL/SQL).

As a personal choice, I normally use "/" for all DDLs (SQL or PL/SQL) and semi-colon for all DMLs. You can have one DDL per file in which case, the "/" is the last character in the file. You can also have multiple DDLs in a file, each separated by "/".  Try not to mix DDLs and DMLs in the same file! This may lead to more confusion and errors!



1. See here for e.g.,

2. When I say "executed", I mean the PL/SQL block that creates the function/procedure is executed - meaning the program is compiled and stored in the database. (Hence the name stored programs.) The actual program execution is done by
SELECT <function name> FROM dual;

OR EXEC <procedure name>

Wednesday, June 5, 2013

Source Code Control


I always think, we programmers are like writers and artists. We create software that could be powerful and beautiful. There is no lack of abstraction in our work either!writer

There is one difference, though. When an Author writes a book, she could revise her work so many times at her desk, but only the final outcome becomes public. Not so with our work! A programmer comes up with several revisions of his/her program before it goes to production. Even so, there may be a bug or something was missed that he/she has to revise it again.

A software could also be "backed out" to a prior version. (Even software giant like Microsoft does this once in a while. Remember when people rejected Windows Vista, their solution was to go back to XP, the prior version of Windows! I myself went back to Gnome desktop on my Ubuntu 12.04 desktop, because of some issues with Unity desktop). And of course, someone might point out that something worked in an earlier version, that you will have to dig up the sources for that version, find out what changed and try to re-incorporate that code back into the new version!! I can't imagine an artist having to go through this type of revising of his/her painting! (I agree a book may have editions, but it's always move forward for them.)

So, if you are a software developer you need to worry about keeping track of the history of the program. Not just the history, but all the files that made up a particular version! And to do this we resort to so many ingenious(?) ways. How many times, have you seen files with .bak, .sav, .org etc extensions on a developer's machines? (guilty!). Some may even organize their files in dated directories, so they can go back to a specific date (guilty as well). But when your program is made up of so many files (gone are the days when you wrote all your code in one file!), such rudimentary methods of keeping track of files won't work! Even more so, if you are working (collaborating) with other developers.

Source Code Control or Version Control

Wouldn't it be better if a Software can track these information at the source level, so it's easy to pull up any version of the software sources and rebuild it? Such a software tool exists for over 40+ years and is known variously as Source Code Control Software (SCCS), Version Control System (VCS), Revision Control System (RCS) and even Software Control Management (SCM). Dozens of tools have been developed with these acronyms in their names. Some of the most popular ones over the years are SCCS, RCS, PVCS, Perforce, Visual Source Safe (VSS), Mercury etc. And in the open source arena, we have had CVS, SVN and Git. Please refer to the wiki site for comparison of many more tools available on the market. The difference is not only in the names, but in techniques and technology involved. Newer tools support concurrency better.

The least a VCS can do is to keep track of history of files and thus offers reversibility. With various versions saved in the repository, it is only natural that we require the VCS to provide a good compare (diff) utility or at least the capability of using external diff tools.  Any type of files can be saved in the repository including binary files. Though, the diff utility is typically geared for text files.

More traditional VCS offers a centralized repository. SCCS, PVCS etc belong to this category. More modern ones offer more distributed approach. With developers from around the world working on open source projects, distributed repositories more sense. And the other characteristics that go with the territory is the concurrency. Again, modern VCS have more and more concurrent support built in. For e.g., CVS refers to Concurrent Version System.

Further Reading

Please refer to the Eric Sink's website (his book is available in PDF format on his site) for detailed discussion on the SCCS tools. This blogger recaps the history of Source Code control in a funny way! And the wiki page like always delivers a nice introduction to this topic. Eric Raymond's paper explains various generations of Version Control Systems nicely.

Oracle: Forward Slash in SQL*Plus - Take 2

Take 2?
I posted about Forward slash in Oracle SQL before. I even went back and updated it a few times based on the traffic. Believe it or not, I still get a lot of traffic to this blog site solely based on this one little topic!! So, I thought I would spend a little more time on this.

Wordpress has a nice feature in the Stats page that shows the Search engine terms that drove the user to my page. This is what some enters in say, Google and clicks Search. Here are some sample terms on Slash:

sqlplus forward slash
sql forward slash
forward slash in sql
oracle forward slash
oracle sql forward slash
slash in sql
sql slash
commit vs forward slash
oracle pl/sql semi-colon or forward slash
oracle slash before commit
oracle sql commit slash
forward slash in ddl file

and so many variations of these. One thing that caught my attention (in bold) was the confusion user(s) had with commit and Forward slash. They really don't have anything in common, but I guess Oracle behavior in different scenarios could lead one to that. So, I thought of explaining these one more time.

Then Vendor said: Let there be a Database and a (client) Tool

Let's start from the beginning. A database system is typically run on a remote server. To work with it, the vendors - like Oracle, Sybase etc- provide a client tool. Oracle has always come with SQL*Plus as a client tool. This is a simple command line tool, yet powerful. People use it for both interactive and batch use. Oracle also came up with "better" tools eventually, like iSQL*Plus, SQL Developer etc. Similarly, Sybase has Interactive SQL. Apart from these tools, there are plenty of tools available from outside vendors. Some prominent ones for Oracle are Toad, SQLTools, PLSQL Developer etc. The modern tools are typically GUI based and provide plenty of options for interactive usage. Irrespective of that, SQL*Plus is still going strong. Several DBAs prefer this tool to flashy new GUI tools, so whichever tool you use, you may want to make sure your SQL will run SQL*Plus.

Why those Punctuation?
In a GUI tool, you can type up a SQL and click on a button (or press a key), the tool will get the SQL executed (send it to Oracle server, get the results etc). If there are multiple SQLs, you just highlight one and click and go. Now, SQL*Plus is a command line tool. It's text based and often used in both interactive and batch modes. You couldn't really click a button or press a key to execute SQLs. Especially, if it's running SQLs from a file. Then you need a marker to show the end of each SQL, right? That, my friend, is the semi-colon - the statement terminator. Sort of like the period at the end of an English statement. Semi-colon is mentioned as the statement terminator in the SQL Standards books also.

Unfortunately, SQL Standards came much later. Database vendors already came up with their way of telling their tool to execute the SQLs. For e.g., client tools for Sybase and Microsoft SQL Server use go command. That makes sense. Type in a SQL. Key in go and it goes! (SQL is sent to server to be executed etc). Oracle (SQL*Plus) had a similar command - RUN!! So, if you are running SQLs in a file, you would have a SQL followed by RUN and then the next SQL followed by RUN and so on. This is good. But then Oracle decided to give us the short cuts. Like Forward Slash ("/") for RUN1. (Those early days, every byte counted. Why type 3 chars for RUN and why store so many of them in a file? Slash will do it for cheaper price!!).

1. Actually RUN = Load & Run and the shortcut for it is "R" while "/" is to just "Run" what's already loaded

What does Forward Slash mean?

So, there you have it. Forward Slash executes a SQL before it. If it was left like that, things probably would have been simpler. Oracle also decided to support the statement terminator, semi-colon. (Sybase and other databases did not require this earlier). Now, you have a semi-colon that could get the SQL executed and a Slash that could do the same thing. They are not the same, but served the same purpose of indicating end of a SQL statement in SQL*Plus!! This started the trouble, especially when people started mixing them. Imagine what happens when you have a semi-colon and a forward slash?

Then came PL/SQL. Now, PL/SQL is a programming language in Oracle which can have several embedded SQLs, often separated by you guessed it - semi-colon - the statement terminators. Now you can have a bunch of PL/SQL block in a file, how do you distinguish each block? Don't you need something like a PL/SQL statement terminator? Well, semi-colon is already taken. So, Oracle came up with the ingenious way of reusing a character that already had a purpose - you guessed it, the Forward Slash!!!!! It was meant to be "RUN" the SQL, right? Now, it also has the purpose to terminate PL/SQL. (It could also mean RUN the PL/SQL). That paved the way to all the troubles we face in mixing these up.

Semi-colons and Forward Slashes

That mixture combined with SQL*Plus's own quirks made it worse. In SQL*Plus, you can type a (SQL like) text and end it with semi-colon, it will pass it on to the database. You type a SQL followed by slash ("/"), it will be sent to the server as well. What happens when you have a semi-colon and slash? It gets executed twice. What happens when you have 2 semicolons? You might get an error on the second semi-colon. This is because of the way SQL*Plus works. As soon as you run a SQL, SQL*Plus stores it away in a buffer. Next time you type "/", it re-executes what's already stored in the buffer. Type "/" again, it runs it again. Until you type up a new SQL. You can smell trouble, right? People run into trouble because they don't understand the nuances or simply because they mistyped.

Remember, "/" is a command. So, it has to be on a line by itself. Semi-colon on the other hand, has to be the last character on a line. If there is anything else after that, even a comment (--) or another semi-colon will cause an error!!

DML, DDL etc
When I mentioned SQL above, I meant any type of SQL. SELECT, INSERT, UPDATE, DELETE, CREATE TABLE etc. First 4 deal with data in tables. SELECT is a query. INSERT, UPDATE, DELETE make up the DML. There are whole bunch of SQL statements like CREATE TABLE that actually define the structure of a database object (here TABLE). These are called Database Definition Language (DDL). What we saw above applies to both DMLs and DDLs. You can use semi-colon to end DML statements as well as DDL statements. You can use Forward Slash with DML and DDL statements.

To confuse you a bit, general convention in Oracle world is to use Forward Slash to end DDL statements. This would make sense if you look at the syntax for creating Stored Procedures, Functions etc. These are PL/SQL objects. Remember, I said PL/SQL needs to be ended in Forward Slash? Now that we decided to (have to) use Forward Slash for DDL for Stored programs, why not use it for all DDLs? So, there you have it. This is how we arrived at Semi-colon (";") to end DML statements and Forward Slash ("/") for DDLs.

How did COMMIT enter this discussion about Forward Slash?

Phew! We got through that fine. Now, where does commit come into the picture and why confusion about it? Typically, Oracle has Auto-commit option turned off by default. You will have to issue an explicit COMMIT to commit a transaction. (This helps us to have bunch of related DMLs inside a transaction).

Here again there are some short cuts and quirks in the tool that made it more confusing. If you didn't add an explicit COMMIT and simply exit SQL*Plus, the tool will issue the COMMIT for you! And the DDLs do not require explicit COMMIT, as a DDL always issues implicit commits (See here) before and after the actual DDL statement. So, then with DDL it's automatically committed and DDLs typically end with Forward Slash ("/"). Now, I can imagine why the user searched for a connection between Commit and Slash. He/She is probably running a script that has "/" at the end. The user probably ran the script and when SQL*Plus exited, it probably committed. Are you seeing the connection now? Really, there is no connection between "/" and COMMIT. They just look to be related because of quirks and twists in the tool and the specs.

What was all the blabbering about?

In summary, Forward slash is like go in SQL Server, but use with caution, as it's not only the RUN command, but could be indirectly construed as a statement terminator as well. Forward Slash ("/") does not commit. SQL*Plus may silently COMMIT your transaction when you exit. But, always add COMMIT and ROLLBACK explicitly.

And Remember, all this is specifically applicable to SQL*Plus. But, since it has become a de facto standard for running SQLs from a file, you make sure to follow these standards to avoid surprises.

And that mumbo-jumbo applies only to Oracle SQL or PL/SQL. Forward slash doesn't have this type of special meaning in other databases!!!!