Brajesh Singh Bhadoriya

 Profile Answers by Brajesh Singh Bhadoriya 

• Jan 16th, 2007

 

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

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.

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.