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

asked 13 Dec '22, 06:02

Baron's gravatar image

Baron
1.8k117129153
accept rate: 50%


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"...

permanent link

answered 13 Dec '22, 07:17

Volker%20Barth's gravatar image

Volker Barth
39.2k353537806
accept rate: 33%

edited 13 Dec '22, 07:23

execute immediate ('' || (select 'delete from mytable where col2 = ''txt2''' ));

Of cause this is ok, because the result of the select is the string 'delete from mytable where col2 = 'txt2' and that is a correct string to execute immediate.

That string is equal to say:

declare stringtoexecute long nvarchar;
select 'delete from mytable where col2 = ''txt2''' into stringtoexecute;
//or:
set stringtoexecute = (select 'delete from mytable where col2 = ''txt2''');
//or:
set stringtoexecute = 'delete from mytable where col2 = ''txt2''';
permanent link

answered 14 Dec '22, 04:41

ArcoW's gravatar image

ArcoW
718
accept rate: 0%

edited 14 Dec '22, 04:42

If you would do

execute immediate(stringtoexecute)

that would work As far as I know execute immediate executes a string expression and can't execute statements like for example (select '...')

(14 Dec '22, 07:06) Frank Vestjens
1

The reason is that '' || (select 'hello') actually is a string. Or better: including the ( and ). This is because || takes the arguments and try to cast them to what is possible. In this case, starting with '' || means it 'needs' to be a string, so it makes it as a string. This will do the same:

execute immediate cast((select 'delete from mytable where col2 = ''txt2''') as nchar(100))
(14 Dec '22, 10:15) ArcoW
Replies hidden

Yes, but we still wait for the OP to tell what he is really about to do - if it was just to issue a DELETE statement via EXECUTE IMMEDIATE, the syntax for that is obvious (see my answer) and does not require to mess around with brackets and string concatenation and sub queries and the like. :)

And even for statements with a result set, one does not need brackets, here's how to use a SELECT with a string literal:

execute immediate with result set on 'select ''Hello World!''';
(14 Dec '22, 10:33) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 13 Dec '22, 06:02

question was seen: 171 times

last updated: 14 Dec '22, 10:35