Norman's Database Guide

Question: Oracle Dynamic Sql – Create Table?
create or replace procedure create_table (TABLENAME VARCHAR2, COLUMN_NAME VARCHAR2) AUTHID CURRENT_USER
IS
my_cursor number;
my_interogation varchar2(100);
my_goguINTEGER;
BEGIN

my_cursor :=dbms_sql.open_cursor;
my_interogation := ‘CREATE TABLE :my_table( COLUMN :my_column VARCHAR2(20))’;

DBMS_SQL.PARSE(my_cursor,my_interogation,DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(my_cursor, ‘:my_table’, tablename);
DBMS_SQL.BIND_VARIABLE( my_cursor, ‘:my_column’, column_name);

my_gogu := DBMS_SQL.EXECUTE(my_cursor);

DBMS_SQL.CLOSE_CURSOR(my_cursor);

END;
/

So, I’m trying to create a table. This is the procedure.
The procedure is created without errors. But when I try to execute it like:
BEGIN
create_table(‘tablename’, ‘columnname’);
END;
/
I get these messages:
BEGIN
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at “SYS.DBMS_SYS_SQL”, line 908
ORA-06512: at “SYS.DBMS_SQL”, line 39
ORA-06512: at “SYSTEM.CREATE_TABLE”, line 11
ORA-06512: at line 2

SQL> spool off;

Anybody knows what’s wrong or why?
DBMS_SQL.PARSE(my_cursor,my_interogation, dbms_sql.native) — at that line

Answer:

Answer by TheMadProfessor
Haven’t used PL/SQL in a long time (and hardly ever in dynamic mode), but the line

my_cursor :=dbms_sql.open_cursor;

looks suspicious to me for 2 reasons

1) shouldn’t ‘dbms_sql’ be in all caps?
2) the context suggests ‘my_cursor’ should be a SQL cursor object (which would be declared as some sort of select query) but you just define it as a simple number-type