Create / Modify SQL Stored Procedure

Question

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

My requirement is to change the column data type in all tables. So, I just get the column name from user_tab_cols. Now I want to create a temporary table that requires a create statement .. but I cannot use this in proc.

Can anyone help me out?

Answer 1

I come from the link to USER_TAB_COLUMNSthat it is Oracle. ALTERand CREATE- these are DDLs that we cannot execute directly in PL / SQL. However, there are several ways around this limitation: EXECUTE IMMEDIATEand DBMS_UTILITY.EXEC_DDL(). I will use EXECUTE IMMEDIATEin 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 data type must be compatible with the existing data type (unless the column is empty), and more and more difficult with some specified data types, such as CLOB.

change

I did not refer to the CREATE TABLE statement. The principle is the same, it just takes longer to print. Also, I don’t quite understand how this relates to your previous requirement to change the data type of these columns.

Answer 2

"exec".