44934

query to find all columns in a table with no value in it

Question:

I have written this query for finding the total number of column which does not have a value in a table but its not working.

SET serveroutput ON; DECLARE v_count NUMBER; v_table_name VARCHAR2(200); CURSOR c2 IS SELECT Column_name FROM all_tab_columns WHERE table_name= A; BEGIN FOR r1 IN c2 LOOP dbms_output.put_line(r1.column_name); EXECUTE immediate('SELECT COUNT(r1.column_name) INTO v_count FROM HR'||'.'||A); IF v_count =0 THEN dbms_output.put_line(v_count); END IF; END LOOP; END;

Answer1:

I would like you to try this code. It works for me :)

SET serveroutput ON; DECLARE myOwner VARCHAR2(20):='HR'; myTable VARCHAR2(25):='A'; CURSOR c2 IS SELECT column_name, avg_col_len FROM all_tab_columns WHERE table_name = myTable AND owner = myOwner; BEGIN FOR r1 IN c2 LOOP IF r1.AVG_COL_LEN =0 THEN dbms_output.put_line('column_name = '||r1.column_name); END IF; END LOOP; END;

Hope this can help you.

Recommend

  • Oracle PLSQL function call into a second function
  • Launch PL/SQL script from batch file (with arguments)
  • select with variable parameter in the procedure
  • How do I handle errors in a batch file that calls sqlplus, because sqlplus doesn't set ERRRORLE
  • catching unknown host exception in codename one
  • display pointlabel in highlighter jqplot
  • Silverlight Tools for Visual Studio 2008 - Placing a Control Issue
  • PL/SQL: re-write SELECT statement using IN parameter in stored procedure
  • Conque SML backspace
  • how to get item from ListView
  • IOS8 Text in TextField Bounces on Focus
  • mongodb num_rows equivalent php
  • Get file path from Uri from Video Chooser
  • Peek MSMQ message with infinite timeout
  • How to export MS Access table into a csv file in Python using e.g. pypyodbc
  • Morphia is there a difference between fetch and asList in performance wise
  • Can't set character_set_results to latin1
  • SQL Procedures - Comparing values
  • psycopg2.ProgrammingError: syntax error at or near “stdin” error when trying to copy_from redshift
  • Tkinter: Determine Widget Position relative to Root Window
  • DML and Exception Handling - Oracle
  • MIT Kerberos fails to locate TGT in MSLSA cache
  • Query timeout expired in django-mssql when executing custom SQL directly
  • JOOQ nested condition
  • mapping between two ontologies
  • Pre-populated SQLite Database not reading properly in Android Studio
  • Converting query results into DataFrame in python
  • Need code translation from VB to C#
  • Best practice to eliminate magic numbers within a member function
  • joining two bezier curves
  • Possible to get mouse events fired when cursor is outside page?
  • Eliminate partial duplicate rows from result set
  • Implementation of State Monad
  • Is there a javascript serializer for JSON.Net?
  • Where to put my custom functions in Wordpress?
  • How to get next/previous record number?
  • Buffer size for converting unsigned long to string
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • Binding checkboxes to object values in AngularJs