A reader, October 19, 2001 - 10:57 am UTC
hi tom
i have midified get_rows
FUNCTION GET_ROWS ( p_tname in varchar2,p_own in varchar2 ) return number
as
l_columnValue number default NULL;
begin
execute immediate
'select count(*)
from ' ||p_own||'.'||p_tname
INTO l_columnValue;
return l_columnValue;
end;
i only want to get row count if count(*) > 0
and i am getting error
1 select * from (SELECT user, table_name,
2 get_rows( table_name,'SIEBEL') cnt
3 from all_tables WHERE OWNER = 'SIEBEL')
4* WHERE cnt > 0
SQL> /
get_rows( table_name,'SIEBEL') cnt
*
ERROR at line 2:
ORA-00942: table or view does not exist
ORA-06512: at "SBLINT.GET_ROWS", line 5
ORA-06512: at line 1
thanks
October 19, 2001 - 12:35 pm UTC
Well, its obvious -- Its trying to query some SIEBEL CRM tables and my code refuses to touch their stuff (thats a joke ;)
Anyway, see
</code>
http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>
you must have access via a role. Try adding:
function get_rows .... return number
AUTHID CURRENT_USER
as
....
to let it run with invokers rights and roles. That should solve it.
Get Rows
Ping Cui, October 19, 2001 - 11:24 am UTC
Hi Tom,
Love your column. Just one comment. In order to be
able to use
select table_name, num_rows from user_tables;
to get the row numbers. You may have to perform table
'analyze' operation against all tables. For example,
select 'analyze table ' || table_name || ' compute statistics;' from user_tables;
Run 'analyze' statements
select table_name,num_rows from user_tables;
Ping
October 19, 2001 - 12:36 pm UTC
Well, I'd use dbms_stats to analyze but .... yes, that is true, you need to analyze the tables.
I tried to infer that with my opening statement:
"Well, if you are doing things correctly, you are using the cost based optimizer
and statistics are generally up to date. ...."
Printing
Citrus, October 19, 2001 - 6:39 pm UTC
Hi Tom,
I'm one of your fans and keep collecting your replies. While printing the questions, everything prints fine, but your Followup answers are truncated from the right, so I need to take a pen and complete the missed info. Could you please make it printable on A4.
Thanking you
October 19, 2001 - 6:50 pm UTC
I'll see what they can do with regards to that.
I need to add a where clause to the function get_rows()
Sujit, May 18, 2006 - 12:39 pm UTC
I am trying to add a where condition to the execute immediate statement in your function
execute immediate 'select count(*) from ' INTO l_rowcount || FROM l_table_name WHERE || 'SUBSTR(' || l_table_name, INSTR(l_table_name,'_', 1, 1)+1) || '_PK' > 0;
Having trouble with parsing. What am I doing wrong
May 19, 2006 - 10:03 am UTC
ugh, you GOTTA USE BINDS
and the into is "last", not in the middle
execute immediate '
select count(*)
from ' || l_table_name || '
where substr( :x, instr(:y,''_'',1,1)+1 ......'
using l_table_name, l_table_name INTO l_rowcount;
oh, i just stopped typing there cause the rest of the where clause makes no sense at all.
where substr(x,instr(x,_',1,)+1)_PK > 0
that makes no sense, I don't know what your query is meant to do.
Good
A reader, September 12, 2006 - 11:05 am UTC
Table name and its rows
Hao Zhang, October 20, 2006 - 5:16 pm UTC
it is very convenient to get answer from you.
thanks
raheem, September 21, 2011 - 10:42 am UTC
hi,
you create a function with 2 IN arguments but when your run this sql stmt
select user, table_name,
get_rows( user||'.'||table_name) cnt
from user_tables
you are only passing 1 argument, i think it's wrong.
September 21, 2011 - 12:29 pm UTC
read closer, MY get_rows takes one input.
someone else posted a MODIFIED version of it that takes two.
Done with a single SQL
Marcus, September 22, 2011 - 12:50 am UTC
Thanks
ramesh.maldakal, July 30, 2013 - 12:25 pm UTC
Thanks boos,nice solution.It is very useful.
A reader, October 10, 2016 - 10:25 am UTC
How to Find a length of each row in a single table?
October 11, 2016 - 12:36 am UTC
Another way to programatically count rows
Hari, November 24, 2020 - 2:29 pm UTC
Hi
I am using below for counting rows, but it's throwing table or view does not exist at line 7. Can you please guide me a better way without creation of fucntion or stored procedure on DB?
declare
v_count integer;
begin
for r in (select table_name,owner from all_tables at WHERE table_name not like'%$%' )
loop
execute immediate 'select count(*) from ' || r.TABLE_NAME into v_count;
INSERT INTO ROWCOUNT_TBL(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
VALUES (r.table_name,r.owner,v_count,SYSDATE);
end loop;
end;
Regards
Hari
November 24, 2020 - 4:40 pm UTC
This can access tables in other schemas, but
execute immediate 'select count(*) from ' || r.TABLE_NAME into v_count;
doesn't include the schema! So it looks for all tables in the current user. You need to add this to the query.