Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old September 27th, 2006, 10:47 PM
Registered User
Join Date: Sep 2006
Location: , , Australia.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Oracle Cursor - SQL Plus

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.

    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;
    /* Check to see if cursor is open */
    If not column_cur%ISOPEN
        open column_cur;
    end if;


        /* 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;

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.

  #2 (permalink)  
Old September 28th, 2006, 12:02 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts

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.
  #3 (permalink)  
Old September 28th, 2006, 05:18 PM
Registered User
Join Date: Sep 2006
Location: , , Australia.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

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.

  #4 (permalink)  
Old October 1st, 2006, 04:25 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts

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 ==================================
  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),
    FROM tblname a;
 -- Execution section
 -- Open cursor
   OPEN tbl_cur;
 -- Loop thru all records
   FETCH tbl_cur INTO v_row_count, v_test_column;
 -- 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;
 -- 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 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....


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Magnetic Cursor - Target Area Cursor? gcarcass .NET Framework 2.0 1 May 5th, 2008 07:20 AM
Use returned cursor in another PL/SQL proc robertogalbiati Oracle 0 January 5th, 2006 11:19 AM
PL/SQL Cursor help Wikked Oracle 1 March 12th, 2005 12:19 AM
Get data from oracle cursor avinash sharma LNCT ADO.NET 0 January 5th, 2005 03:39 AM
dynamic-sql-cursor uskiranj Oracle 1 July 5th, 2004 05:54 AM

All times are GMT -4. The time now is 01:02 AM.

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.