create table sample (
person_id number primary key,
first_name varchar2(50) not null,
last_name varchar2(50) not null
)
Table created.
insert into sample values (1, 'John', 'Doe')
1 row(s) inserted.
insert into sample values (2, 'Mary', 'Doe')
1 row(s) inserted.
declare
name varchar2(50) := 'John';
jsonResult clob;
begin
select json_object (*) into jsonResult
from sample
where first_name = name;
dbms_output.put_line (jsonResult);
end;
ORA-00904: "NAME": invalid identifier ORA-06512: at line 5 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-00904
declare
name varchar2(50) := 'John';
jsonResult clob;
begin
execute immediate 'SELECT JSON_OBJECT(*) FROM SAMPLE WHERE first_name = :name'
into jsonResult using name;
end;
ORA-06550: line 7, column 42: PL/SQL: ORA-00600: internal error code, arguments: [qcsRslvLocalExpr0-null-bind-2], [1], [], [], [], [], [], [], [], [], [], []More Details: https://docs.oracle.com/error-help/db/ora-06550
declare
name varchar2(50) := 'John';
jsonResult clob;
begin
select json_object (person_id, first_name, last_name) into jsonResult
from sample
where first_name = name;
dbms_output.put_line (jsonResult);
end;
Statement processed.
{"PERSON_ID":1,"FIRST_NAME":"John","LAST_NAME":"Doe"}