G Frequently Asked Questions About PL/SQL

When Should I Use Bind Variables with PL/SQL?

When you imbed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically. Oracle can reuse these SQL statement each time the same code is executed. To run similar statements with different variable values, you can save parsing overhead by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the right places.

You do need to specify bind variables with dynamic SQL, in clauses like WHERE and VALUES where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (prefixed by a colon) and specify the corresponding PL/SQL variables with the USING clause. Using the USING clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle reuse the SQL statements.

When Do I Use or Omit the Semicolon with Dynamic SQL?

When building up a single SQL statement in a string, do not include any semicolon at the end (inside the quotation marks).

When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block. You will have a semicolon right before the end of the string literal, and another right after the closing single quotation mark.

How Can I Use Regular Expressions with PL/SQL?

You can search for regular expressions using the SQL operator REGEXP_LIKE.

You can test or manipulate strings using the built-in functions REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR.

Oracle's regular expression features use characters like '.', '*', '^', and '$' that you might be familiar with from UNIX or Perl programming. For multi-language programming, there are also extensions such as '[:lower:]' to match a lowercase letter, instead of '[a-z]' which does not match lowercase accented letters.

How Do I Continue After a PL/SQL Exception?

By default, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue executing from the spot where an exception happens, enclose the code that might raise an exception inside another BEGIN-END block with its own exception handler. For example, you might put separate BEGIN-END blocks around groups of SQL statements that might raise NO_DATA_FOUND, or around arithmetic operations that might raise DIVIDE_BY_ZERO. By putting a BEGIN-END block with an exception handler inside a loop, you can continue executing the loop even if some loop iterations raise exceptions.

Does PL/SQL Have User-Defined Types or Abstract Data Types?

The PL/SQL term for these things is "object types". To do object-oriented programming, you use a mix of SQL and PL/SQL. You create the types themselves and the tables to hold them using SQL. You write the bodies of the methods in PL/SQL, and you can also manipulate tables of objects and call object methods through PL/SQL.

The best source of information for object-oriented programming with PL/SQL is the book Oracle Database Application Developer's Guide - Object-Relational Features.

How Do I Pass a Result Set from PL/SQL to Java or Visual Basic (VB)?

PL/SQL lets you issue a query and return a result set using a cursor variable, also known as a REF CURSOR. See "Using Cursor Variables (REF CURSORs)".

How Do I Specify Different Kinds of Names with PL/SQL's Dot Notation?

Dot notation is used for identifying record fields, object attributes, and things inside packages or other schemas. When you combine these things, you might need to use expressions with multiple levels of dots, where it isn't always clear what each dot refers to. Here are some of the combinations.

func_name().field_name
func_name().attribute_name

schema_name.table_name
schema_name.procedure_name()
schema_name.type_name.member_name()

schema_name.package_name.procedure_name()
schema_name.package_name.record_name.field_name

record_name.field_name.attribute_name
record_name.field_name.member_name()

What Can I Do with Objects and Object Types in PL/SQL?

You can create object types by issuing the SQL statement CREATE TYPE using dynamic SQL (the EXECUTE IMMEDIATE statement).

You can use objects to group related values, as you would do with records, with the added advantage that you can store objects directly in the database. You can also create varrays, nested tables, and associative arrays of objects.

You can write PL/SQL procedures and functions that accept objects as parameters. You can pass objects as parameters to these procedures and functions. You can also return objects from functions.

You can write member procedures and functions for an object type in PL/SQL.

You can create overloaded PL/SQL procedures and functions by defining different versions that accept different subtypes of the same supertypes as parameters.

You can write a PL/SQL application in a completely object-oriented way.

You can write a PL/SQL application in a mostly procedural way, using objects and object types to work around limitations on PL/SQL variables and parameters.

How Do I Create a PL/SQL Procedure?

There are several ways to create different kinds of PL/SQL procedures and functions:

CREATE PROCEDURE and CREATE FUNCTION statements in SQL.

CREATE TRIGGER statement in SQL.

CREATE PACKAGE and CREATE PACKAGE BODY statements in SQL. The package body can contain both procedures and functions.

CREATE TYPE and CREATE TYPE BODY statements in SQL. The type body can contain both procedures and functions.

Tip: When using these SQL statements, you might find the CREATE OR REPLACE forms more convenient to allow frequent updates without having to drop the original versions.

PROCEDURE procedure_name IS... or FUNCTION function_name RETURN return_type IS... within the DECLARE section of an anonymous block. These procedures and functions only exist for the life of the anonymous block, and are only visible within the block. This technique makes sense if you have a long-running or extensive anonymous block.

PROCEDURE procedure_name IS... or FUNCTION function_name RETURN return_type IS... within the declarative section of another procedure or function. These procedures and functions are only visible within the outer procedure, so you can use this technique to avoid cluttering up the namespace and prevent unwanted calls from other subprograms.

How Do I Input or Output Data with PL/SQL?

Most PL/SQL I/O is through SQL statements, to store data in database tables or query those tables.

All other PL/SQL I/O is done through APIs that interact with other programs. For example, the DBMS_OUTPUT package has procedures such as PUT_LINE. To see the result outside of PL/SQL requires another program, such as SQL*Plus, to read and display the data passed to DBMS_OUTPUT. (SQL*Plus does not display DBMS_OUTPUT data unless you issue the command SET SERVEROUTPUT ON first.)

Other PL/SQL APIs for doing I/O are HTP (for displaying output on a web page), DBMS_PIPE (for passing information back and forth between PL/SQL and operating-system commands), UTL_FILE (for reading and writing operating-system files), UTL_HTTP (for communicating with web servers), UTL_SMTP (for communicating with mail servers), and TEXT_IO (for displaying text from Oracle Forms).

Although some of these APIs can accept input as well as output, there is no built-in language facility for accepting data directly from the keyboard. For that, you can use the PROMPT and ACCEPT commands in SQL*Plus:

How Do I Perform a Case-Insensitive Query?

You may be familiar with workarounds that allow a query to match VARCHAR2 values regardless of uppercase and lowercase. For example, you can use the UPPER() or LOWER() function on the column value, and build a function-based index on the column. Or you can add another column to the table, with a trigger to store an all-uppercase or all-lowercase copy of another column value, and then query this new column.

Now you can make queries case-insensitive (or even accent-insensitive) without any code changes at all. Add _CI to the usual value of the NLS_SORT initialization parameter, and queries are case-insensitive. Add _CI to the usual value of the NLS_SORT parameter, and queries are accent-insensitive.