Oracle PL/SQL: Function or Procedure will not work when used in dynamic query
I came across this error ORA-00904: "GetTotalItem": invalid identifier
when I tried to call a local function inside a dynamic query.
I am currently working with Oracle database (we’re using
Oracle 9i). My task is to add additional field (TotalItem) in the existing
query in the StoredProc1 procedure.
//Package definition
CREATE OR REPLACE MyPackage
AS
TYPE
RefCursorType IS REF CURSOR;
PROCEDURE StoredProc1(myRefCursor OUT RefCursorType);
PROCEDURE StoredProc2();
..
FUNCTION GetTotalItem(productId IN NUMBER) RETURN
NUMBER;
END;
//package body
CREATE OR REPLACE PACKAGE BODY MyPackage AS
PROCEDURE StoredProc1(myRefCursor OUT RefCursorType)
IS
MyQuery VARCHAR2(300);
BEGIN
myQuery := 'SELECT
field1, field2, field3, GetTotalItem(productId) as TotalItem FROM Sometable'
OPEN myRefCursor FOR myQuery;
//this will raise error ORA-00904
END;
.. .. // other codes omitted
END;
To fix the error, the function should be fully qualified with the package
name.
'SELECT field1, field2, field3, MyPackage.GetTotalItem(producId)
as TotalItem FROM Sometable'
... (currently learning Oracle) 
.