• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 780
  • Last Modified:

Oracle function to count rows

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
TOWELLR
Asked:
TOWELLR
1 Solution
 
DrSQLCommented:
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
 
MikeOM_DBACommented:
Try:

accept owner prompt Table Owner:
accept tabnm prompt Table  Name:
 
SELECT COUNT(*) from &&owner..&&tabnm.@db_link;

Open in new window

0
 
DrSQLCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
dbmullenCommented:
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
 
TOWELLRAuthor Commented:
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
 
DrSQLCommented:
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
 
DrSQLCommented:
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
 
TOWELLRAuthor Commented:
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
 
DrSQLCommented:
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
 
TOWELLRAuthor Commented:
Solution was excellent.
0
 
TOWELLRAuthor Commented:
I will create additional question for the new problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now