CODEWITHSUNDEEP
oracle-databaseplsql
AJay
AJay

Reputation: 1

How to create new table and insert using function in plsql?

I want to create a function in plsql to create table dynamically

   Create or replace procedure p_dynamic_table (p_table varchar2, 
    col_specs varchar2)
    as
    sql_stmt varchar2 (2000);
    begin
       sql_stmt := 'CREATE TABLE' || p_table || '(' || col_specs || ')';
      EXECUTE IMMEDIATE sql_stmt;
    end;

Upvotes: 0

Views: 251

Answers (3)

Mohammad Osman Gani Faisal
Mohammad Osman Gani Faisal

Reputation: 45

Are you want to create Procedure or Function?

You mentioned Function in Topics header but try to create Procedure (according the mentioned code by you).

If you don't want to create constraints & Indexes then your Procedure is okay without

SQL_STMT := 'CREATE TABLE' || P_TABLE || '(' || COL_SPECS || ')';

you must be put SPACE after 'CREATE TABLE' which is already mentioned in answer of Stew Ashton.

According to what I understood, I tried to mention the solution below.

PROCEDURE

    CREATE OR REPLACE PROCEDURE P_DYNAMIC_TABLE (P_TABLE      VARCHAR2,
                                                 COL_SPECS    VARCHAR2)
    AS
       SQL_STMT   VARCHAR2 (2000);
    BEGIN
       SQL_STMT := 'CREATE TABLE ' || P_TABLE || '(' || COL_SPECS || ')';

       EXECUTE IMMEDIATE SQL_STMT;
    END P_DYNAMIC_TABLE;

Execute Procedure

    EXEC P_DYNAMIC_TABLE('TEST_TBL','COLUMN_1 NUMBER , COLUMN_2 VARCHAR2(100), COLUMN_3 DATE');

Upvotes: 0

Pai
Pai

Reputation: 1

You can try this!

create or replace procedure f2
is
begin
execute immediate 'create table &nds_ddl(&c1 &datatype)';
end;

Then execute the procedure f2 i.e

exec f2;


Select * from table_name_you_entered;

Upvotes: -1

Stew Ashton
Stew Ashton

Reputation: 1529

Put a space after TABLE and before the ending single quote.

Then your procedure will "work". It will not be a function, but a procedure. Also, it is a procedure that has no added value so I don't understand why you want to code it.

Best regards, Stew Ashton

Upvotes: 3

Related Questions