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"]

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 ¹. 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));

and

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)
RETURN VARCHAR2
AS
    v_emp_name VARCHAR2(40);
BEGIN
   SELECT emp_name
   INTO v_emp_name
   FROM employee
   WHERE emp_id = a_emp_id;
END;
/

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:

SQL> CREATE TABLE employee(
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!

References

http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

http://docs.oracle.com/html/A90108_01/sqcmd.htm

Notes:

---

1. See here for e.g., https://forums.oracle.com/thread/1020117

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>

Link: Confusion around PL/SQL to_date and to_char functions

See the TechTarget post below:

Confusion around PL/SQL to_date and to_char functions.

Forward Slash in SQL*Plus

Note to the visitor: Thank you for stopping by. I see a lot of people trying to find out about forward slash in SQL*Plus. I want to clarify: This post only applies to Oracle SQL. If you are you are querying about a "/" usage in any other database, you are in the wrong place.

Forward Slash ("/") in Oracle SQL

This post is about using forward slash ("/") in Oracle SQL and PL/SQL. This gets a lot of developers (new and experienced alike) as it's not always needed and sometimes it shouldn't be used.

Have you ever tried typing "/" (without quotes), as soon as you logged into SQL*Plus? Try it. You will see something like below.

SQL> /
SP2-0103: Nothing in SQL buffer to run.
SQL>

The above snippet explains it all pretty well. Forward slash ("/" ) is a shortcut for RUN command in SQL*Plus (like "go" in SqlServer or mysql). Since there is nothing to run yet, SQL*Plus returned an error message (prefixed with SP2-).

The message in the above example also shows there is a SQL buffer in SQL*Plus. Any SQL sent to DB ends up in this (one statement) buffer. Also, if there is a statement in the buffer, "/" would have simply re-executed it.

When you type a line and press ENTER, SQL*Plus checks if it's one of it's own commands, a SQL statement or just bad text. If it's a SQL (identified by keywords like SELECT etc), it starts storing it in the buffer until it sees a "/" or a semi-colon, at which time it executes it (actually sends it to the server to execute it).

Gotcha with Forward Slash (/)

SQL Statements typically end in Semi-colon. Semi-colon is the Statement Terminator in ANSI standard. But, in Oracle SQL, you can use "/" to run a SQL, instead of semicolon, like shown. (This is how SPANISH record got inserted in my attached example at the bottom of this post).

SQL> SELECT dummy FROM dual
 2 /
Dummy
-----
X

But, here is the gotcha: If the statement itself contained a semi-colon, then "/" would have re-executed it!!!!

SQL> select dummy from dual;
Dummy
-----
X
SQL> /
DUMMY
----------
X

Oops. Imagine this was an INSERT statement instead!!! (And this is what happened in my example script attached. If you notice the "/" before commit, this is what caused FRENCH to be inserted twice!!).

So be careful with your usage of "/". For plain SQL, it's a matter of choice. Typically, this is used in DDL statements and semi-colons in DML statements. Be consistent to avoid surprises.

Remember "/" is just a RUN (or push) command in the tool, not part of Oracle SQL itself. If you run my attached example script in SQLTools, you will get an error on "/".

Always, run your scripts in SQL*Plus to make sure it will run fine in production, as this is a tool of choice for several DBAs.

Forward Slash ("/") in PL/SQL

PL/SQL on the other hand is a group of SQL statements with embedded semicolons. In this case, a semicolon alone cannot be used to send to DB. It needs a push with "/"! So, in this case, it acts as a Statement Terminator as well.

SQL> list
 1 DECLARE
 2    x NUMBER;
 3 BEGIN
 4    SELECT 10 as num INTO x FROM dual;
 5    Dbms_Output.put_line('x = ' || x);
 6
 7 END;
 8
 9
 10
 11
 12
 13*
SQL>

In the above example, Notice lines  7 - 13 are blank. This is because I pressed ENTER several times. Since SQL*Plus knows this a PL/SQL (declare, begin...end), it waits for me to signal the real end, which is..... a "/". Until I typed "/" on line 13, it didn't come out of the PL/SQL editing mode. "/" also pushed the PL/SQL block to server to execute it.

Even though this has a bunch of lines, this whole text is a single PL/SQL statement. Type list at the SQL*Plus Prompt. You will see the "single" statement listed in full (minus the "/").

SQL*Plus commands

SQL*Plus commands like SET, SHOW, SPOOL etc are typically one liners and should not be ended with semicolon (and don't need "/" either).

SQL> show serveroutput;
serveroutput OFF
SQL>

These commands will be executed locally in the tool and won't be stored in SQL buffer. So, a "/" after a SQL*Plus command will not re-execute it. In fact, it will re-execute the previous SQL Statement in the buffer!!

(Note: A "/" must be the first non-whitespace character (space, tab etc) on a line by itself. It cannot be at the end of a line, like semi-colon can be).

I've also posted a more detailed description of SQL*Plus here.

Notes

1. There is a subtle difference: RUN actually lists before it runs; / doesn't!


2. This is a local message from SQL*Plus. If this was from Oracle database server, you would see a "ORA" prefix.

Example Script

-- Example data for SQL*Plus related blogs
DROP TABLE greetings;
CREATE TABLE greetings (id NUMBER, lang VARCHAR2(10), msg VARCHAR2(30));
INSERT INTO greetings VALUES (1, 'ENGLISH', 'Hello World');
INSERT INTO greetings VALUES (2, 'FRENCH', 'Bonjour le monde');
/
INSERT INTO greetings VALUES (3, 'GERMAN', 'Hallo Welt');
list
/
INSERT INTO greetings VALUES (4, 'SPANISH', 'Hola Mundo')
/
COMMIT;
-- Here are some extra notes about this script:
-- Just Hello world in some languages. Translations are approx. from Google.
-- Even though we are submitting this script to SQL*Plus entirely,
--   it executes one SQL Statement at a time. List command shows this.

-- Forward Slash after FRENCH, reexecutes previous command. so you will see
-- 2 FRENCH records inserted.
-- Forward Slash after GERMAN was, by mistake, thought to run list, but instead it
-- it ran the previous SQL statement, thus we have 2 GERMANs!!
-- Forward Slash after SPANISH inserts only once - because it's missing semi-colon (;)
-- and / substitutes for it!!
-- Commit is required in Oracle SQL. By default, Autocommit is not turned on in SQL*Plus
-- To see/set default use SHOW/SET AUTOCOMMIT

Further Reading

• See my Take 2 on this topic here.


• If you are looking for any relationship between forward slash and commits (there is none), please see my post here.


• If you are looking to see if Forward Slash somehow affects DDLs, please see here.


•  Please see here for a more detailed description of SQL*Plus in general.

References

SQL BNF Grammars

1. http://savage.net.au/SQL/sql-92.bnf.html


2. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql2bnf.aug92.txt


3. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt