Solved

PL/SQL for 2 looping structure

Posted on 2011-09-21
6
228 Views
Last Modified: 2012-05-12
I want to get the name of the tables belonging to a user and then find the record count count(*) for that table in a 2nd loop and insert table_name, count(*) into a static table
Thanks
0
Comment
Question by:Greens8301
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:Greens8301
ID: 36577112

For ueach user in (usr1,usr2,usr3 ...)
loop 1
Get the names of tables belonging to the user
loo 2
get the total number of records for each table that user owns

INSERT into a static table (table_name, recCNT)

I need a PL/SQL code to do this

Thanks
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36577538
You can do in in a single sql statement.

Take the following solution, add the table_name, an insert into table (select ...), change the view from user_tables to dba_users and add a: where username in ('A','B');
And you should be good to go.

http://www.experts-exchange.com/Database/Oracle/Q_25111045.html?#a26476979
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36580723
I remembered you need to remove the SUM call in that code.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Expert Comment

by:iaakov
ID: 36713243
try


insert into tab_resume (tab_name ,tab_count)
select table_name ,num_rows
from dba_tables
where owner = 'NAME_USER'
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36713473
Num_rows is an estimate based off statistics.  You need to be aware the number might be off.
0
 

Author Closing Comment

by:Greens8301
ID: 37033417
Thanks
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question