How to access table data through a variable [message #386700] |
Mon, 16 February 2009 14:36  |
sangram_ocp
Messages: 12 Registered: February 2009 Location: Mumbai
|
Junior Member |
|
|
Hi,
In our database tables are not periodically analyzed so always getting a wrong value when i am trying to
SELECT NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME='XYZ';
To overcome this I am trying to write a plsql block that will count the no. of rows of tables.
I have used a cursor to hold all table names.
I will appreciate if anyone will help me.
Any one can execute this code for their database;
I am facing the below error with my code.
--------------------------------------------
select count(*) from (cur.table_name);
*
ERROR at line 23:
ORA-06550: line 23, column 28:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 23, column 1:
PL/SQL: SQL Statement ignored
---------------------------------------------
MY cODE:~
==========
SET SERVEROUTPUT ON size 1000000
DECLARE
v_tbl_count NUMBER(4);
v_show_tbl varchar2(30);
CURSOR sangram_row_cur IS
SELECT table_name FROM all_tables;
cur sangram_row_cur%rowtype;
BEGIN
OPEN sangram_row_cur;
v_cursor_row_count := sangram_row_cur%ROWCOUNT;
SELECT count(*) INTO v_tbl_count FROM ALL_TABLES;
DBMS_OUTPUT.PUT_LINE('All Tables Available in ALL_TABLES is : '||v_tbl_count);
LOOP
FETCH sangram_row_cur INTO cur;
EXIT WHEN sangram_row_cur%NOTFOUND;
select count(*) from (cur.table_name);
END LOOP;
CLOSE sangram_row_cur;
END;
~Sangram Keshari (OCP)
mobile: 09920672073
|
|
|
|
|
|
|
|
|
|
|
Re: How to access table data through a variable [message #386727 is a reply to message #386700] |
Mon, 16 February 2009 19:31   |
sangram_ocp
Messages: 12 Registered: February 2009 Location: Mumbai
|
Junior Member |
|
|
Don't be extra SMART.
I better know what this query is going to do.
I doubt your SKILLs.
You are advising me to TRUNCATE all my tables to counting it's rows.
(Re: How to access table data through a variable [message #386726 is a reply to message #386700 ] Tue, 17 February 2009 06:51
BlackSwan
Messages: 468
Registered: January 2009 Senior Member add to buddy list
ignore all messages by this user
below will solve your problem when invoked via sqlplus
spool rerun.sql
SELECT 'TRUNCATE TABLE '|| TABLE_NAME || ';' FROM USER_TABLES;
SPOOL OFF
@rerun.sql
[Updated on: Tue, 17 February 2009 06:51]
)
WOW! What a solution...
If you are unable to answar then feel it.
Dont try to be a BLOGGING expert.
MIND IT.
[Updated on: Mon, 16 February 2009 19:33] Report message to a moderator
|
|
|
|
Re: How to access table data through a variable [message #386729 is a reply to message #386700] |
Mon, 16 February 2009 19:52   |
sangram_ocp
Messages: 12 Registered: February 2009 Location: Mumbai
|
Junior Member |
|
|
I KNOW THIS CONCEPT IS THERE IN
ORACLE 9I SQL (ORCALE BOOK) APPENDIX D SLIDE(6-9).
Ok. It will print the counts how come you know which count is from which table.
I dont want to make a script through MS-Excel.
I know I am good on that.
But I want the o/p in one shot and i.e with help of plsql.
|
|
|
|
Re: How to access table data through a variable [message #386823 is a reply to message #386731] |
Tue, 17 February 2009 02:14   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
@BlackSwan has given you a perfectly good answer with this line:Of course if you'd catch a clue, you could replace "TRUNCATE"
with "SELECT COUNT(*)" & you might have your answer.
He's shown you how to generate a series of select statements that will return you the data that you want.
If you'd rather do it in pl/sql, just take the select statement ,make it into a cursor and loop through it, dynamically executing each of the queries that it returns.
|
|
|
Re: How to access table data through a variable [message #386831 is a reply to message #386700] |
Tue, 17 February 2009 02:35   |
sukhijank
Messages: 5 Registered: February 2009
|
Junior Member |
|
|
Hi,
You can use:
DECLARE
v_tbl_count NUMBER (4);
v_row_count NUMBER (10);
v_table_name VARCHAR2 (30);
CURSOR sangram_row_cur IS
SELECT table_name
FROM user_tables;
BEGIN
SELECT COUNT (*)
INTO v_tbl_count
FROM user_tables;
DBMS_OUTPUT.put_line ('All Tables Available in USER_TABLES is : ' || v_tbl_count);
FOR cur IN sangram_row_cur LOOP
EXECUTE IMMEDIATE 'SELECT COUNT (*) FROM ' || cur.table_name
INTO v_row_count;
DBMS_OUTPUT.put_line ('Number of rows in table ' || cur.table_name || ': ' || v_row_count);
END LOOP;
END;
|
|
|
|