Проблема с запросом Oracle REST POST, возвращающим sys_refcursor

В настоящее время мы создаем прототип REST API, используя облачный уровень бесплатного пользования Oracle APEX. У меня возникла проблема с возвратом sys_refcursor в запросе Oracle REST POST. Вот подробности (я новичок в том, чтобы задавать здесь вопросы, поэтому я надеюсь, что форматирование удобочитаемо:

ТАБЛИЦА

 CREATE TABLE "WF_PROTOTYPE"."INGREDIENT_TYPES"     
(   "ID" NUMBER(10,0)  NOT NULL ENABLE,     
    "ACCOUNT_ID" NUMBER(10,0) NOT NULL ENABLE,
    "NAME"  VARCHAR2(100 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, 
    "CREATED_AT" TIMESTAMP (6),     "UPDATED_AT" TIMESTAMP (6), 
    "DELETED_AT" TIMESTAMP (6),     "CREATED_BY" NUMBER(10,0), 
    "UPDATED_BY" NUMBER(10,0),      "DELETED_BY" NUMBER(10,0), 
    "DESCRIPTION" VARCHAR2(1000 BYTE) COLLATE "USING_NLS_COMP",     
 CONSTRAINT "INGR_TYPE_PK" PRIMARY KEY ("ID"))

ТРИГГЕР

create or replace TRIGGER ingredient_types_id_TRIG BEFORE INSERT OR UPDATE ON ingredient_types
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.id IS NULL THEN
    SELECT  ingredient_types_id_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT NVL(max(id),0) INTO v_newVal FROM ingredient_types;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT ingredient_types_id_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.id := v_newVal;
  END IF;
END;

СОХРАНЕННАЯ ПРОЦЕДУРА

create or replace PROCEDURE ingredient_type_POST (
   p_account_id     IN  ingredient_types.account_id%TYPE,
   p_name           IN  ingredient_types.name%TYPE,
   p_description    in  ingredient_types.description%type,
   p_created_by     IN  ingredient_types.created_by%type,
   p_out_rec        OUT sys_refcursor
)
AS
    new_id ingredient_types.id%type;
BEGIN
   INSERT INTO ingredient_types (account_id, name, DESCRIPTION, created_at, created_by)
   VALUES (p_account_id, p_name, p_description, systimestamp, nvl(p_created_by,2))
   RETURN id INTO new_id;

   OPEN p_out_rec FOR
    SELECT id, account_id, name, description
    FROM   ingredient_types
    WHERE  ID = new_id;

EXCEPTION
   WHEN OTHERS
   THEN HTP.print(SQLERRM);
END ingredient_type_POST;

ОПРЕДЕЛЕНИЕ ЗАКАЗОВ

ORDS.DEFINE_HANDLER(
      p_module_name    => 'wf_api.rest',
      p_pattern        => 'accounts/:acct_id/ingredient_types',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => 'CREATE an Ingredient Type',
      p_source         => 
'begin
    INGREDIENT_TYPE_POST(
                p_account_id    => :acct_id, 
                p_name          => :name, 
                p_description   => :description,
                p_created_by    => :APP_USER, 
                p_rec           => :new_rec);
end;'
      );
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'wf_api.rest',
      p_pattern            => 'accounts/:acct_id/ingredient_types',
      p_method             => 'POST',
      p_name               => 'rec',
      p_bind_variable_name => 'new_rec',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'RESULTSET',
      p_access_method      => 'OUT',
      p_comments           => NULL);      

Когда я запускаю SP из РЕДАКТОРА рабочего листа SQL Developer, заполняя входные переменные, я получаю успешный результат (новая запись создана), и он возвращает ожидаемый набор результатов в параметре p_out_rec. Однако, когда я запускаю его в редакторе HANDLER, заполняя соответствующие переменные, я получаю следующее сообщение об ошибке:

Error starting at line : 1 in command -
begin
    INGREDIENT_TYPE_POST(
                p_account_id    => :acct_id, 
                p_name          => :name, 
                p_description   => :description,
                p_created_by    => :APP_USER, 
                p_rec           => :new_rec);
end;
Error report -
ORA-06550: line 2, column 5:
PLS-00306: wrong number or types of arguments in call to 'INGREDIENT_TYPE_POST'
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Я пытался подражать руководству ThatJeffSmith https://www.thatjeffsmith.com/archive/2017/03/parameters-and-binds-for-your-restful-services-resultsets/ как можно ближе, и я не вижу где моя вина лежит ... любые идеи очень ценятся !!!


person Coder-1    schedule 17.03.2021    source источник
comment
Ваша процедура была определена с помощью p_out_rec, но вы вызываете ее с помощью p_rec.   -  person Andrew Sayer    schedule 17.03.2021
comment
вы не указываете значение для столбца ID, и оно НЕ NULL, так что это не сработает... если только у вас не определен столбец последовательности/триггера или IDENTITY, который вы не показываете в своем примере кода   -  person thatjeffsmith    schedule 18.03.2021


Ответы (1)


Нашел пару проблем с вашим кодом.

  1. вы не указываете значение для столбца идентификатора таблицы
  2. ваш refcursor заполняется запросом, который в основном никогда не будет работать, вы проверяете идентификатор по значению ACCOUNT_ID
  3. вы имеете в виду p_out_rec как p_rec - спасибо, @andrew!

Вот как это может работать

введите здесь описание изображения

-- Generated by ORDS REST Data Services 20.4.1.r0131644
-- Schema: HR  Date: Wed Mar 17 05:24:17 2021 
--

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'HR',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'hr',
      p_auto_rest_auth      => FALSE);
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'wf_api.rest',
      p_base_path      => '/wf_api/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'stackoverflow question');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'wf_api.rest',
      p_pattern        => 'accounts/:acct_id/ingredient_types',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'wf_api.rest',
      p_pattern        => 'accounts/:acct_id/ingredient_types',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'begin
 INGREDIENT_TYPE_POST(
    P_ID             => :id,
    P_ACCOUNT_ID     => :acct_id,
    P_NAME           => :name,
    P_DESCRIPTION    => :description,
    P_CREATED_BY     => :APP_USER,
    P_OUT_REC        => :new_rec
  );
