Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle function to count rows

Posted on 2008-06-26
11
Medium Priority
?
777 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
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

577 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