Create/alter from SQL stored procedure

Question

I want to call create table/ alter table command from a procedure. Is it possible?

My requirement is to change the datatype of a column in all tables. So, I am just getting the column name from user_tab_cols. Now I want to create a temp table which requires create statement .. but i am unable to use that within a proc.

Can anyone please help me out?

In Oracle there is no need to create temp tables on the fly. You should consider using a Global Temporary Table - it is created once only, but each session can use it as if they had their own private copy of it (and the data in it is automatically cleared when the session ends (or at commit time, if you prefer)). — Jeffrey Kemp
– Jeffrey Kemp, Commented Jan 6, 2010 at 12:06

APC · Accepted Answer · 2010-01-04 13:27:24Z

11

I presume from the reference to USER_TAB_COLUMNS that this is Oracle. ALTER and CREATE statements are DDL, which we cannot execute directly in PL/SQL. However, there are a couple of ways around this restriction: EXECUTE IMMEDIATE and DBMS_UTILITY.EXEC_DDL(). I will use EXECUTE IMMEDIATE in the following example.

begin
    for lrec in ( select table_name from user_tab_columns
                  where column_name = 'UNIVERSAL_COLUMN_NAME')
    loop
        execute immediate 'alter table '||lrec.table_name||
                               ' modify UNIVERSAL_COLUMN_NAME varchar2(255)';
    end loop;
end;

Note that the usual restrictions apply: the new datatype has to be compatible with the existing datatype (unless the column is empty), and things are trickier with some specilaized datatypes like CLOBs.

edit

I haven't addressed the CREATE TABLE statement. The principle is the same, it is just longer to type out. Besides, I am not entirely clear how it applies to your prior requirement to change the datatype of those columns.

edited Jan 4, 2010 at 13:27

answered Jan 4, 2010 at 13:21

APC

147k20 gold badges180 silver badges289 bronze badges

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

1 Comment

M.J. Over a year ago

thanks .. i used that one.. and its working absolutely fine. thanks a lot

Fedor Hajdu · Accepted Answer · 2010-01-04 13:18:44Z

0

you can generate the query as string and execute it with 'exec' keyword.

answered Jan 4, 2010 at 13:18

Fedor Hajdu

4,7053 gold badges35 silver badges50 bronze badges

3 Comments

Jeffrey Kemp Over a year ago

exec is SQL*Plus, not PL/SQL

Dan Over a year ago

The OP did not mention Oracle originally. A sufficiently knowledgeable person could infer it from the presence of user_tab_columns but this reply is innocent.

Jeffrey Kemp Over a year ago

innocent, but not very helpful :)

Collectives™ on Stack Overflow

Create/alter from SQL stored procedure

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related