How to use a WHERE clause with Oracle JSON_OBJECT?

Question

I am trying to produce some oracle records snapshots using JSON_OBJECT but, surprisingly it seems that I cannot use a variable in the clause statement?

CREATE TABLE SAMPLE(
  person_id NUMBER,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL,
  PRIMARY KEY(person_id)
);

INSERT INTO SAMPLE (person_id, first_name, last_name)
  VALUES (1, 'John', 'Doe');
INSERT INTO SAMPLE (person_id, first_name, last_name)
  VALUES (2, 'Mary', 'Doe');

SET SERVEROUTPUT ON
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;

Error report:
ORA-00904: "NAME": invalid identifier
ORA-06512: at line 5

What database version are you using? It appears that JSON_OBJECT(*) was introduced in Oracle 19 so if you are using a version prior to that then you will have to list every key. — MT0
– MT0, Commented May 27, 2021 at 21:57
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production — pabloelustondo
– pabloelustondo, Commented May 27, 2021 at 22:04
This looks to be a bug. The query works if you use a text literal but not with a PL/SQL variable. Trying WHERE first_name = (SELECT name FROM DUAL); gives an internal error. — MT0
– MT0, Commented May 27, 2021 at 22:25

pabloelustondo · Accepted Answer · 2021-05-27 22:01:33Z

1

For now I found one, sad, workaround... replacing the wildcard "*" for the list of columns.

This works:

-- WORKs!
SET SERVEROUTPUT ON
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;

answered May 27, 2021 at 22:01

pabloelustondo

2,2163 gold badges20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use a WHERE clause with Oracle JSON_OBJECT?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related