При выборке в JSON_OBJECT` с переменной в WHERE клаузе ошибка: ORA-00904: invalid identifier

Question

Пытаюсь создать несколько срезов записей из таблицы, используя JSON_OBJECT. Но, кажется, что нельзя использовать переменную в WHERE клаузе.

Упрощённый воспроизводимый пример того, что делаю:

CREATE TABLE SAMPLE(
  person_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL
);
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');

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;
/

Получаю ошибку, что переменная name неизвестна:

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

Как можно исправить?

_{Свободный перевод вопроса How to use a where clause with Oracle JSON_OBJECT от участника @pabloelustondo}

Answer 1

Начиная с версии 19c синтаксис для JSON_OBJECT был упрощён и стало возможным использовать шаблон * в листе инициализации.

В данном случае, скорее всего, имеет место новый баг. Попытки обойти его пока не увенчались успехом (см. на LiveSQL 19.8).

Единственная пока возможность, не использовать шаблон * и указывать все колонки явно:

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; 
/

{"PERSON_ID":1,"FIRST_NAME":"John","LAST_NAME":"Doe"}

Answer 2

Версия oracle 19c. Мне помогло использование execute immediate. Т.е. пример автора можно переписать так:

  execute immediate 'SELECT JSON_OBJECT(*) FROM SAMPLE WHERE first_name = :name'
  into jsonResult using name;

В данном случае ошибки нет.

БЛОГ НА HUSL

При выборке в JSON_OBJECT` с переменной в WHERE клаузе ошибка: ORA-00904: invalid identifier

Ответы (2 шт):