Rather than this
....
This is not working somehow, it's giving me several errors
....How about a test case to reproduce your errors ?
Here is what we ran based on the data you provided to us.
drop table t purge;
create table t(x int, y clob check(y is json) );
insert into t(x,y) values(1235537096597307393, '[
{
"stepName":"CreateTransFromPain",
"timestamp":"2021-09-01T17:11:39.515Z",
"entityReference":{
"CustomerCreditTransferInitiation":{
"mapName":"inbound_pain",
"id":"1235537096597307393",
"timestamp":"2021-09-01T17:11:39.516Z"
}
},
"latency":3
},
{
"stepName":"TransactionEnrichment",
"timestamp":"2021-09-01T17:11:39.519Z",
"latency":0
},
{
"stepName":"ResolveOptyLebe",
"timestamp":"2021-09-01T17:11:39.521Z",
"latency":4
},
{
"stepName":"TransactionDuplicateValidation",
"timestamp":"2021-09-01T17:11:39.533Z",
"latency":1
},
{
"stepName":"RequestedExecutionDateTimeCheck",
"timestamp":"2021-09-01T17:11:39.537Z",
"latency":0
}
]');
commit;
demo@XEPDB1> select t2.*
2 from t , json_table( y, '$[*]'
3 columns(
4 stepName varchar2(60) path '$.stepName') ) t2
5 where x = 1235537096597307393
6 /
STEPNAME
------------------------------------------------------------
CreateTransFromPain
TransactionEnrichment
ResolveOptyLebe
TransactionDuplicateValidation
RequestedExecutionDateTimeCheck
demo@XEPDB1> select t2.*
2 from t , json_table( y, '$[*]'
3 columns(
4 stepName varchar2(60) path '$.stepName') ) t2
5 where x = 1235537096597307393
6 and t2.stepname ='CreateTransFromPain'
7 /
STEPNAME
------------------------------------------------------------
CreateTransFromPain
demo@XEPDB1>
dont see any errors as you describe - if that is still not working, you have to show us via a reproducible test case.
PS: Also dont forget to wrap your code with "code" tag while you post something here