Oracle nvl2 не работает в хранимой процедуре PLS-00201: необходимо объявить идентификатор NVL2

Как заголовок, я пишу хранимую процедуру на Oracle, сначала я проверил версию

SELECT * FROM v$version;

с результатом

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

...

И попробовал NVL2 с обычным запросом SQL

select 'Test: ' || nvl2('...', 'things', 'nothing') from dual;
select 'Test: ' || nvl2('', 'things', 'nothing') from dual;

результат кажется правильным

Test: things
Test: nothing

...

Итак, я подтвердил, что 11g поддерживает функцию NLV2, теперь я могу начать писать свою хранимую процедуру, например:

create or replace procedure my_schema.SP_READ_MEMBER(noP in varchar2, nameP in varchar2, idNoP in varchar2, birthdayP in varchar2, resultP out sys_refcursor)
is
v_prg_name varchar2(20) := 'SP_READ_MEMBER';
sys_sql    varchar2(1000);

begin
  Insertlog(SYSDATE, v_prg_name, '1.0 Start');
  sys_sql :=  sys_sql || 'select a.no, a.name, a.id_no, to_char(a.birthday, ''yyyy/MM/dd'') as birthday, ''REGISTERED'' as type, email, mobile from rep where 1=1 ';
  sys_sql :=  sys_sql || nvl2(noP,'and no='''|| noP ||'''', ''); --PLS-00201

  open resultP for sys_sql;
  Insertlog(SYSDATE, v_prg_name, '2.0 Finished w/o error');

  exception
  when others then
    declare
      error_time VARCHAR2(30) := RTRIM(TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS'));
      error_code NUMBER := SQLCODE;
      error_msg  VARCHAR2(300) := SQLERRM;
    begin
      rollback;
      DBMS_OUTPUT.PUT_LINE(error_time || ',' || TO_CHAR(error_code) || ',' || error_msg);
      Insertlog(SYSDATE, v_prg_name,  error_msg || ', 3.0 ERROR, sql:' || sys_sql);
    end;
end;
/

Oracle сказал мне, что он скомпилирован с ошибкой, которая

PLS-00201: необходимо объявить идентификатор NVL2

Почему функция работает в обычном запросе, но становится необъявленной в хранимой процедуре?


oracle stored-procedures nvl pls-00201
person RRTW    schedule 22.07.2019    source источник
comment
У вас интересный вариант использования. Я почти уверен, что nvl2 не отображается как функция PL/SQL. Попробуйте coalesce. Еще не смотрел на MOS, но я считаю отсутствие этого отображения ошибкой. Но если это не будет исправлено, то это, вероятно, потому, что coalesce намного лучше, чем nvl2.   -  person Jeff Holt    schedule 22.07.2019
comment
NVL2 также не работает в 12.2.0.1 (чтобы вы знали, что обновление базы данных не решит эту проблему).   -  person Littlefoot    schedule 22.07.2019
comment
Не все функции SQL поддерживаются в PL/SQL. decode это другое.   -  person William Robertson    schedule 22.07.2019

Ответы (1)


arrow_upward
5
arrow_downward

NVL2 работает в SQL, но не в PL/SQL.

Не каждое ключевое слово SQL также работает в PL/SQL, что глупо, но это уже случалось несколько раз. Oracle постепенно унифицирует SQL и PL/SQL, и есть вероятность, что в конечном итоге они доберутся до этой функции. На данный момент я думаю, что эта проблема рассматривается в документе My Oracle Support Note 359506.1 Pls-00201 Assigning The Result of NVL2() To A Variable In a PLSQL Block. Хотя этот документ недоступен даже людям с доступом в службу поддержки.

Сейчас я рекомендую использовать другой синтаксис. Лично я нахожу версию CASE более понятной, даже если она немного многословнее.

Изменять:

sys_sql :=  sys_sql || nvl2(noP,'and no='''|| noP ||'''', '');

To:

sys_sql :=  sys_sql || case when nop is not null then 'and no='''|| noP ||'''' else '' end;
person Jon Heller    schedule 23.07.2019
comment
Итак, это было ограничение поддержки PL/SQL в Oracle 11g... Наконец-то я понял. Также тот случай, когда решение помогает, спасибо~ - person RRTW; 23.07.2019