Solved

Oracle function to count rows

Posted on 2008-06-26
11
769 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 500 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.

734 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