Parameterizing values for oracle sql

Question

I am using Oracle - SQL developer

Want to check the count of null values for each column .

Currently I am using the below to achieve results.

select COLUMN_NAME from all_tab_columns where table_name = 'EMPLOYEE'

    SELECT COUNT (*) FROM EMPLOYEE WHERE <Column_name1> IS NULL
    UNION ALL
    SELECT COUNT (*) FROM EMPLOYEE WHERE <Column_name2> NULL
    UNION ALL
    SELECT COUNT (*) FROM EMPLOYEE WHERE <Column_name3> IS NULL
    UNION ALL ......................

How can we use bind value to run the below query like

   DEFINE Column_name = Column_name1
   SELECT COUNT (*) FROM EMPLOYEE WHERE &&Column_name IS NULL .

NUM_NULLS in all_tab_columns would have the number of NULL values in this table. Provided the table has stats updated!! Any data inserted/updated without gathering stats would result in wrong answer. To know about stats you can refer Oracle Docs — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Aug 27, 2014 at 10:20

Sathyajith Bhat · Accepted Answer · 2014-08-27 10:35:17Z

You can't use bind variables when you're constructing the select statement, you can pass values via bind variables, but the select statement itself cannot be constructed. You have to go the dynamic SQL way, using EXECUTE IMMEDIATE.

Here's an example:

DECLARE
    v_sql_statement VARCHAR2(2000);
    n_null_count    NUMBER;
BEGIN
    FOR cn IN (SELECT column_name
               FROM   user_tab_cols
               WHERE  table_name = 'EMPLOYEE') LOOP
        v_sql_statement := 'SELECT COUNT(1) FROM EMPLOYEE where '
                           || cn.column_name
                           || ' IS null';

        EXECUTE IMMEDIATE v_sql_statement INTO n_null_count;

        dbms_output.Put_line('Count of nulls for column: '
                             || cn.column_name
                             || ' is: '
                             || n_null_count);
    END LOOP;
END;

This is what the above query will fetch

Count of nulls for column: EMPNO is: 0
Count of nulls for column: NAME is: 0
Count of nulls for column: JOB is: 0
Count of nulls for column: BOSS is: 1
Count of nulls for column: HIREDATE is: 0
Count of nulls for column: SALARY is: 0
Count of nulls for column: COMM is: 20
Count of nulls for column: DEPTNO is: 0

Why not a single EXECUTE IMMEDIATE using all_tab_columns? COUNT(COLUMN_NAME1),COUNT(COLUMN_NAME2) FROM TABLE?
@MaheswaranRavisankar COLUMN_NAME1, COLUMN_NAME2... COLUMN_NAMEn are rows in all_tab_columns, you need to loop through all rows and find no of nulls for each column_name

Robert3452 · Accepted Answer · 2014-08-27 10:20:20Z

0

No you can't do this with bind variables. This is because the query is complied and it must know the field name at compilation time. To achieve what you want you you can build the query you need in a VARCHAR2 and use execute immediate.

answered Aug 27, 2014 at 10:20

Robert3452

1,5162 gold badges20 silver badges41 bronze badges

Comments

Srini V · Accepted Answer · 2014-08-27 10:30:33Z

0

Why not simply using NUM_NULLS? But you should gather stats before

exec dbms_stats.gather_table_stats('user','TBL');

select num_nulls from user_tab_columns where table_name='TBL';

You can also make use of coalesce for the faster execution than IS NULL

SELECT COUNT(COALESCE( _COLUMN, 1)) AS CNT FROM _TABLE;

If you want to have a column wise count then I would second Sathya's answer but with COALESCE instead of IS NULL

answered Aug 27, 2014 at 10:30

Srini V

11.5k15 gold badges70 silver badges93 bronze badges

Collectives™ on Stack Overflow

Parameterizing values for oracle sql

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related