In recent times, IT organizations have been tasked more and more with charting a course for writing new applications and migrating existing applications to a Java 2 Platform, Enterprise Edition (J2EE) environment. On the surface, this task might seem to be a matter of using predetermined methods, as was the case with the migration to client/server technology. However, one major aspect of J2EE is its variety and breadth. There is no one way to create a J2EE system; in fact, there are probably hundreds of combinations of J2EE technologies that could serve any one purpose. This makes the decision of selecting the proper technology combination daunting, especially if the J2EE environment is new to the organization.

Problem

You want to grant someone the ability to create and execute stored PL/SQL programs.

Solution

To grant the ability for a user to create a procedure, function, or package, you must log in to the Oracle database with a privileged account and grant the CREATE PROCEDURE privilege to the user. Here's an example:

Oracle E-Business Suite, also known as Oracle Applications or Oracle EBS, is sophisticated software that works like a software system, which as a whole supplies the enterprise resource planning (ERP), customer relationship management (CRM), and supply chain planning application families. It is a software system that consists of global business applications with built-in integrity.

The large object (LOB) datatype allows us to hold and manipulate unstructured and semistructured data such as documents, graphic images, video clips, sound files, and XML files. The DBMS_LOB package was designed to manipulate LOB datatypes. Starting in Oracle Database 12c, LOBs can store large amounts of data with a maximum size of 128 TB depending on the database block size; a single table can have one or more columns of LOB datatypes, such as binary large object (BLOB), character large object (CLOB), national character large object (NCLOB), and BFILE. This article describes some of the problems that can happen when you have LOB segments in your environment and how to mitigate these problems.

When you think about architecting your backup strategy, as part of the process you must also consider how you’re going to restore and recover Oracle Database 12c. Your backups are only as good as the last time you tested a restore and recovery. A backup can be rendered worthless without a good restore and recovery strategy. The last thing you want to happen is to experience a media failure, go to restore your database, and then find out you’re missing a file, you don’t have enough space to restore, something is corrupt, and so on.

Problem

You want to create a PL/SQL procedure that accepts several parameters. However, some of those parameters should be made optional and contain default values.

Solution

You can allow the procedure caller to omit the parameters if default values are declared for the variables within the procedure. The following example shows a procedure declaration that contains default values: