Solved

PL/SQL for 2 looping structure

Posted on 2011-09-21
6
226 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Field name with special character (Ñ) in Oracle 11 132
Oracle Distributed Transaction Lock Error ORA-01591 8 68
Loading flat file data in tables 2 58
error doing substr 3 35
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.

762 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