0

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 :)

maher.belkh
  • 473
  • 2
  • 7
  • 21
  • You mean [something like this](http://stackoverflow.com/a/2446009/266304), but altering the default instead of the data type? Or do you want to execute the alter statements dynamically too, [more like this](http://stackoverflow.com/questions/7825167/how-to-loop-through-columns-with-pl-sql/7825273#7825273) (but not dropping, obviously)? – Alex Poole Feb 16 '16 at 09:05

1 Answers1

1

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;
Dinesh V
  • 131
  • 4
  • Thanks a lot ! I think this will do the trick ! This works on oracle ? Two things i did not understand is the IMMEDIATE and the CHR(39)(, why 39 ?) Thanks again. – maher.belkh Feb 16 '16 at 09:30
  • 1) EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block 2) CHR(39) is single quote character '-- ASCII value of single quote – Dinesh V Feb 16 '16 at 09:36
  • Great ! You think that this can be used to do an update field that i just modified to have the old values correctly set with the default value? (something like if the value of the field is not null then insert a 'Y') Sorry if it isn't clear (i am a beginner in oracle and sql) – maher.belkh Feb 16 '16 at 09:41
  • do you mean to update the columns which is null only or not having the value you are expecting – Dinesh V Feb 16 '16 at 09:49
  • Yes i mean after doing the alter table i want to loop on the data (of the fields i just updated) and insert a 'Y' (the default value) when i find a null value so i won't have a null on these fields anymore – maher.belkh Feb 16 '16 at 10:05
  • I had edited the code for you. After altering the column, update statement will be issued to update the null columns with 'Y' – Dinesh V Feb 16 '16 at 10:12
  • Thank for your time ! – maher.belkh Feb 16 '16 at 10:23