Skip to Main Content
  • Questions

  • Update a column in a table after checking its existence

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Aryan.

Asked: June 13, 2023 - 8:09 pm UTC

Last updated: June 20, 2023 - 9:04 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hello Tom,
I wrote a PL/SQL block for updating a column in a table after checking whether the column exists in the table.

DECLARE
EID INTEGER;
PID INTEGER;

BEGIN
SELECT COUNT(*) INTO EID FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'employee_details' and COLUMN_NAME='ALT_ID';
SELECT COUNT(*) INTO PID FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'department_details' and COLUMN_NAME='ALT_ID';

IF (EID > 0) THEN
UPDATE EMPLOYEE_DETAILS set ALT_ID = 1234 WHERE EMP_ID = '3456';
END IF;

IF (PID > 0) THEN
UPDATE DEPARTMENT_DETAILS set ALT_ID = 1234 WHERE EMP_ID = '3456';
END IF

EXCEPTION WHEN OTHERS THEN NULL;
ROLLBACK;
END;
/
COMMIT;
EXIT;


The above works if the column is present and throws an invalid identifier error if the column is not present.
Could you please let me know why the statement gets executed even though the condition is false?
Also, I noticed that the dynamic statement works without any errors.

Thanks,
Aryan

and Chris said...

Before running the PL/SQL block, the database checks it's valid. This means any tables and columns you access in SQL statements must exist. If they don't you can't run the code:

create table t (
  c1 int
);

declare
  id integer;
begin
  select count(*) into id from user_tab_columns 
  where  table_name = 'T' and column_name = 'NOT_HERE';

  if (id > 0) then
    update t set not_here = 0 where c1 = 1;
  end if;
end;
/
/*
ORA-06550: line 8, column 18:
PL/SQL: ORA-00904: "NOT_HERE": invalid identifier
*/


This makes the column check beforehand pointless. You can't run the code if the column is missing. You have to use dynamic SQL if you want to do this. Though I'd really have to question why you need to do this!

Could you please let me know why the statement gets executed even though the condition is false?

I'm unsure what you mean: which statement and which condition?

A couple of other points:

- The table names in the queries are in lowercase. Unless you've created the tables with quoted lowercase names, they'll be in uppercase in the data dictionary
- WHEN OTHERS NULL means you suppress every exception. This is a bad idea; if something unexpected is happening you won't know.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Aryan, June 16, 2023 - 2:15 pm UTC

Thanks Chris for the explanation

Aryan, June 16, 2023 - 2:21 pm UTC

I'm unsure what you mean: which statement and which condition?
>>> My understanding was IF (PID > 0) THEN, the statement under this condition will not be executed since
SELECT COUNT(*) INTO PID FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'department_details' and COLUMN_NAME='ALT_ID'; will return 0 if the column doesn't exist.




Chris Saxon
June 16, 2023 - 4:47 pm UTC

Let's tackle this in a different way:

If ALT_ID doesn't exist (on either table) - the whole block will fail to compile. Running it will raise an error.

What exactly are you observing? Is the column updated or not? How do you know?

In other words...

Gabriel, June 16, 2023 - 4:40 pm UTC

I don't have anything new to offer but maybe a different perspective could help? My takeaway from previous comments is that the result of 
if (id > 0)
is irrelevant. Troubleshooting the IF condition and what will happen during execution time is missing the real issue. The example could be reduced even further:

BEGIN

IF (0 > 0) THEN
    UPDATE EMPLOYEE_DETAILS set ALT_ID = 1234 WHERE EMP_ID = '3456';
END IF;

END;
/


Now we have a block of code that does nothing because we know the IF condition will never be true. You'll still see the error. Not because the IF condition isn't working as you expect. Not because the UPDATE statement fails when executed, it is never executed. You get the error because the UPDATE statement fails to compile. No matter how many safety checks you wrap around it, it will still be compiled. So the problem lies in all the things the compiler is trying to do behind the scenes.
That is:
-Verify references to valid database objects
-Verify necessary privileges are in place to access those objects
-Create schema object dependencies
etc...

That's the difference when you make the UPDATE statement dynamic. Dynamic SQL forgoes the benefits of static, compiled, statements.

Connor McDonald
June 19, 2023 - 4:40 am UTC

nice input

A reader, June 20, 2023 - 12:20 am UTC

Thanks Chris and Gabriel for your inputs. Now I understand what the issue is.
Chris Saxon
June 20, 2023 - 9:04 am UTC

You're welcome
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library