END;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'wf_api.rest',
      p_pattern            => 'accounts/:acct_id/ingredient_types',
      p_method             => 'POST',
      p_name               => 'rec',
      p_bind_variable_name => 'new_rec',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'RESULTSET',
      p_access_method      => 'OUT',
      p_comments           => NULL);

    
        
COMMIT;

END;

И процесс PL/SQL...

create or replace PROCEDURE ingredient_type_POST (
   p_id             IN  ingredient_types.id%TYPE,
   p_account_id     IN  ingredient_types.account_id%TYPE,
   p_name           IN  ingredient_types.name%TYPE,
   p_description    in  ingredient_types.description%type,
   p_created_by     IN  ingredient_types.created_by%type,
   p_out_rec        OUT sys_refcursor
)
AS
    new_id ingredient_types.id%type;
BEGIN
   INSERT INTO ingredient_types (id, account_id, name, DESCRIPTION, created_at, created_by)
   VALUES (p_id, p_account_id, p_name, p_description, systimestamp, nvl(p_created_by,2))
   RETURN id INTO new_id;

   OPEN p_out_rec FOR
    SELECT id, account_id, name, description
    FROM   ingredient_types
    WHERE  account_ID = p_account_id;

EXCEPTION
   WHEN OTHERS
   THEN HTP.print(SQLERRM);
END ingredient_type_POST;

И просьба -

curl --request POST \
  --url http://localhost:8080/ords/hr/wf_api/accounts/42/ingredient_types \
  --header 'Content-Type: application/json' \
  --data '{
    "id".         : 55,
    "name"        : "heyYou",
    "description" : "some words",
    "APP_USER"    : 44
}'
person thatjeffsmith    schedule 17.03.2021
comment
Спасибо за ваши ответы! Столбец ID заполняется последовательностью, поэтому я не хочу ее заполнять. . Account_id отличается. УЧЕТНАЯ ЗАПИСЬ может иметь много связанных INGREDIENT_TYPES, поэтому, когда я возвращаю запись, я не хочу возвращать все INGREDIENT_TYPES для учетной записи, я просто хочу вернуть информацию из строки JUST INSERTED. Хороший улов по моей проблеме с p_rec / p_out_rec. Позвольте мне взглянуть поближе. Кажется, вы получили его, возвращая набор результатов. - person Coder-1; 18.03.2021
comment
в следующий раз включите свой РЕАЛЬНЫЙ сценарий, тогда я смогу приблизиться к тому, чтобы помочь вам. в противном случае нам остается только гадать. - person thatjeffsmith; 18.03.2021
comment
Извиняюсь. Я думаю, я не сделал это достаточно ясно. Я поместил это в заголовок над моей таблицей DDL ==> TABLE (у нее есть триггер для заполнения идентификатора, который я не включил). Как я пытался объяснить, хранимая процедура работает должным образом, когда я тестирую ее в SQL Dvlpr. Это не работает, когда его вызывает обработчик, что заставляет меня поверить, что я как-то перепутал параметры... Но я новичок в этом. Извините за путаницу. - person Coder-1; 19.03.2021
comment
Я добавил ТРИГГЕР к своему вопросу. Моя вина... - person Coder-1; 19.03.2021
comment
Без проблем! Посмотрите, что я сделал, откорректируйте свой код, попробуйте еще раз и отчитайтесь! - person thatjeffsmith; 19.03.2021
comment
Я попытался добавить p_id и обойти триггер и заполнить идентификатор явно, как вы. Еще раз, SP работает, используя зеленую стрелку, но я получаю ту же ошибку при вызове из обработчика ORDS (используя зеленую стрелку). Я пытаюсь максимально приблизиться к вашему примеру. Это может быть в понедельник, прежде чем я вернусь к этому. - person Coder-1; 19.03.2021
comment
Только что вернулся к этому. Я переключил SP и обработчик, чтобы просто вернуть только что сгенерированное поле идентификатора, и это сработало. Поэтому он не возвращает рекурсор. Я думаю, что на данный момент я пойду с этим, хотя я бы предпочел вернуть все введенные значения, но пока я собираюсь сократить свои потери. Спасибо за ваши идеи. Я люблю ваши блоги! - person Coder-1; 25.03.2021