Category Archives: 21c

Creating a Java Stored Procedure in Oracle Database

This blog post provides a quick (and dirty) way of creating Java Stored Procedures in Oracle Database because I can’t ever remember how to do that. The Java Developer’s Guide details the use of Java in the database, chapter 5 explains how to create Java Stored Procedures. Please refer to the documentation for a proper discussion of Java in the Oracle database.

This blog was written using

  • Oracle 21c Enterprise Edition patched to 21.7.0
  • Oracle Linux 8.6
  • VirtualBox 6.1.40

Java stored procedures are written in Java (unsurprisingly). Before they can be used they have to be made available to the PL/SQL and SQL layer of the Oracle database. Therefore there are a few extra steps involved compared to writing stored procedures in PL/SQL.

Creating the Java Source

Rather than relying on the loadjava tool this post uses the CREATE JAVA command to create and compile the Java source. Note that errors in the code are not reported back to you so make sure that what you’re loading into the database is valid Java and complies with the requirements for Java Stored Procedures (like using static functions etc).

public class HelloClass { 
    public static string hello( string who )  {
        return "hello, " + who ; 

This creates 2 new objects in the schema, a JAVA source and its associated class in my current schema.

    created > sysdate - 1;

_________________ ______________ 
HelloClass        JAVA CLASS     

With the Java class stored in the database the next step is to make it available to the SQL and PL/SQL layers.

Publishing the Java Class

The hello() Java function returns a string, and I’m going to do the same with the PL/SQL call specification.

CREATE FUNCTION hello_java (
    p_who VARCHAR2
NAME 'HelloClass.hello(java.lang.String) return java.lang.String';

The hello_java (PL/SQL!) function takes a single argument, p_who of (database) type VARCHAR2 and returns a VARCHAR2. The function is then mapped to the static hello() function in HelloClass, which is where you enter the Java world. hello() takes a string as an input parameter and returns a string.

Using hello_java

Once the PL/SQL call specification is created, it’s very easy to use the function:

    hello_java('world') AS greeting

hello, world 

Although I named the function hello_java, there is no need to specify that Java is used under the covers. It just makes it easier for me to see that this function isn’t a PL/SQL but rather a Java function. Any valid PL/SQL identifier can be used in the call specification. Speaking of PL/SQL, I can of course use hello_java() in PL/SQL:

    l_string VARCHAR2(100);
    l_string := hello_java('world');

Which prints “hello, world” once serveroutput is enabled.


I can never remember how to create Java stored procedures and hope this post helps you save 5 minutes as it does for me. There is of course a lot more to say about the topic, so please head over to the Java Developer’s Guide for more details.


Swingbench: creating benchmark schemas with strong passwords

This post describes my experience creating the SOE user account in an Oracle 21 database provided by Oracle’s Database Cloud Service (DBCS). The same principle should apply for earlier releases as well, at the end of the day it boils down to the use of Oracle’s password verify functions. To avoid any ambiguity: the use of password verification functions isn’t a cloud-only feature ;)

Any security conscious administrator should enforce strong passwords and good password hygiene. There is a lot to be said about the topic, too much even for me :) Please refer to the Database Security Guide for your release for an introduction to the topic in the context of the Oracle database and take it from there.

Password Verify Functions

Quite recently I wanted to install Swingbench in my Oracle 21 system, hosted in Oracle’s Cloud Infrastructure (OCI). Unsurprisingly the database enforces strong passwords. Connected to my PDB (“demo_pdb”) I noticed the following in dba_profiles:

SQL> select profile, resource_name, limit from dba_profiles 
  2  where resource_name = 'PASSWORD_VERIFY_FUNCTION';

------------------------------ -------------------------------- --------------------------------------------------


A weak password unsurprisingly will not work in these circumstances:

SQL> create user martin identified by superWeakPassword;
create user martin identified by superWeakPassword
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20000: password must contain 2 or more digits

In other words, you need to provide a strong password for Swingbench’s schema creation wizards as well.

Calling OEWizard

I commonly use the command line to create Swingbench’s benchmark schemas. This time around I wanted to create the Swingbench Order Entry schema. To adhere to the password-complexity rule I have to provide Oracle with a password containing special characters. As per the create user SQL command, passwords containing those special characters need to be enclosed in double-quotes. Now the trick is to read man(1) bash correctly, namely the section on quoting.

The keep it short you need to wrap the actual password into a single quote/a double quote combination, like so:

./oewizard -allindexes ... -u soe -p '"superSafeTempPassword"' ...

This way the passwords will make it all the way to oewizard, allowing it to create the user successfully.

Change those passwords immediately

Unfortunately there is no other way than providing passwords to oewizard on the command line. They will be visible to other users on the system, so make sure to change them immediately as soon as the wizard finished with the schema creation.

Password complexity rules

By the way, the Oracle-provided password verification functions and their password complexity rules are explained in chapter 3 of the Database Security Guide. If you get ORA-28003/ORA-20000 in OEWizard or later in SQL*Plus when changing the password your new password doesn’t adhere to the complexity rules.

Happy benchmarking!