cancel
Showing results for 
Search instead for 
Did you mean: 
Read only

executing the result of sql statement (EXECUTE IMMEDIATE)

Baron
Participant
0 Kudos
1,691

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
View Entire Topic
0 Kudos

Hello this is Gulshan Negi Well, I searched about it on the internet and I found that The reason why 2, 3, and 4 are not working is that they are trying to pass a result set as an argument to the EXECUTE IMMEDIATE statement. As you mentioned correctly, EXECUTE IMMEDIATE expects a string as an argument. In contrast, the reason why 5 works is that it converts the result set to a string by concatenating an empty string to it. This creates a string that contains the SQL statement, which can be executed by the EXECUTE IMMEDIATE statement. Note that this approach may not work in all cases, especially if the result set is large, in which case concatenating an empty string to it may cause performance issues. In such cases, you may want to consider using a cursor to iterate over the result set and execute the SQL statement for each row. Thanks