Scenario
We want to create stored procedure which the table name as parameter and build an empty table.

CREATE OR REPLACE PROCEDURE Build_Table
(Table_Name IN VARCHAR)
IS
sql_stmt VARCHAR(2000);
BEGIN
sql_stmt := 'create table :t nologging
as select rownum id,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects a where 1=0′;

EXECUTE IMMEDIATE sql_stmt
USING Table_Name;
END;

Problem
The above stored procedure is compiled succesfully. However, when we try to run the stored proc build_table,

SQL> exec build_table('tableA');
we got the following error

ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at “SYSTEM.BUILD_TABLE”, line 14
ORA-06512: at line 1

Solution
After a brief search in google,we found that the problem here is…we cannot use bind variables for table name
And here is an excerpt from Oracle Pl/SQL programming:
“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 entrie chunks of the SQL statement. For those parts of your string, you must use concatenation(operator)”

OK then, we change our dynamic statement using concatenation as below and the problem is solved.

CREATE OR REPLACE PROCEDURE Build_Table
(Table_Name IN VARCHAR)
IS
sql_stmt VARCHAR(2000);
BEGIN
sql_stmt := 'create table '
||Table_Name
||' nologging
as select rownum id,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects a where 1=0′;

EXECUTE IMMEDIATE sql_stmt;
END;