Can we create a table by using a procedure or a function?

Showing Answers 1 - 4 of 4 Answers

Hi.....yes we can create a table by using a procedure  or a function but it must be done by Dynamic SQL. Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. lets say you want to create an EMP table at run time in a procedure. The table name must be like EMP_<location_name>. So to get this we can pass the location name at run time to the procedure. To create table in a procedure u can use the following syntax:

EXECUTE IMMEDIATE 
    'CREATE TABLE ' || 'EMP_' || loc ||
    '(
        empno   NUMBER(4) NOT NULL,
        ename   VARCHAR2(10),
        job     VARCHAR2(9),
        sal     NUMBER(7,2),
        deptno  NUMBER(2)
    )';
You can also drop the table using Execute immediate. for example:

EXECUTE IMMEDIATE 'DROP TABLE ' || 'EMP_' || loc;

This way you use DDL commands in a procedure or function.

Thanks,

Brajesh

prakash chandra patra

  • Mar 28th, 2013
 

EXECUTE IMMEDIATE
*
ERROR at line 1:
ORA-00900: invalid SQL statement

  Was this answer useful?  Yes

Sneha Shrivastava

  • May 3rd, 2013
 

DDL is not possible inside procedure body

veera

  • Nov 25th, 2013
 

It is possible by using Dynamic SQL.
By using EXECUTE IMMEDIATE.

  Was this answer useful?  Yes

Abhijit Musale

  • Mar 23rd, 2015
 

We can create a table in procedure by executing a string statement using EXECUTE IMMEDIATE or using DBMS_SQL.EXECUTE.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions