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?

Answer 1

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.