Home » Other » Training & Certification » Need Help On Pl/sql ( Very very Challenging)
Need Help On Pl/sql ( Very very Challenging) [message #363308] Fri, 05 December 2008 15:10 Go to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
It's a very Challenging Question....Very very helpful if you give the Answer

It's all about loop Functions

Be carefull..It's a bit hard to understand

I have 5 tables....Each Table has 10 Columns

Table 1

1) column 1
|
|
|
10) Column 10

Table 2

1) Column1
2) Column 2
..........................Column 10

like tht i have 5 tables with each 10 columns total 50 columns..

Assume tht i got all the data in an excel sheet...

Now i wrote 10 queries

1) Count()
2) Nulls
3) Not Nulls

.........................like tht i wroke 10 queries

i have to run all the 10 queries to each and every column (50 Columns)

Can anybody help on tht
i heard tht we can work the loop function through pl/sql

the process should be
picks the first column and run all 10 queries and next pick the second column and run all the queries and finally displaying all the results at the same time......

Whoever sloves this questions is going to be a Hero in this Forum.I vote for U...........lol

Thanks
please make it Fast i got the deadline on Monday Hurry Up!!!


Re: Need Help On Pl/sql ( Very very Challenging) [message #363310 is a reply to message #363308] Fri, 05 December 2008 15:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Be carefull..It's a bit hard to understand

So post a test case: create table and insert statements along with the result you want with these data.

Before please read 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 (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Quote:
i have to run all the 10 queries to each and every column (50 Columns)

What does this mean?

Quote:
picks the first column and run all 10 queries and ...

Which 10 queries?

Quote:
Whoever sloves this questions is going to be a Hero in this Forum

If he or she understand what you posted sure he or she will be my hero.

Quote:
please make it Fast i got the deadline on Monday Hurry Up!!!

Do your homework and post what you tried.
By the way, don't use IM speak, this is also in the rules mentionned above.

What is very very challenging is to understand what you post.

Regards
Michel

[Updated on: Fri, 05 December 2008 15:19]

Report message to a moderator

Re: Need Help On Pl/sql ( Very very Challenging) [message #363317 is a reply to message #363308] Fri, 05 December 2008 15:52 Go to previous messageGo to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
Oracle 10.2.0

Ok Ok I will explain it again....

ok assume it i have 1 table it has 50 cloumns

i wrote 10 queries

1) select Count(column1) from Table;

2) Select count(distinct(column1) from Table;

3) select Count(*)-Count(distinct(Column1)) from Table;

like tht i wrote 10 queries to work out on a column

I want to know weather is it possible to Run the same queries for all the 50 columns at the same time

i mean once the column1 execute all the queries once tht done tht system has to pick the column2 to run the same queries but instead of column2 on column1

is there any loop function tht we can work out this

Thanks
Re: Need Help On Pl/sql ( Very very Challenging) [message #363321 is a reply to message #363308] Fri, 05 December 2008 16:42 Go to previous messageGo to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
People Must be Polite in this Forums ..

If You guys didn't Understand the Question ask it Agiain We will try to Explain it Better

Don't laugh on others Doubts....do remember tht You guys came from the Juniour level itself....


Thanks

[Updated on: Fri, 05 December 2008 16:44]

Report message to a moderator

Re: Need Help On Pl/sql ( Very very Challenging) [message #363325 is a reply to message #363321] Fri, 05 December 2008 17:54 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@malladipavan,

I am still not clear with the your requirement. Maybe Native Dynamic SQL can help you achieve this.

Go through the link and see if it helps you meet the requirement.
Also searching for some information on DBA_TAB_COLS or ALL_TAB_COLUMNS or USER_TAB_COLS might be useful.

Regards,
Jo
Re: Need Help On Pl/sql ( Very very Challenging) [message #363327 is a reply to message #363325] Fri, 05 December 2008 18:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The dictionary views would be populated only after collecting statistics. It is just not reliable, as it might differ based on sampling/method used and anyone can spoof it.
Not sure why OP would be interested in these.
@OP
>>People Must be Polite in this Forum
That should apply across the board. Right?
Urging someone to "hurry" to meet your deadline is exactly ain't polite either.

[Updated on: Fri, 05 December 2008 18:19]

Report message to a moderator

Re: Need Help On Pl/sql ( Very very Challenging) [message #363329 is a reply to message #363327] Fri, 05 December 2008 18:26 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Mahesh,

I have used USER_TAB_COLUMNS and DBA_TAB_COLS views in few of my procedures to retrieve the column names of a particular table. I thought they were reliable sources for these purposes.

OP needs to run the same set of queries against all the columns in different tables (That's what I understood). That's why I suggested to look for information in any of the above dictionary views. I thought it might be helpful.

My apologies if I was wrong there and misled OP.

Regards,
Jo

[Updated on: Fri, 05 December 2008 18:29]

Report message to a moderator

Re: Need Help On Pl/sql ( Very very Challenging) [message #363331 is a reply to message #363329] Fri, 05 December 2008 18:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Jo,
You have not said anything wrong or have been misleading.
>>to retrieve the column names of a particular table
I was not even talking about that.

Actually, I said dictionary views in general, which was a bad generalization.
The count of records, number of distinct records and number of nulls (which seems to be the matter of interest for OP) are also computed along with statistics/histogram collection.
These numbers are not exactly reliable (or atleast it used to be not reliable) for reasons already mentioned.

[Updated on: Fri, 05 December 2008 18:41]

Report message to a moderator

Re: Need Help On Pl/sql ( Very very Challenging) [message #363352 is a reply to message #363317] Sat, 06 December 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to know weather is it possible to Run the same queries for all the 50 columns at the same time

Yes it is.

Regards
Michel
Re: Need Help On Pl/sql ( Very very Challenging) [message #364166 is a reply to message #363352] Sun, 07 December 2008 13:37 Go to previous messageGo to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
Oh! really

If so, How it goes

Any loop kind of program....

and you post if you have any idea;


Thanks
Re: Need Help On Pl/sql ( Very very Challenging) [message #364262 is a reply to message #364166] Sun, 07 December 2008 13:44 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 05 December 2008 22:17
...
So post a test case: create table and insert statements along with the result you want with these data.
...
Regards
Michel

Previous Topic: Object v Record
Next Topic: Triggerzzzz (like a tiger)
Goto Forum:
  


Current Time: Thu Mar 28 11:19:26 CDT 2024