Getting Error “PLS-00201: identifier ‘JSON_VALUE’ must be declared” in PL/SQL

I am trying to extract data from Json file stored in a table. But I am not able to execute the package JSON_VALUE inside PL/SQL.

Below query works fine:

SELECT JSON_VALUE('{a:100}', '$.a' ) AS value
  FROM DUAL;

But Below PL/SQL block doesn’t work:

declare 
  SQL_QUERY_RES varchar2(300);
  cursor c1 is
  SELECT 1 from DUAL;
begin
   for i in c1 
   loop
     SQL_QUERY_RES :=  JSON_VALUE('{a:100}', '$.a' );
     DBMS_OUTPUT.PUT_LINE ( SQL_QUERY_RES );    
   end loop;
   COMMIT;
end;
/

It gives PLS-00201: identifier ‘JSON_VALUE’ must be declared Error.

Answer

Try this

DECLARE
    SQL_QUERY_RES   VARCHAR2(300);
    CURSOR C1 IS
    SELECT
        1
    FROM
        DUAL;

BEGIN
    FOR I IN C1 LOOP
        -- USE THIS
        SELECT
            JSON_VALUE('{a:100}', '$.a')
        INTO SQL_QUERY_RES
        FROM
            DUAL;

        DBMS_OUTPUT.PUT_LINE(SQL_QUERY_RES);
    END LOOP;

    COMMIT;
END;
/

It is working for me.

For DB version 12.1, PL/SQL assignment SQL_QUERY_RES := JSON_VALUE('{a:100}', '$.a' ); doesn’t work, you need to use within a select statement, but for 12.2 works.