How to use a WHERE clause with Oracle JSON_OBJECT?

user_1173878 0
Viewed 335

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

oracle19c plsql oracle sql
1 Answers

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;
user_11738780
Related