From: fbortel@nescape.net   
      
   finlma wrote:   
      
   > I'm trying to run an EXECUTE IMMEDIATE within a PL/SQL if loop but it   
   > doesn't work for me. I'm trying to create a column conditionally but   
   > it doesn't work. It fails because there are apostrophes within the   
   > statement. How do I override the apostrophe?   
   >   
   > DECLARE cCount NUMBER;   
   > BEGIN SELECT count(*)   
   > INTO cCount   
   > FROM all_tab_columns   
   > WHERE owner = 'Owner'   
   > AND table_name = 'table_name'   
   > AND column_name = 'column_name' ;   
   >   
   > IF cCount = 0 THEN   
   >         EXECUTE IMMEDIATE 'ALTER  TABLE owner.table_name   
   >         ADD (column_name CHAR(1) DEFAULT ' ' NOT NULL)';   
   > ELSE   
   > 	EXECUTE IMMEDIATE 'ALTER TABLE owner.table_name   
   > 	MODIFY column_name DEFAULT ' '';   
   > END IF;   
   > END;   
   >   
   > Thanks   
      
   You don't override, you escape - with an extra quote:   
   to insert "It's XMAS time" in a table, you would:   
   insert into table(column) values ('It''s XMAS time');   
   And there's inefficient code: you do not need to know   
   *how* many (into cCount), you just want to know IF any:   
   select 1 into cCount from dual   
   where exists (...)   
   would be more efficient.   
   --   
   Regards, Frank van Bortel   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)