SQL returning error when selecting non-existent field

Question

It has a system that several companies use, but changes in bank tables are not informed.

You could create a select that does not return an error by not finding a field recently removed from a table?

Code:

SELECT 
    cd_cliente,
    nm_cliente,
    dt_imp_banco 
FROM 
    dbamv.cliente
WHERE 
    dt_imp_banco IS NOT EXISTS;

Error message:

ERROR: column "dt_imp_banco" does not exist LINE 4: dt_imp_banco SQL state: 42703 Character: 44

Answer 1

Based in that question of SOEN, is possible, but not directly. It suggests checking if the column exists and only then run the select:

DECLARE
  v_column_exists number := 0;  

BEGIN
  SELECT count(*) INTO v_column_exists
  FROM user_tab_cols
  WHERE column_name = 'dt_imp_banco' AND table_name = 'cliente';

  IF (v_column_exists > 0) THEN
      SELECT cd_cliente, nm_cliente, dt_imp_banco FROM dbamv.cliente;
  END IF;
END;

---

edited

After the comments in this answer the idea follows the same, check if the column exists before the query. However, since the need is for select to be done regardless of the existing column (in case the query is not made by the other fields), the suggestion would be to create individual validations per column that may exist.

DECLARE
  exist_dt_imp_banco number := 0;  

BEGIN
  SELECT count(*) INTO exist_dt_imp_banco
  FROM user_tab_cols
  WHERE column_name = 'dt_imp_banco' AND table_name = 'cliente';

  SELECT
    cd_cliente,
    nm_cliente,
    CASE exist_dt_imp_banco WHEN 0 THEN null ELSE dt_imp_banco END
  FROM dbamv.cliente;
END;

Thus, if the column exists (in the example, exist_dt_imp_banco > 0), the value of the column in the bank would be consulted; otherwise it would bring null.

DETAIL: the point is that in cases where it is necessary to validate many columns, the code would be very large (and perhaps costly).

Here is an example with three columns to be validated.

DECLARE
  exist_dt_imp_banco number := 0;  
  exist_dt_imp_banco_2 number := 0;  
  exist_dt_imp_banco_3 number := 0;  

BEGIN
  SELECT count(*) INTO exist_dt_imp_banco
  FROM user_tab_cols
  WHERE column_name = 'dt_imp_banco' AND table_name = 'cliente';

  SELECT count(*) INTO exist_dt_imp_banco_2
  FROM user_tab_cols
  WHERE column_name = 'ADD_TMSdt_imp_banco_2' AND table_name = 'cliente';

  SELECT count(*) INTO exist_dt_imp_banco_3
  FROM user_tab_cols
  WHERE column_name = 'dt_imp_banco_3' AND table_name = 'cliente';

  SELECT
    cd_cliente,
    nm_cliente,
    CASE exist_dt_imp_banco WHEN 0 THEN null ELSE dt_imp_banco END,
    CASE exist_dt_imp_banco_2 WHEN 0 THEN null ELSE dt_imp_banco_2 END,
    CASE exist_dt_imp_banco_3 WHEN 0 THEN null ELSE dt_imp_banco_3 END
  FROM dbamv.cliente;
END;