Oracle PL/SQL Programming, 5th Edition

Binding Variables

You have seen several examples that use bind variables or arguments with NDS. Let’s now go over the various rules and special situations you may encounter when binding.

You can bind into your SQL statement only those expressions (literals, variables, complex expressions) that replace placeholders for data values inside the dynamic string. You cannot bind in the names of schema elements (tables, columns, etc.) or entire chunks of the SQL statement (such as the WHERE clause). For those parts of your string, you must use concatenation.

For example, suppose you want to create a procedure that will truncate the specified view or table. Your first attempt might look something like this:

PROCEDURE truncobj (
   nm IN VARCHAR2,
   tp IN VARCHAR2 := 'TABLE',
   sch IN VARCHAR2 := NULL)
IS
BEGIN
   EXECUTE IMMEDIATE
      'TRUNCATE :trunc_type :obj_name'
      USING tp, NVL (sch, USER) || '.' || nm;
END;

This code seems perfectly reasonable. But when you try to run the procedure you’ll get this error:

ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword

If you rewrite the procedure to simply truncate tables, as follows:

EXECUTE IMMEDIATE 'TRUNCATE TABLE :obj_name' USING nm;

then the error becomes:

ORA-00903: invalid table name

Why does NDS (and DBMS_SQL) have this restriction? When you pass a string to EXECUTE IMMEDIATE, the runtime engine must first parse the statement. The parse phase guarantees that the SQL statement is properly defined. PL/SQL can tell that the following statement is ...