I'm basically learning PL/SQL by reading the book, and I'm trying to cursor through a table that has a list of column names (actually all_tab_columns). Then I want to use the column_name to perform a select that counts the number of rows in the related table that are not null.

The following is what I thought would work but I get a syntax error on the select.

Declare
    v_column_name varchar2(30);

    /* Explicit declaration of cursor */
    cursor column_cur IS
        select column_name from all_tab_columns where table_name = 'ITEM' order by column_name;
Begin
    /* Check to see if cursor is open */
    If not column_cur%ISOPEN
    then
        open column_cur;
    end if;

    loop

        /* fetch row from cursor directly into a variable */
        fetch column_cur INTO v_column_name;
        EXIT WHEN column_cur%NOTFOUND;

        /* do the count of not null */
        select v_column_name as Column_Name, count(v_column_name) as Not_Null
        from ITEM
        where length(v_column_name) <> 0;

    end loop;

    close column_cur;
end;
/

All the examples I have seen use INSERT INTO or DBMS_OUTPUT.PUT_LINE but not a select after the FETCH of the cursor row.

Maybe I've just headed off into the wrong direction. Can anyone get me on track?

Note that I don't have any table creation privileges and if I could get the right GRANT, the DBA is on leave and no one else from our IT Support can help.

Thanks in advance for any help anyone could provide.

gbagdona:
it is not clear as to what you are trying to accomplish. What version of Oracle are you using? Is all_tabs_columns the name of a table or listbox items? What do you want to do with the "null column" row? Further in your code, where you are testing for column <> 0, is not the same as testing for "IS NULL" or "NOT NULL".

Hope this helps.

Thanks for your reply peace95.

We have Oracle 10g.

Essentially I'm trying to analyse tables in a database to determine usage. That is, what columns in the table actually have any data in them. The database is over 10 years old, and data entry guideleines have changed over time. The database has over 2m items and out of those 2m a lot of columns have no data.

We will be converting this database to a new one on a new system, so we're trying to make informed judgements of which data we retain. If a business user states that they want to retain a field, we can say sure but this field only has data entered 0.01% of the time.

The all_tab_columns is one of the Oracle system views of the data dictionary that contains all columns in the database by table. Therefore by using this view I may be able to programmatically go through each table checking each column for the presence of any data.

I did this manually a number of years ago, and am trying to find a way to do this in a more automated way.

I have had problems with previous Oracle versions using IS NULL and NOT NULL and have used LENGTH(column) <> 0 or LENGTH(column) > 0 with success.

It appears that the select in the loop is incorrect but have tried alternatives without success.

The code below starts from the oint that the table name and the field or column ( from all_columns_table) is hard-coded into the block. After I process flowed the anonymous block, I noticed that your code is not that far off.
How It Works:
1. I decided to count everything, number of records read(v_row_count), number of null fields(v_null_count) and the number of not null fields (v_field_count). This will serve as a check and also used to compute the percentages, if needed at some point. In order to get the total record count, I included in the SELECT statment the Primary Key field, it will always be present, whether your test column is null or not null. (See the Declaration Section);

2. Also in the Declaration Section, I declared the CURSOR, which IS absent the WHERE-Clause.

3. The Execution Section begins by Opening the declared CURSOR and LOOP thru each record passing values into the declared variable.
Test for the NULL , using the declared variable ,
v_null BOOLEAN := NULL, and incrementing the counters depending on the true or false comparison results. Here you may want to substitute your LENGTH <> 0 function. If the comparison test shows true, when to the naked-eye there is nothing, gsrbage could exist in the field if default spaces were not declared when field or entry was created.

4. When the LOOP is exhausted, all totals are printed. The CURSOR is closed and execution ends. Here you can calculate the the percentaqes.

5. The optional Exception Section is not included here.
==================== The Code ==================================
SET SERVEROUTPUT ON
DECLARE
  v_row_count PLS_INTEGER := 0;
  v_field_count PLS_INTEGER := 0;
  v_null_count PLS_INTEGER := 0;
  v_nullbit BOOLEAN := NULL;
  v_test_column tblname.tblcolumn%TYPE;
 -- Cursor Declaration
CURSOR tbl_cur IS
   SELECT count(a.tblid),
          a.tblcolumn
    FROM tblname a;
 -- Execution section
BEGIN
   DBMS_OUTPUT.ENABLE(5);
 -- Open cursor
   OPEN tbl_cur;
 -- Loop thru all records
  LOOP
   FETCH tbl_cur INTO v_row_count, v_test_column;
   EXIT WHEN tbl_cur%NOTFOUND;
 -- test for Nulls
  IF v_test_column = v_nullbit
    THEN v_null_count := v_null_count + 1;
    ELSE v_field_count := v_field_count + 1
    END IF;
  END LOOP;
 -- Display All Counts
  DBMS_OUTPUT.PUT_LINE('Total Records: 'v_row_count
                       ||','
                       || 'Total Empty Cells: ' v_null_count
                       || ','
                       || 'Total Cells Used: ' v_field_count)
  CLOSE tbl_cur;
END;
====================== end of code==============================
Suggestions: Work on getting the Execution block working then add selecting the test column and table name...
Warning: This code has not been tested....

Hope this helps. Email me if you have any questions....