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

Question

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;

What's the question, then? That should do the trick, as long as the col_specs column is valid. Another question: why do you want to do this? — Steven Feuerstein
– Steven Feuerstein, Commented Jun 14, 2019 at 10:20
Steven, there is a space missing after the word ...TABLE'. Agree on the "why?" question. — Stew Ashton
– Stew Ashton, Commented Jun 14, 2019 at 10:22

Stew Ashton · Accepted Answer · 2019-06-14 10:21:29Z

3

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

answered Jun 14, 2019 at 10:21

Stew Ashton

1,5199 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AJay Over a year ago

Thank you for your feedback. could you please elaborate how to create table and columns using function in pl/sql?

Stew Ashton Over a year ago

No I can't, because you don't say what the function should return. Please elaborate as to why you want a function.

Mohammad Osman Gani Faisal · Accepted Answer · 2019-06-15 06:50:31Z

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');

hotfix · Accepted Answer · 2019-06-14 11:27:49Z

-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;

edited Jun 14, 2019 at 11:27

hotfix

3,39624 silver badges36 bronze badges

answered Jun 14, 2019 at 10:21

Pai

13 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related