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>