Having following query:
select table_name
from user_tables
where table_name in ('A','B','C','D','E','F');
Assuming only user_tables records B,C, and F exist, I want to retrieve the non-existing values A,D and E. This is a simple example, on real world the list can be huge.
A good way to generate fake rows is with a standard collection such as sys.odcivarchar2list:
select
tables_to_check.table_name,
case when user_tables.table_name is null then 'No' else 'Yes'end table_exists
from
(
select column_value table_name
from table(sys.odcivarchar2list('does not exist', 'TEST1'))
) tables_to_check
left join user_tables
on tables_to_check.table_name = user_tables.table_name
order by tables_to_check.table_name;
TABLE_NAME TABLE_EXISTS
---------- ------------
TEST1 Yes
does not exist No
if you have list of all those tables to be checked in Table1 then you can use NOT EXISTS clause
select name
from Table1 T1
where not exists ( select 1 from
user_tables U
where T1.name = U.table_name)
Only way is to use NOT EXISTS by converting the IN clause String into a Table of values.(CTE)
This is not a clean solution though. As The maximum length of IN clause expression is going to be 4000 only, including the commas..
WITH MY_STRING(str) AS
(
SELECT q'#'A','B','C','D','E','F'#' FROM DUAL
),
VALUES_TABLE AS
(
SELECT TRIM(BOTH '''' FROM REGEXP_SUBSTR(str,'[^,]+',1,level)) as table_name FROM MY_STRING
CONNECT BY LEVEL <= REGEXP_COUNT(str,',')
)
SELECT ME.* FROM VALUES_TABLE ME
WHERE NOT EXISTS
(SELECT 'X' FROM user_tables u
WHERE u.table_name = ME.table_name);
You can't. These values have to be entered into a temporary table at the least to do the desired operation. Also Oracle's IN clause list cannot be huge (i.e, not more than 1000 values).
Are you restricted to receiving those values as a comma delimited list?
then you can select all from table B
select * from tab1;
------------------
A
B
C
D
E
F
Create or replace procedure proc1 as
cursor c is select col1 from tab1;
r tab1.col1%type;
i number;
begin
open c;
loop
fetch c into r;
exit when c%notfound;
select count(tname) into i from tab where tname = r;
if i = 0 then
v_sql := 'insert into tab2 values ('''||r||''');
execute immediate v_sql;
commit;
end if;
end loop;
close c;
end proc1;
select * from tab2;
------------------
A
D
E
if this is not a one-off, then having this proc on hand will be handy.
