executing the result of sql statement (EXECUTE IMMEDIATE)

[Baron]

I am trying to execute a statement, the statement is based on the content of one table, so that I should 'select' some table and then pass the result of the sql statement to EXECUTE STATEMENT.

I understand that EXECUTE IMMEDIATE expects a string and not a result set, so its clear why 2 and 3 below dont work, but I am surprised why 5 below works!!

create or replace table mytable(col1 int, col2 varchar(10));
insert into mytable values (1, 'txt1'), (2, 'txt2');
execute immediate ('delete from mytable where col2 = ''txt2''');--1 OK
execute immediate (select 'delete from mytable where col2 = ''txt2''' );--2 NOK
execute immediate ((select 'delete from mytable where col2 = ''txt2''' ));--3 NOK
execute immediate (string (select 'delete from mytable where col2 = ''txt2''' ));--4 NOK
execute immediate ('' || (select 'delete from mytable where col2 = ''txt2''' ));--5 OK

[VolkerBarth]

Hm, check the syntax (*), the statement string does not expect brackets around it, it's simply put after EXECUTE IMMEDIATE, such as

EXECUTE IMMEDIATE 'DELETE MyTable where col2 = ''txt2''';

Furthermore, for statements with a result set, there's the WITH RESULT SET ON clause.

(*): Or to be correct, the "EXECUTE (string-expression)" is T-SQL syntax, whereas the EXECUTE IMMEDIATE is Watcom-SQL, so you seem to use a - probably undefined - mix of SQL dialects?

---

That being said, what exactly does "pass the result set ... to execute immediate" mean? If you want to use DML statements based on queries, that is usually be done via joins in the DML statement (DELETE...FROM...), not requiring dynamic SQL at all, and if you want to get the rows affected by a DML statement, you can use DML-derived-tables aka "SELECT over a DML statement"...