Hi,
I wonder if I can get any help.
This is my sql/db:
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 25 15:46:03 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Jan 25 2022 15:30:39 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
I have a package and the gist of it as below as . I have included an extract as its quite big.
CREATE OR REPLACE PACKAGE spk_test IS
FUNCTION f_get_substr(in_string IN VARCHAR2, position NUMBER) RETURN VARCHAR2;
FUNCTION f_test RETURN INTEGER ;
END;
/
CREATE OR REPLACE PACKAGE BODY spk_test IS
FUNCTION f_get_substr(in_string IN VARCHAR2, position NUMBER)
RETURN VARCHAR2
IS
ret_string VARCHAR2(100);
BEGIN
IF position = 1 THEN
ret_string := upper(trim(substr(in_string, 1, instr(in_string, ',', 1, 1)-1)));
ELSIF position = 2 THEN
ret_string := upper(trim(substr(in_string, instr(in_string, ',', 1, 1)+1, instr(in_string, ',', 1, 2)-instr(in_string, ',', 1, 1)-1)));
ELSIF position = 3 THEN
ret_string := upper(trim(substr(in_string, instr(in_string, ',', 1, 2)+1, length(in_string)-instr(in_string, ',', 1, 2))));
ELSE
ret_string := '';
END IF;
RETURN(NVL(ret_string, 'DUMMY'));
END;
FUNCTION f_test RETURN INTEGER IS
BEGIN
SELECT xx
FROM
tables\_x AI
where f\_get\_substr(AI.DSC,1) != 'LAST TARGET';
END;
END;
/
when i do a query in sqlplus such as below which is the same query as in the package I get ...
SELECT xx
FROM
tables_x AI
where f_get_substr(AI.DSC,1) != 'LAST TARGET';
it comes up with the error of ora-00904 invalid identifier. This is working fine in one environment but not in this one.
ERROR at line 50:
ORA-00904: "F_GET_SUBSTR": invalid identifier
Is there anything obvious that I have missed as regards getting this error.
As I said I havent included the whole package as its quite huge but gave a flavour so as to show what's occurring.
Regards