I know I didn't make it clear in the question title. Let me explain.
Say I have a Table SOURCE_TABLE
with 1 column that looks like this:
Filter
------------------|
Name='John'
Surname = 'Smith'
Age = '25'
And I want to use this table as a filter. Like below:
SELECT * FROM TARGET_TABLE WHERE (SELECT FILTER FROM SOURCE_TABLE)
I heard maybe evaluate function could help me but to be honest I couldn't understand how.
Do you know any method that I can use a column as my filter source like above?
Edit1:
DECLARE
my_filter VARCHAR2(100);
my_query VARCHAR2(500);
BEGIN
my_query := 'SELECT FILTER FROM SOURCE_TABLE WHERE ROWNUM=1';
EXECUTE IMMEDIATE my_query INTO my_filter;
EXECUTE IMMEDIATE 'SELECT * FROM TARGET_TABLE WHERE '|| my_filter;
END;
@Sujitmohanty30 I have come up with the above after learning EXECUTE IMMEDIATE
. But there is a problem I stumble upon. This needs to be dynamic regarding to the end result and i want to see the result of the select query at the end.
Let's say we have these 2 tables:
create table TARGET_TABLE(name varchar2(30), surname varchar2(30));
insert into TARGET_TABLE(name,surname) values ('John', 'Doe');
insert into TARGET_TABLE(name,surname) values ('Ann', 'Smith');
insert into TARGET_TABLE(name,surname) values ('Steven', 'Feuerstein');
insert into TARGET_TABLE(name,surname) values ('Steven', 'King');
commit;
create table SOURCE_TABLE(filter) as
select q'[name='John']' from dual union all
select q'[name='Ann']' from dual union all
select q'[name='Steven' and surname='King']' from dual union all
select q'[surname='Feuerstein']' from dual;
Then you can use xmltable and dbms_xmlgen.getXMLtype to get such rows dynamically:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=72abdf18b149cf30882cb4e1736c9c33
select *
from SOURCE_TABLE
, xmltable(
'/ROWSET/ROW'
passing dbms_xmlgen.getXMLtype('select * from TARGET_TABLE where '|| SOURCE_TABLE.filter)
columns
name varchar2(30) path 'NAME',
surname varchar2(30) path 'SURNAME'
) x;
Results:
FILTER NAME SURNAME
-------------------------------- ------------------------------ ------------------------------
name='John' John Doe
name='Ann' Ann Smith
name='Steven' and surname='King' Steven King
surname='Feuerstein' Steven Feuerstein