How to Develop Java Stored Procedures

I’ll try to demonstrate how we can use loadjava to load our Java class into Oracle Database and use its methods as stored procedures. Before we begin to write the Java codes, I’ll create a simple table. When I call my java stored procedure, it will insert a record to this table.

I created this table in HR schema because I’ll load my java object in HR schema. If you’ll use another schema, then do not forget to load your java code in same schema or set required permissions. This is the main method I’ll use as my stored procedure:

Application Express URL Syntax

The URLs are automatically constructed by APEX, so in most cases we do not need to understand the syntax nor modify it. On the other hand, understanding how URL (f?p) syntax works can be useful while defining hypertext links to the pages.

Let’s take a look at the parameters send as variable “p”:

http://<servername>:<port>/pls/f?p=
App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

App: Uniquely identified application ID

Page: Unique page ID

Session: Randomly generated session ID – to keep your session information.

Request: The value of REQUEST. When you click a button, the value of REQUEST will keep the name of the button. Bind variable: :REQUEST

Debug: DEBUG flag. Setting this flag to YES displays details about application processing.

ClearCache: Clears the cache if you specify he numeric page number.

ItemNames: Comma-delimited list of item names (stored in user’s session).

ItemValues: List of item values (stored in user’s session)

PrinterFriendly: If it’s set to YES, the page is rendered in printer friendly mode.

About vmstat (Virtual Memory Statistics)

The vmstat helps you to identify bottlenecks on your server. It displays real-time performance information about processes, memory, paging, disk I/O, and CPU usage.

Here’s sample output (Oracle Linux 5.6):

Descriptions of the columns:

r: The number of processes waiting for run time
b: The number of processes in uninterruptible sleep
swpd: Total virtual memory (swap) in use (KB)
free: Total idle memory (KB)
buff: Total memory used as buffers (KB)
cache: Total memory used as cache (KB)
si: Amount of memory swapped in from disk (KB/s).
so: Amount of memory swapped to disk (KB/s).
bi: Blocks received from a block device (blocks/s).
bo: Blocks sent to a block device (blocks/s).
in: The number of interrupts per second, including the clock.
cs: The number of context switches per second.
us: User-level code time as a percentage of total CPU time
sy: System-level code time as a percentage of total CPU time
id: Idle time as a percentage of total CPU time.
wa: Time spent waiting for IO.
st: Time stolen from a virtual machine.

How to Add Swap Space in Linux

If the swap space is not enough, you can add a new swap file to your server. Login as root user and then run the following commands to add 10GB of swap space:

You can verify if new swap space was added successfully:

If you want to remove the swap file, run the following commands:

If you want to add this swap space permanently, you need to include it in your /etc/fstab file. Edit the fstab file and add an entry that looks like this:

How to Delete Duplicate Rows from Oracle Tables

Let’s see how we can delete the duplicate rows in a table. First we need to find the duplicated records by comparing/grouping columns, then we’ll use ROWID to separate the rows.

Here’s a script to generate the sample table:

In this table, column named “no” is unique, but I’ll use ROWID to identify the rows. I’ve added this column to show which records will not be deleted.

Let’s remove the duplicate rows:

Here’s the result after deleting rows:
NO NAME PHONE
——————————
1 GOKHAN 232-4446735
4 TOM 800-4446735
7 MIKE 900-4446735