Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle function to count rows

Posted on 2008-06-26
11
Medium Priority
?
774 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 21877258
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
ID: 21877272
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 2000 total points
ID: 21877295
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 10

Expert Comment

by:dbmullen
ID: 21880443
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
ID: 21882969
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
 
LVL 22

Expert Comment

by:DrSQL
ID: 21883005
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
ID: 21883019
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
ID: 21883161
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
ID: 21883397
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
ID: 31471094
Solution was excellent.
0
 

Author Comment

by:TOWELLR
ID: 21883522
I will create additional question for the new problem.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

721 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