execute immediate update returns NULL whilst the non string query works fine

Question

I have an update query which works fine. once I put this in my PLSQL block it returns NULL and there is no error. Prior to this part of the block I insert a default value into the target table, therefore I know the update is actually updating the fields to NULL. Since this query is counting the rows of each column group by another column, then there is always a value for it.and I get the correct count from the query outside of the string..

when I place the query in my plsql Block I use the Execute immediate and the query would be a string in which my_table_name and my_column_names and Table_1 are variable.

I searched alot and found things like I should commit and etc. But problem still there.

for example: Need help in execute immediate update query

    update Table_FINAL r
set column_1  = 
(
    select max(totalcount)
    from    (
            select 'my_table_name' as table_name, 'my_collumn_name' as column_name, column_3, count(*) as totalcount
            from  my_table_name a
            where exists (select 1 from Table_2 where Table_2.column_x = a.column_x)
            group by column_3
            ) s 
    where r.column_3 = s.column_3
)
;

and here the string:

    execute immediate 'update Table_FINAL r
set column_1  = 
(
    select max(totalcount)
    from    (
            select ''' || my_table_name || ''' as table_name, ''' || my_collumn_name || ''' as column_name, column_3, count(*) as totalcount
            from ' ||  my_table_name || ' a
            where exists (select 1 from Table_2 where Table_2.column_x = a.column_x)
            group by column_3
            ) s 
    where r.column_3 = s.column_3
)'
;

Answer 1

it updates column_1 to NULL

If your dynamic SQL returns no rows, totalcount will be null, so max(totalcount) will be null and in that circumstance column_1 will be updated to null.

There are a couple of obvious solutions:

1. only execute the update if there's a value: ... where r.column_3 = s.column_3 and totalcount is not null.
2. handle the null : select max(nvl(totalcount,0)) …

---

Now, you assert that the update query "works fine". Does it work fine for all values you pass as my_table_name? Another reason why dynamic SQL is hard is that we can't look at the source code and know for certain what it's going to do at runtime. So you need to so some debugging. Run the dynamic SELECT statement without the update and see what you're actually executing:

execute immediate ' select max(totalcount) from (
       select ''' || my_table_name || ''' as table_name, ''' || my_collumn_name || ''' as column_name, column_3, count(*) as totalcount
            from ' ||  my_table_name || ' a
            where exists (select 1 from Table_2 where Table_2.column_x = a.column_x)
            group by column_3)' into l_total_count;
dbms_ouput.put_line(my_table_name ||'.'|| my_collumn_name ||' max(totalcount) = ' ||  l_total_count);

Remember to enable SERVEROUTPUT in whatever client you're using.

---

the update string is inside a loop and it seems that in each loop round it indeed does the correct update but then ir updates the rest to NULL … But I do not fully get what I should do since I hvae hundreds of fields.. (sic)

I guessed you were calling this code from a loop. So, it's not that you don't overwrite the "correct update" with null but that you don't want to over-write any value with any subsequent value. I can offer a few suggestions but really this is your data model and your business logic, so only you can decide the correct way to handle this.

1. Aggregate totalcount like this: set column_1 = column_1 + total_count. For this to work you'll need to apply nvl(max(totalcount),0).
2. Add a column to TABLE_FINAL to store values of my_collumn_value. Reference that column in the UPDATE statement's WHERE clause. (Obviously this supposes a completely different result set from the previous suggestion). You may need a column for values of my_table_name too.