Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    13

    Unanswered: Checking for column extistance

    Hey All,

    I'm hoping you might be able to help me out with a problem I'm having.
    I was wondering if there is syntax for checking if a column exist. I have to do migration e.g.

    Code:
    insert into TBLone( a ,b ,c ,d ,e ,F ) select a ,b ,c ,d ,e ,F From TBLtwo!

    My problem is that in "TBLtwo" the column "f" may not exist, so I would want to do the migration if it does exist but if it doest not skip it e.g



    Code:
    if column F exists 
       migrate data from TBLtwo.F into TBLone.F 
    else 
      Skip migration of that column

    Anyone got any suggestions or if I didn't explain that well just shout
    Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your first problem is that the code will not compile if the column doesn't exist, so you will have to use an execute immediate. See the following code

    Code:
    declare
    cnt  number;
    begin
      select count(*)
      into cnt
      from user_tab_columns
      where table_name = 'TABLTWO'
      and column_name = 'F';
    
      if cnt = 1 then
        execute immediate 'insert into TBLone( a ,b ,c ,d ,e ,F ) select a ,b ,c ,d ,e ,F From TBLtwo';
      end if;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Nov 2006
    Posts
    13
    Hey thanks for the info, do you mind me asking why you'd have to use the " execute immediate"

    Thanks,

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you entered the statment "insert into TBLone( a ,b ,c ,d ,e ,F ) select a ,b ,c ,d ,e ,F From TBLtwo" into a procedure and the F column in TBLTWO didn't exist, the procedure would not be able to be compiled because of the error.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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