저장 프로시저 PLS-00201에서 Oracle nvl2가 작동하지 않음: 식별자 'NVL2'를 선언해야 합니다.

저장 프로시저 PLS-00201에서 Oracle nvl2가 작동하지 않음: 식별자 'NVL2'를 선언해야 합니다.

제목은 오라클에 저장 프로시저를 쓰고 있는데 먼저 버전을 확인했습니다.

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

...

그리고 일반 SQL 쿼리를 사용하여 NVL2를 시도했습니다.

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

오라클이 오류를 가지고 컴파일을 했다고 알려주었는데, 그것은

PLS-00201: 식별자 'NVL2'를 선언해야 합니다.

함수는 일반 쿼리에서는 작동하지만 저장 프로시저에서는 선언되지 않는 이유는 무엇입니까?

NVL2SQL에서는 작동하지만 버전 21c 이상이 아니면 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 ||'''', '');

받는 사람:

sys_sql :=  sys_sql || case when nop is not null then 'and no='''|| noP ||'''' else '' end;

NVL2는 SQL에서는 작동하지만 PL/SQL에서는 작동하지 않습니다.

변경 사항:

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

받는 사람:

select sys_sql || nvl2(noP,'and no='''|| noP ||'''', '') into sys_sql from dual;

언급URL : https://stackoverflow.com/questions/57138515/oracle-nvl2-not-working-in-stored-procedure-pls-00201-identifier-nvl2-must-be