Home » SQL & PL/SQL » SQL & PL/SQL » Select Count Within Cursor Loop
Select Count Within Cursor Loop [message #301259] Tue, 19 February 2008 21:25 Go to next message
gbagdona
Messages: 3
Registered: February 2008
Junior Member
I have been trying to develop simple scripts for SQL*Plus to do analysis on our database prior to conveting it to a new application system. I wanted to use the system tables to do this in a more automated way. Here's the code I tried:

Declare
v_notnull_cnt number := 0;

cursor column_cur IS
select column_name
from all_tab_columns
where table_name = 'TITLE' and owner = 'AIMS'
order by column_name;

Begin

for column_rec in column_cur
loop

select count(*) into v_notnull_cnt
from TITLE
where column_rec.column_name is not null;

dbms_output.put_line(column_rec.column_name || ',' || v_notnull_cnt);

end loop;

end;

What is returned are all column names in the table, following by the total count of records in the table not only those that are not null.

I have tried using Open-Fetch-Close and that didn't work either, so there is something fundamentally wrong with my understanding of cursors.

Thanks for any help any of you can provide.

Re: Select Count Within Cursor Loop [message #301267 is a reply to message #301259] Tue, 19 February 2008 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>where column_rec.column_name is not null;
Under what condition(s) can COLUMN_NAME be NULL?

If you have reasonably current DB statistics, results could be generated by joining DBA_TABLES & DBA_TAB_COLUMNS using only SQL.
Re: Select Count Within Cursor Loop [message #301301 is a reply to message #301259] Wed, 20 February 2008 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think this as an exercise as it is not viable in real world.
You have to use dynamic SQL for this.

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Mon, 25 February 2008 00:09]

Report message to a moderator

Re: Select Count Within Cursor Loop [message #302211 is a reply to message #301301] Sun, 24 February 2008 22:40 Go to previous messageGo to next message
gbagdona
Messages: 3
Registered: February 2008
Junior Member
I guess I didn't explain my problem/question correctly about cursors. I know I can use the DB system tables to generally analyse the DB but what I was trying to do was get farther down into the data.

For example, "How many occurrences of Column_A are null where Column_B = 'C' and Column_C = 'X'?"

I can do this one column at a time or to try to use the Tab_Columns table to get the column names for a table.

One reply was that I had to use Dynamic SQL. If that entails programming then that leaves me out.

My question is, why does the select count in the cursor loop always return the total records in the DB rather than those that are just null? Is this a function of the cursor fetch?

Thanks,
Re: Select Count Within Cursor Loop [message #302217 is a reply to message #301259] Sun, 24 February 2008 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
My assessment is that you know some (random?) database terms, names & phrases; but have little to concept of these terms or how Oracle/SQL works.

What business problem are you actually trying to solve?
Based upon what quantifiable criteria would an independent observer be able to conclude your problem has been successfully solved?
Re: Select Count Within Cursor Loop [message #302221 is a reply to message #302217] Sun, 24 February 2008 23:26 Go to previous messageGo to next message
gbagdona
Messages: 3
Registered: February 2008
Junior Member
This forum is "SQL & PL/SQL Newbies" and I thought I would get some help, but I see that I won't. I have unsubscribed from this topic and the forum as I see it doesn't tolerate newbies.
Re: Select Count Within Cursor Loop [message #302228 is a reply to message #302221] Mon, 25 February 2008 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you don't tolerate questions.
You are free to do it as you want.

Quote:
why does the select count in the cursor loop always return the total records in the DB rather than those that are just null?

Because what you do is not what you think you do.
As I said, you have to use dynamic SQL to do what you want to do.

Regards
Michel
Re: Select Count Within Cursor Loop [message #347432 is a reply to message #302228] Thu, 11 September 2008 11:33 Go to previous messageGo to next message
OCMinProgress
Messages: 1
Registered: September 2008
Location: Boca Raton, FL
Junior Member
While reading through this post i was perplexed as to how unhelpful some of the advice was to this newbie; not to mention the rudeness by Michel. Until i saw he is from France...it all makes sense now.
Re: Select Count Within Cursor Loop [message #347434 is a reply to message #347432] Thu, 11 September 2008 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how unhelpful some of the advice was to this newbie

And you just added one more.

Regards
Michel

Re: Select Count Within Cursor Loop [message #347436 is a reply to message #347432] Thu, 11 September 2008 11:45 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Quote:
Until i saw he is from France...it all makes sense now.


Didn't quite get this?
Re: Select Count Within Cursor Loop [message #347441 is a reply to message #347436] Thu, 11 September 2008 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ehegagoka wrote on Thu, 11 September 2008 18:45
Quote:
Until i saw he is from France...it all makes sense now.


Didn't quite get this?

Just racism or congratulations depending if you are pessimist or optimist (or the opposite).

Regards
Michel

[Updated on: Thu, 11 September 2008 11:59]

Report message to a moderator

Re: Select Count Within Cursor Loop [message #347443 is a reply to message #347441] Thu, 11 September 2008 12:00 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Oh OK, the "R" word, I do experienced that *a lot* in my place here.
Previous Topic: Get Main Filters
Next Topic: Reverse Word
Goto Forum:
  


Current Time: Fri Dec 09 17:37:31 CST 2016

Total time taken to generate the page: 0.55711 seconds