Hi Experts,
I have simplified the sample data as shown below for my case. We are storing some value in column c1 e.g. A, B, C etc. and for each of these we have stored a sql in c2 column. I have to show the c1 and the output of query in c2 as output. Also, when we see $ symbol in the c2 query that means we have to refer the output of the previous query to run the query with $ sign. We can use oracle standard scott schema for the sql in c2.
create table x (c1 varchar2(10), c2 varchar2(500), c3 number );
INSERT INTO "X" (C1, C2, c3) VALUES ('A', 'select empno from emp where ename = ''KING''', 10)
INSERT INTO "X" (C1, C2, c3) VALUES ('A', 'select deptno from emp where empno = $empno$', 20)
INSERT INTO "X" (C1, C2, c3) VALUES ('C', 'select hiredate from emp where ename = ''KING''', 10)
desired output -
A, 7839, 10 --→ (empno of king in c2)
A, 10, 20 -→ (dept no of king in c2 by referring the c2 of seq 10 for empno of king)
C, 17-11-1981, 10 (hiredate of king in c2)