Solved

Oracle function to count rows

Posted on 2008-06-26
11
755 Views
Last Modified: 2013-12-07
I need to develop an Oracle function that

1. Gets the owner and table_name
2. Counts the number of rows in this table from a remote database.

The dblink will be created
0
Comment
Question by:TOWELLR
11 Comments
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
create or replace function HowManyRows(owner varchar2, tablename varchar2) return number is
    NumRows number;
begin
    execute immediate 'select count(*) from 'owner' || '.' || tablename || '@dblink'
            into numrows;
    return (numrows);
end;
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility
Try:

accept owner prompt Table Owner:

accept tabnm prompt Table  Name:
 

SELECT COUNT(*) from &&owner..&&tabnm.@db_link;

Open in new window

0
 
LVL 22

Accepted Solution

by:
DrSQL earned 500 total points
Comment Utility
Sorry, I garbled that a little:

create or replace function HowManyRows(owner varchar2, tablename varchar2) return number is
    NumRows number;
begin
    execute immediate 'select count(*) from ' || owner || '.' || tablename || '@dblink'
            into numrows;
    return (numrows);
end;
0
 
LVL 10

Expert Comment

by:dbmullen
Comment Utility
how accurate do you need the count to be?  assuming you have up-todate stats:

select owner, table_name, NUM_ROWS
from dba_tables@dblink
;

0
 

Author Comment

by:TOWELLR
Comment Utility
DrSQL,

Thanks the function works great I need the exact counts to do some comparison checks.

I am trying to do the same with a status lookup but it does not seem to work it always finds nothing what do you think?

CREATE OR REPLACE function SYSTEM.statuslocal(P_owner varchar2, P_objectname varchar2) return varchar2 is
    StatVal varchar2(15);
begin
    execute immediate 'select status from dba_objects where owner=' ||P_owner|| ' and objectname=' ||P_object_name
            into StatVal;
    return (StatVal);
    EXCEPTION
      WHEN OTHERS THEN
      RETURN('xxx');
end;
/
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
TOWELLR,
    Glad it worked.  Usually, we try to keep it to one problem per question.  But this is a easy follow-up.  When you use execute immediate it is taking the literal value and executing it.  It doesn't know about variables that were use to build the string.  So value of your "P_OWNER" is going into the select without any quotes.  try this:

CREATE OR REPLACE function SYSTEM.statuslocal(P_owner varchar2, P_objectname varchar2) return varchar2 is
    StatVal varchar2(15);
begin
    execute immediate 'select status from dba_objects where owner=''' ||P_owner|| ''' and objectname=''' ||P_object_name || ''''
            into StatVal;
    return (StatVal);
    EXCEPTION
      WHEN OTHERS THEN
      RETURN('xxx');
end;
/

Good luck!
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
Also, remember that it's a case-sensitive search.  If you are concerned that the function might be called with lower or mixed case, try:

CREATE OR REPLACE function SYSTEM.statuslocal(P_owner varchar2, P_objectname varchar2) return varchar2 is
    StatVal varchar2(15);
begin
    execute immediate 'select status from dba_objects where owner=upper(''' ||P_owner|| ''') and objectname=upper(''' ||P_object_name || ''')'
            into StatVal;
    return (StatVal);
    EXCEPTION
      WHEN OTHERS THEN
      RETURN('xxx');
end;
/

Good luck!
0
 

Author Comment

by:TOWELLR
Comment Utility
DrSQL,

This select works:
select status from dba_objects
where owner='BLADELOGIC' and object_name='COMPLIANCE_RULE_ID_GEN';

STATUS
-------
VALID

This function:

CREATE OR REPLACE function SYSTEM.statuslocal(P_owner varchar2, P_objectname varchar2) return varchar2 is
    StatVal varchar2(15);
begin
    execute immediate 'select status from dba_objects where owner=''' ||P_owner|| ''' and object_name=''' ||P_objectname || ''''
            into StatVal;
    return (StatVal);
    EXCEPTION
      WHEN OTHERS THEN
      RETURN('xxx');
end;
/

Compiles but never finds the object status

SQL> select statuslocal('BLADELOGIC','COMPLIANCE_RULE_ID_GEN') from dual;

STATUSLOCAL('BLADELOGIC','COMPLIANCE_RULE_ID_GEN')
--------------------------------------------------------------------------
xxx

1 row selected.

0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
TOWELLR,
     Are you logged on as SYSTEM in both cases?  Also, since you have a generic exception handler, it isn't possible to tell what the actual error is.  Try changing the "RETURN('xxx')" to a "RAISE" or remove the exception handler while you try to find out what's wrong.  If that doesn't help you pinpoint the erro, open another question and we'll get some more attention on the problem.

Good luck!
0
 

Author Closing Comment

by:TOWELLR
Comment Utility
Solution was excellent.
0
 

Author Comment

by:TOWELLR
Comment Utility
I will create additional question for the new problem.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now