Fortunately for many of you, scenarios requiring method 4 dynamic SQL are rare. If, you run into it, however, you should read "Meet Method 4 Dynamic SQL Require- ments" on page 546. 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, com- plex 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 valid: 'UPDATE emp SET sal = :xyz' without having to know the value of :xyz. But how can PL/SQL know if the following statement is well formed? 'UPDATE emp SET :col_name = :xyz' Binding Variables | 525