CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
Awknewbie
Awknewbie

Reputation: 269

Parameterizing values for oracle sql

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 .

Upvotes: 0

Views: 177

Answers (3)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

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

Upvotes: 1

Srini V
Srini V

Reputation: 11355

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

Upvotes: 0

Robert3452
Robert3452

Reputation: 1486

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.

Upvotes: 0

Related Questions