468,456 Members | 1,776 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,456 developers. It's quick & easy.

EXECUTE IMMEDIATE help

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
Jul 19 '05 #1
4 28106
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

Jul 19 '05 #2
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

Jul 19 '05 #3
you would like to include brackets in the statement as well like this
EXECUTE IMMEDIATE ('ALTER TABLE owner.table_name
ADD (column_name CHAR(1) DEFAULT '' '' NOT NULL)');
Thanks

Faheem
fi********@hotmail.com (finlma) wrote in message news:<8b**************************@posting.google. com>...
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

Jul 19 '05 #4
you would like to include brackets in the statement as well like this
EXECUTE IMMEDIATE ('ALTER TABLE owner.table_name
ADD (column_name CHAR(1) DEFAULT '' '' NOT NULL)');
Thanks

Faheem
fi********@hotmail.com (finlma) wrote in message news:<8b**************************@posting.google. com>...
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

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Agoston Bejo | last post: by
1 post views Thread by lakon15 | last post: by
3 posts views Thread by nghivo | last post: by
3 posts views Thread by Rahul Babbar | last post: by
2 posts views Thread by finlma | last post: by
6 posts views Thread by Oliver | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by subhajit12345 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.