I am working on a function which of the following string:

create or replace PROCEDURE get_error_keys(p_error_code IN VARCHAR2) IS
    v_error_key varchar2(2000):= '
EF03   There are disabled accounts
EF04   The account is invalid. Check your cross validation rules and segment values
EF05   There is no account with this account combination ID
EF06   The alternate account is invalid
WF01   An alternate account was used instead of the original account
WF02   A suspense account was used instead of the original account';
v_linea              VARCHAR2(32767);
    --
--
Begin
    
   SELECT
    regexp_substr(v_error_key_list, '[^('||chr(13)||chr(10)||')] ',1,level) Linea into v_linea
FROM
    dual
CONNECT BY
    regexp_substr(v_error_key_list, '[^('||chr(13)||chr(10)||')] ',1,level) IS NOT NULL;
    
End;

My intention is to filter this query, which receives p_error_code and filters by that parameter, for example:

If p_error_code = 'EF04', then the query returns: EF04 The account is invalid. Check your cross validation rules and segment values

I have modified the query to add a where condition:

select
     regexp_substr(v_error_key, '[^('||chr(13)||chr(10)||')] ',1,level) Line into v_line
DESDE
     dual
WHERE Line like '%'||p_error_code||'%'
CONNECT BY
     regexp_substr(v_error_key, '[^('||chr(13)||chr(10)||')] ',1,level) IS NOT NULL;

But I get the following error: 7/18 PL/SQL: ORA-00904: "Linea": invalid identifier

Thanks, Cesar.

CodePudding user response：

As I said in the comment, the right way is to put the code/descriptions in a table and then do a simple lookup. But since I'm sitting in a meeting and can play with this...it can be simplified by treating the string of error/descriptions as a delimited string with a chr(10) character as the delimiter. Get the substring that starts with the error code passed in, followed by one or more whitespace characters, then zero or more any characters non-greedy until the delimiter or the end of the line.

SELECT
REGEXP_SUBSTR(v_error_key_list, '('||p_error_code||'\s .*?)('||CHR(10)||'|$)')
INTO v_linea

But at least do this for easier maintenance if you can't create a proper lookup table for some reason. Use a WITH clause to put your codes/descriptions in a table form. Best practice is to always allow for the exception where your code is not found also.

BEGIN
  WITH tbl_err(code, DESCR) AS (
    SELECT 'EF03', 'There are disabled accounts' FROM dual UNION ALL
    SELECT 'EF04', 'The account is invalid. Check your cross validation rules and segment values' FROM dual UNION ALL
    SELECT 'EF05', 'There IS NO ACCOUNT WITH this ACCOUNT combination ID' FROM dual UNION ALL
    SELECT 'EF06', 'The alternate account is invalid' FROM dual UNION ALL
    SELECT 'WF01', 'An alternate ACCOUNT was used INSTEAD OF THE original ACCOUNT' FROM dual UNION ALL
    SELECT 'WF02', 'A suspense account was used instead of the original account' FROM dual
  )
  SELECT DESCR
  INTO v_linea
  FROM tbl_err
  WHERE code = p_error_code;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_linea := 'Code: ' || p_error_code || ' not found.';
  END;
  

CodePudding user response：

It would really be best to put the error codes and error texts into some sort of lookup table. Below is a function that meets your requirements with a hard-coded string containing multiple error codes.

create or replace FUNCTION get_error_keys(p_error_code IN VARCHAR2) 
RETURN VARCHAR2
IS
    v_error_key_list varchar2(2000):= '
EF03   There are disabled accounts
EF04   The account is invalid. Check your cross validation rules and segment values
EF05   There is no account with this account combination ID
EF06   The alternate account is invalid
WF01   An alternate account was used instead of the original account
WF02   A suspense account was used instead of the original account';
    --
--
Begin
    
   RETURN REGEXP_SUBSTR(v_error_key_list, p_error_code || '\s [^' || CHR(10) ||'$] ');
    
End;

Below is code to test the function.

DECLARE

  TYPE t_input_value_tbl IS TABLE OF VARCHAR2(4);
  l_input_value_tbl t_input_value_tbl;
  l_return_value VARCHAR2(32767);

BEGIN

  l_input_value_tbl := t_input_value_tbl('EF03','EF04','EF05','EF06','WF01','WF02','ZZ01');

  FOR i IN l_input_value_tbl.FIRST..l_input_value_tbl.LAST LOOP
  
    l_return_value := get_error_keys(l_input_value_tbl(i));
    dbms_output.put_line('input: "' || l_input_value_tbl(i) || '" l_return_value: "' || l_return_value || '"');
  
  END LOOP;

END;