SQL & PL/SQL

Hi All,

Oracle version : 12C

I have a scenario where to replace and append the Alias name - AS COUNT for around 1300 Sqls which is kept in CLOB column.

All the SQLs currently in DB is not with an common alias as mentioned in the expected result.

Please help to provide a logic to update this Alias name in all SQLs.

create table test_tab(sqlno number,sql_txt clob);

insert into test_tab values(1,'select count(1) cnt from emp');

insert into test_tab values(2,'select count(deptid) from emp');

insert into test_tab values(3,'select count(empid) count from emp');

insert into test_tab values(4,'select nvl(sum(sal),0) cnt from (select deptid,sum(salary) sal from emp

                                     group by deptid

                                     having sum(salary) >100000)');

insert into test_tab values(5,'with temp(select deptid,sum(salary) sal from emp

                                     group by deptid

                                     having sum(salary) >100000 )

                           select count(t1.deptid)  

                           from emp e, temp t1

                           where e.deptid = t1.deptid');

commit;

select *

from test_tab;

| **SQLNO
** | **SQL_TXT
** |
| 1 |

| select count(1) cnt from emp |

|
| 2 |

| select count(deptid) from emp |

|
| 3 |

| select count(empid) count from emp |

|
| 4 |

| select nvl(sum(sal),0) cnt from (select deptid,sum(salary) sal from emp
group by deptid
having sum(salary) >100000) |

|
| 5 |

| with temp(select deptid,sum(salary) sal from emp
group by deptid
having sum(salary) >100000 )
select count(t1.deptid)
from emp e, temp t1
where e.deptid = t1.deptid |

|

Expected Output:-

Regards

Lazar