Re: EXECUTE IMMEDIATE help

From: Frank <fbortel_at_nescape.net>
Date: Mon, 15 Dec 2003 22:08:27 +0100
Message-ID: <brl7bh$i6s$1_at_news2.tilbu1.nb.home.nl>


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
Received on Mon Dec 15 2003 - 22:08:27 CET

Original text of this message