No question at this time
DBA Top 10
1 A. Kavsek 8900
2 B. Vroman 6600
3 M. Cadot 5000
4 T. Boles 4550
5 P. Wisse 4500
6 J. Schnackenberg 3700
7 . Lauri 1300
8 G. Lambregts 800
9 T. P 700
9 R. Pattyn 700
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48392
Total active users1527
Act. users last 24h5
Act. users last hour3
Registered user hits last week277
Registered user hits last month713
Go up

How to show non-existing record from an in clause
Next thread: oracle 11g r2 install ora-19587 error masege withe windows 7 64bit
Prev thread: Invalid header when restoring datafiles using RMAN

Message Score Author Date
Hi, Is there a trick somehow for the SQL below ...... BenBart BartBen Jun 25, 2019, 09:05
This works for a list with up to 32767 chars: ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts David Johnson Jun 25, 2019, 13:42
Thanks, I'll give your suggestion a go.... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts BenBart BartBen Jun 28, 2019, 11:33

Follow up by mail Click here


Subject: How to show non-existing record from an in clause
Author: BenBart BartBen, Philippines
Date: Jun 25, 2019, 09:05, 21 days ago
Os info: Linux/Solaris
Oracle info: Oracle11
Message: Hi,

Is there a trick somehow for the SQL below to show which one of the the username in the IN clause does not exist?

select username, account_status

from dba_users
where username in (
'DBSNMP',
'MICKEY',
'OUTLN',
'ANONYMOUS',
'PUBLIC',
'HR',
'MINNIE',
'SCOTT',
'TIGER', ... )


At the moment, I create a temporary table x and insert the list from the IN clause and then do a select from x and where username not in dba_users.

Can't think of other way to do it except having a script that select everything from dba_users and then put the IN list into a file, sorted and then diff :(

Just posting in case someone has a similar requirement before and has a better solution.

Thanks in advance.

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to show non-existing record from an in clause
Author: David Johnson, United States
Date: Jun 25, 2019, 13:42, 21 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: This works for a list with up to 32767 chars:


CREATE OR REPLACE TYPE tx_ as table of varchar2(4000char);

create or replace function split$
( list varchar2
, delim varchar2 := null
) return tx_ pipelined
authid definer
is results varchar2(4000char);
delim_l varchar2(15char ) := coalesce(delim,',');
i pls_integer ;
list_l varchar2(32767char) := list ;
begin
loop
i := instr( list_l, delim_l );
if i > 0 then
results := substr( list_l, 1, i-1 );
list_l := substr( list_l, i+length(delim_l) );
else
results := list_l;
list_l := null;
end if;
pipe row( results );
exit when i=0;
end loop;
return;
end split$;


select column_value username
from table(split$('DBSNMP,MICKEY,OUTLN,ANONYMOUS,PUBLIC,MINNIE,SCOTT,TIGER')) where column_value not in
(select username from dba_users);


USERNAME
--------
MICKEY
MINNIE
TIGER
PUBLIC

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: How to show non-existing record from an in clause
Author: BenBart BartBen, Philippines
Date: Jun 28, 2019, 11:33, 18 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Thanks, I'll give your suggestion a go.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here