CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqldynamic
Ahsan khan
Ahsan khan

Reputation: 3

I want to create table dynamically using plsql here is a code but its not working it shows this error

CREATE OR REPLACE PROCEDURE DYNAMIC_TABLE_CREATE(D_NAME in VARCHAR2, D_TABLE_SPECS in VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE' || D_NAME || '(' || D_TABLE_SPECS || ')' ;

END;

Upvotes: 0

Views: 105

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Dynamic SQL is ... well, option you should use if you must. I'd suggest you not to dynamically create tables.

If you must, then it is a good habit to compose the statement first (into a local variable) and display its contents so that you could check whether it is OK:

SQL> CREATE OR REPLACE PROCEDURE DYNAMIC_TABLE_CREATE (D_NAME         IN VARCHAR2,
  2                                                    D_TABLE_SPECS  IN VARCHAR2)
  3  IS
  4     l_str  VARCHAR2 (200);
  5  BEGIN
  6     l_str := 'CREATE TABLE' || D_NAME || '(' || D_TABLE_SPECS || ')';
  7
  8     DBMS_OUTPUT.put_line (l_str);
  9  --EXECUTE IMMEDIATE l_str;
 10  END;
 11  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC dynamic_table_create('super_man', 'id number');
CREATE TABLEsuper_man(id number)

PL/SQL procedure successfully completed.

SQL>

As you can see, your code is trying to run this statement:

SQL> CREATE TABLEsuper_man(id number)
  2  /
CREATE TABLEsuper_man(id number)
       *
ERROR at line 1:
ORA-00901: invalid CREATE command


SQL>

There's a space missing between table and table name. So, fix it, and then uncomment execute immediate:

SQL> CREATE OR REPLACE PROCEDURE DYNAMIC_TABLE_CREATE (D_NAME         IN VARCHAR2,
  2                                                    D_TABLE_SPECS  IN VARCHAR2)
  3  IS
  4     l_str  VARCHAR2 (200);
  5  BEGIN
  6     l_str := 'CREATE TABLE ' || D_NAME || '(' || D_TABLE_SPECS || ')';
  7     --                    ^
  8     --           add space here
  9     DBMS_OUTPUT.put_line (l_str);
 10
 11     EXECUTE IMMEDIATE l_str;
 12  END;
 13  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC dynamic_table_create('super_man', 'id number');
CREATE TABLE super_man(id number)

PL/SQL procedure successfully completed.

SQL> SELECT * FROM super_man;

no rows selected

SQL>

Now it works.

Upvotes: 2

Related Questions