use list of values as table in a select query in Oracle

Question

I have a requirement to pass a list of employee details (EmpNo, Dept) that is not in a DB table (get from another system as a JSON array). I have to use them in a select query as a table with an inner join. Is this possible in Oracle 11g? If so pls explain.

Please note that I do not have any privilege to create a function or procedure.

ex:

Emp Details :

{"EmpNo":"1", "Dept":1},{"EmpNo":"2", "Dept":1},{"EmpNo":"3", "Dept":2},{"EmpNo":"4", "Dept":2}

SQL:

SELECT * FROM attendance att

INNER JOIN (Emp Details) emp

ON att.EmpNo = emp.EmpNo

WHERE emp.Dept = 1

I found out that it can be done in SQL server as follows, but no luck with oracle

SELECT *  FROM

(VALUES (1,2) , (3,4)

) t1 (c1, c2)

Thanks in advance!

Answer 1

Here is code may help you.

SELECT *  FROM
(

(SELECT 1 AS EmpNo,2 AS Dept FROM DUAL)
UNION 
(SELECT 3 AS EmpNo,4 AS Dept FROM DUAL)

) t1

Answer 2

You can use common table expression with JSON_QUERY() and json_table() as below:

Schema and insert statements:

 create table attendance (EmpNo int,attn_date date);

 insert into attendance values(2,DATE '2021-09-02');

 insert into attendance values(3,DATE '2021-09-03');

Query:

 with emp_json  as  
 ( SELECT JSON_QUERY('[{"EmpNo":"1", "Dept":1},{"EmpNo":"2", "Dept":1},{"EmpNo":"3", "Dept":2},{"EmpNo":"4", "Dept":2}]', '$' ) AS EmpDetails
   FROM DUAL
    
 )   
 SELECT  emp.EmpNo , emp.Dept
 FROM  json_table( (select EmpDetails from emp_json) , '$[*]'  
                 COLUMNS ( EmpNo PATH '$.EmpNo'  
                         , Dept PATH '$.Dept'  
                         )   
                )emp
       inner join attendance att  
       ON att.EmpNo = emp.EmpNo

Output:

EMPNO	DEPT
2	1
3	2

db<>fiddle here

For Oracle 11g you can use string manipulation to get your desired result.

Schema and insert statements:

 create table attendance (EmpNo int,attn_date date);

 insert into attendance values(2,DATE '2021-09-02');
 insert into attendance values(3,DATE '2021-09-03');

Query:

 with cte (emp_details) as
 (
    select regexp_substr(replace(replace(replace('[{"EmpNo":"1","Dept":1},{"EmpNo":"2","Dept":1},{"EmpNo":"3","Dept":2},{"EmpNo":"4","Dept":2}]','},{',';'),'[{',''),'}]','')  ,'[^;]+', 1, level) 
    from dual 
    connect BY regexp_substr(replace(replace(replace('[{"EmpNo":"1","Dept":1},{"EmpNo":"2","Dept":1},{"EmpNo":"3","Dept":2},{"EmpNo":"4","Dept":2}]','},{',';'),'[{',''),'}]','')  , '[^;]+', 1, level) 
    is not null
 )
 ,
 cte2 as
 (
   select regexp_replace(regexp_substr(emp_details, '[^,]+', 1, 1),'[^0-9]','') as EmpNo, 
          regexp_replace(regexp_substr(emp_details, '[^,]+', 1, 2),'[^0-9]','') as Dept
   from cte
 )
 select emp.EmpNo,emp.Dept
  from cte2 emp
  inner join attendance att  
        ON att.EmpNo = emp.EmpNo

Output:

EMPNO	DEPT
2	1
3	2

db<>fiddle here