Home » SQL & PL/SQL » SQL & PL/SQL » How to access table data through a variable (Oracle DBA)
How to access table data through a variable [message #386700] Mon, 16 February 2009 14:36 Go to next message
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 #386701 is a reply to message #386700] Mon, 16 February 2009 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for:
- dynamic SQL / execute immediate
- count table rows
...

This has been asked and answered many times.
Read OraFAQ Forum Guide and follow it for your next posts.

Regards
Michel

[Updated on: Mon, 16 February 2009 14:41]

Report message to a moderator

Re: How to access table data through a variable [message #386715 is a reply to message #386700] Mon, 16 February 2009 18:31 Go to previous messageGo to next message
sangram_ocp
Messages: 12
Registered: February 2009
Location: Mumbai
Junior Member
pls help me...

Sad
Re: How to access table data through a variable [message #386717 is a reply to message #386700] Mon, 16 February 2009 18:35 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>To overcome this I am trying to write a plsql block that will count the no. of rows of tables.
Alternatively you could run DBMS_STATS to collect current row counts.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Is GOOGLE broken for you?
Re: How to access table data through a variable [message #386721 is a reply to message #386700] Mon, 16 February 2009 19:01 Go to previous messageGo to next message
sangram_ocp
Messages: 12
Registered: February 2009
Location: Mumbai
Junior Member
As I am working in production support team. We dont have dba privilege.So I cann't run DBMS packages. Even to analyze tables we have promote script and that adds extra cost to project.

So I am trying to code something that will run with out a dba privs.

[Updated on: Mon, 16 February 2009 19:02]

Report message to a moderator

Re: How to access table data through a variable [message #386722 is a reply to message #386700] Mon, 16 February 2009 19:04 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
write SQL to write the desired SQL
Re: How to access table data through a variable [message #386724 is a reply to message #386722] Mon, 16 February 2009 19:08 Go to previous messageGo to next message
sangram_ocp
Messages: 12
Registered: February 2009
Location: Mumbai
Junior Member
Do u know the ans or not ?
Re: How to access table data through a variable [message #386725 is a reply to message #386700] Mon, 16 February 2009 19:12 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.


>Do u know the ans or not ?
People don't feel encouraged when they have to decipher some gobbledygook.

Abbreviations like "U", "Ur", "U r", "Plz", "prb", "qry" especially tend to get on people's nerves.

IM speak is not appreciated:
It is hard to read.
It is unprofessional.
It doesn't show much respect towards your fellow forum members.

If you don't take time to write in proper English, how can you expect that others would answer properly?

Re: How to access table data through a variable [message #386726 is a reply to message #386700] Mon, 16 February 2009 19:21 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
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: Mon, 16 February 2009 19:21]

Report message to a moderator

Re: How to access table data through a variable [message #386727 is a reply to message #386700] Mon, 16 February 2009 19:31 Go to previous messageGo to next message
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 #386728 is a reply to message #386700] Mon, 16 February 2009 19:40 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>You are advising me to TRUNCATE all my tables to counting it's rows.
Then you will know all the rows counts = 0.

Of course if you'd catch a clue, you could replace "TRUNCATE"
with "SELECT COUNT(*)" & you might have your answer.
Re: How to access table data through a variable [message #386729 is a reply to message #386700] Mon, 16 February 2009 19:52 Go to previous messageGo to next message
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 #386731 is a reply to message #386700] Mon, 16 February 2009 19:59 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Again you refuse to comply with Posting Guidelines (o/p?)
>But I want the o/p in one shot and i.e with help of plsql.

Asked & answered within this thread
How to access table data through a variable [message #386701 is a reply to message #386700 ]

Search for:
- dynamic SQL / execute immediate
- count table rows

This REALLY, REALLY is a FAQ with multiple posts on this forum.

So either Read The Fine Manual (RTFM) or SEARCH!
Re: How to access table data through a variable [message #386823 is a reply to message #386731] Tue, 17 February 2009 02:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;

Re: How to access table data through a variable [message #386838 is a reply to message #386831] Tue, 17 February 2009 02:40 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@sukhijank

Please read OraFAQ Forum Guide, especially "How to format your post?" section but other ones too.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Problem in formatting Date
Next Topic: Pl/sql for Parallel processing
Goto Forum:
  


Current Time: Tue Dec 06 06:30:25 CST 2016

Total time taken to generate the page: 0.13653 seconds