Return items not found in database in Oracle

oracle

I've been given a list of employee names (several hundred), and I need to look them up, and return their employee number.

However, the names given are not necessarily exactly as they appear in the database. I therefore also need to know which ones it does not find, so we can do some further manual research to locate them and find out their employee id – but I cannot figure out how to also return the employees not found.

Example:

select name, emplid
from employees
where name in ('Smith,John','Brown,Jill','Doe,Jane')

If one of the names is 'Doe,Jane April') in the db, then I don't find her.

I am using Oracle, and I do not have the access to create tables. Is there any way to do this?

Best Answer

You can use this approach as it demonstrated in this SQL fiddle:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table cust (custname varchar2(20), custid int);
insert into cust values('Andrew', 1);
insert into cust values('John', 2);
insert into cust values('Mary', 3);

Query 1:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
)
select n.search, NVL(c.custid, 0) custid
from names n left join cust c 
on n.search = c.custname

Results:

| SEARCH | CUSTID |
|--------|--------|
| Andrew |      1 |
|   Mary |      3 |
|   Mark |      0 |

Query 2:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
union all
select 'Jo' AS search from dual
)
select n.search, NVL(c.custid, 0) custid,
CASE WHEN c.custid IS NULL THEN 'not found'
 WHEN c.custname = n.search THEN 'exact match'
 ELSE 'partial match' END AS result
from names n left join cust c 
on c.custname LIKE n.search || '%'

Results:

| SEARCH | CUSTID |        RESULT |
|--------|--------|---------------|
| Andrew |      1 |   exact match |
|   Mark |      0 |     not found |
|   Mary |      3 |   exact match |
|     Jo |      2 | partial match |

As you can see, you need to put the search values into the cte called names, and then left join it with your table. Using this approach will also allow you to use LIKE operator with a 'result' column, as demonstrated by the second query.

BTW, read this article on how to create a UDF that will convert the IN-list to a table

Related Solutions

Encountering exception ORA-01555

Gaius is right, use DBA_TABLES for NUM_ROWS and DBA_SEGMENTS for size:

select owner,table_name,num_rows,last_analyzed from dba_tables;

The num_rows count is as of LAST_ANALYZED date, which should be close enough even without running DBMS_STATS.

For sizing:

select owner,segment_type,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_type;

or (depending what level of details you need):

select owner,segment_name,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_name;

For LOBs you'll need to join it to DBA_LOBS, for indexes to DBA_INDEXES, for tables to DBA_TABLES. A lot will depend on your specific requirement. Since you mentioned you are trying to figure out used space in tablespace it might be as simple as:

select sum(bytes)/1024/1024 size_mb
from dba_segments
where tablespace_name='YOUR_TBLSP_NAME';

Your solution would really work only with small DB as it would not be feasible to read all data on large DB.

I am hesitant to post how to address ORA-1555 as in this case this it NOT your primary issue but just for completeness - you are on 10g using automatic undo management so your DBA would have to increase undo_retention in your database (the ixora link is relevant to a DB without auto undo management).

Can Oracle Text query return the terms found

You might want to look into the MARKUP or HIGHLIGHT functions of the CTX_DOC package. An example of how MARKUP can be used for your query (this assumes your index is called idx_news and you have an ID column):

declare
  MARKLOB   clob;
  MARK1     number;
  MARK2     number;

  cursor NEWS_SEARCH is
    select * from NEWS;
begin
  for REC in NEWS_SEARCH loop
    CTX_DOC.MARKUP('idx_news', to_char(REC.ID), 'people OR country', MARKLOB);
    MARK1 := instr(MARKLOB, '<<<');
    MARK2 := instr(MARKLOB, '>>>');

    if MARK1 + MARK2 <> 0 then
      dbms_output.
       PUT_LINE(
        REC.ID || ',' || substr(MARKLOB, MARK1 + 3, MARK2 - MARK1 - 3));
    end if;
  end loop;
end;
/

What it does is mark up your text (surround the found items with <<<>>>) and outputs that new text to a clob which is then searched for the markup. All text that is found in your query will be marked up but my PL/SQL will only display the first result; it can be adapted to find them all and display only the unique ones. This is a simple example and perhaps can become too complicated for your original query but it's here to point the way.

Alternatively, a simple SELECT may suffice if all you have is an OR in your text query:

select decode(sign(SCORE(1))
             ,1, decode(sign(SCORE(2)), 1, 'both', 'people')
             ,'country')
         as WHICH
      ,TITLE
  from NEWS A
 where CONTAINS(TITLE, 'people', 1) > 0 or CONTAINS(TITLE, 'country', 2) > 0;

This checks the score for individual searches on 'people' and 'country' and works out which ones were positive, returning 'both' if both search terms were found.

Best Answer

Related Solutions

Encountering exception ORA-01555

Can Oracle Text query return the terms found

Related Question