Ad

Modify The Metadata Of A Database

- 1 answer

This is simple, i have a database that contains many tables and what i want to do is to add a default value to all the fields that represent a boolean (a char(1 byte)). So is there a way to (using a function) write some logic that uses the meta-data of the database and its tables to add that default value without iterating manually on each field in each table ?

Hope this is clear guys :)

Ad

Answer

Use the block given below for doing your task

DECLARE

LV_SQL VARCHAR2(4000);

  CURSOR C_GET_COLUMNS IS
    SELECT TABLE_NAME,COLUMN_NAME,NULLABLE,DATA_LENGTH,DATA_TYPE
      FROM USER_TAB_COLUMNS
     WHERE DATA_TYPE = 'CHAR'
       AND DATA_LENGTH = 1;

BEGIN

  FOR I IN C_GET_COLUMNS LOOP
      LV_SQL := 'ALTER TABLE '||I.TABLE_NAME||' MODIFY '||I.COLUMN_NAME||' '||I.DATA_TYPE||'('||I.DATA_LENGTH||') DEFAULT '||CHR(39)||'Y'||CHR(39);
      EXECUTE IMMEDIATE LV_SQL;
      LV_SQL := 'UPDATE '||I.TABLE_NAME||' SET '||I.COLUMN_NAME||' =  '||CHR(39)||'Y'||CHR(39)||' WHERE '||I.COLUMN_NAME||' IS NULL';
      DBMS_OUTPUT.PUT_LINE(LV_SQL);
      EXECUTE IMMEDIATE LV_SQL;
  END LOOP;

END;
Ad
source: stackoverflow.com
Ad