+ Reply to Thread
Results 1 to 2 of 2

Thread: Catch Exception when alter a table

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    24

    Catch Exception when alter a table

    I am trying to alter a column whether it exists or not , i wrote something like :
    Code:
    BEGIN
    
      ALTER TABLE T_Name ADD ( COL_NAME VARCHAR2(100 );
    
      EXCEPTION WHEN OTHERS THEN
        ALTER TABLE T_Name MODIFY ( COL_NAME VARCHAR2(100) );
    
    END;
    But However, Oracle complains about having the ALTER command inside of BEGIN. Is there a way to achieve this without using Begin?

  2. #2
    For me , I'd check whether the column exists first and then issue the DDL :
    Code:
    DECLARE
      l_cnt INTEGER;
    BEGIN
      SELECT COUNT(*)
        INTO l_cnt
        FROM dba_tab_cols
       WHERE table_name  = 'T_Name'
         AND owner       = <<owner of table>>
         AND column_name = 'COL_NAME';
    
      IF( l_cnt = 0 )
      THEN
        EXECUTE IMMEDIATE 'ALTER TABLE T_Name ADD( col_name VARCHAR2(100) )';
      ELSE
        EXECUTE IMMEDIATE 'ALTER TABLE T_Name MODIFY( col_name VARCHAR2(100) )';
      END IF;
    END;

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts