ORA-00904 - Invalid Identifier in Dynamic SQL

Question

I'm trying to get to the bottom of ORA-00904 - Invalid Identifier error that I'm getting in my stored procedure.

Here is the code:

CREATE OR REPLACE PROCEDURE CDG4_HIER_GET_SUBTREE(pDimensionId IN VARCHAR2, pPeriodId IN NUMBER, pNodeId IN NUMBER, pMode IN CHAR, rCursor OUT SYS_REFCURSOR) IS

v_table_name VARCHAR2(30);
v_function_name VARCHAR2(30) := 'CDG4_HIER_MGR_HAS_CHILDREN'; -- some function defined in the same package
InvalidMode EXCEPTION;
---
v_sql VARCHAR2(2000);

BEGIN

-- Get dynamic table name
v_sql := 'SELECT UPPER(TABLE_ID)'
      ||'FROM CDG4_CFG_MAP_GER '
      ||'WHERE UPPER(DIMENSION_ID) = UPPER(''' || pDimensionId || ''') ';

EXECUTE IMMEDIATE v_sql INTO v_table_name;

IF pMode = 'F' THEN

    -- Do something

ELSIF pMode = 'S' THEN

    v_sql := 'SELECT A.PERIODO_K, '
           ||'      A.FIGLIO_K, '
           ||'      A.PADRE_K, '
           ||'      A.F_LAYOUT, '
           ||'      A.F_VISUALIZZA, '
           ||'      DECODE(A.PADRE_K, NULL, NULL, B2.CODICE) AS CODICE_PADRE, '
           ||'      A.F_ORDINE, '
           ||'      DECODE(A.GEN1, ''2'', ''I'', A.GEN1) AS GEN1, '
           ||'      A.GEN2, '
           ||'      A.GEN3, '
           ||'      ''[''|| B1.CODICE || ''] - '' || B1.DESCR AS DESCR, '
           ||'      ''[''|| B1.CODICE || ''] - '' || B1.DESCR AS DESCRIZIONE, '
           ||'      ' || v_function_name || '(''' || pDimensionId || ''', ' || pPeriodId || ', A.FIGLIO_K) AS HAS_CHILDREN '
           ||'FROM DM_GERARCHIE A, DIM_BO_CANALE_PRIMARIA B1, DIM_BO_CANALE_PRIMARIA B2 '
           ||'WHERE A.FIGLIO_K = B1.CODICE_K '
           ||'AND A.PADRE_K = B2.CODICE_K '
           ||'AND UPPER(A.DIMENSION_ID) = UPPER(''' || pDimensionId || ''') ' 
           ||'AND UPPER(B1.DIMENSION_ID) = UPPER(''' || pDimensionId || ''') ' 
           ||'AND UPPER(B2.DIMENSION_ID) = UPPER(''' || pDimensionId || ''') '
           ||'AND A.PERIODO_K = ' || pPeriodId
           ||' AND A.FIGLIO_K = ' || pNodeId;

ELSE 

    RAISE InvalidMode;

END IF;

OPEN rCursor FOR v_sql;

EXCEPTION
WHEN InvalidMode THEN
RAISE_APPLICATION_ERROR(-20005, 'Invalid selection mode.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20004, 'Generic error - '||SQLCODE||' -ERROR- '||SQLERRM);

END;

Oracle seems not to be able to identify the function v_function_name (it is in the same package) as it keeps returning ORA-00904 while opening the cursor rCursor.

Any hint to a plausible clause of this behaviour?

Thanks in advance.

Answer 1

Oracle seems not to be able to identify the function v_function_name (it is in the same package

Dynamic SQL is executed as SQL but outside of the scope of the package. That means we need to build a valid SQL statement. If you were running a similar static SELECT statement you would have written package_name.v_function_name because your SQL would execute outside the package. And that's what you need to do here.

---

Although now I've looked more closely at your code and I don't understand why you are using Dynamic SQL at all. You could execute your statements as static SQL with bind variables and parameters.

OPEN rCursor FOR 
    SELECT A.PERIODO_K, 
           A.FIGLIO_K, 
           A.PADRE_K, 
           A.F_LAYOUT, 
           A.F_VISUALIZZA, 
           DECODE(A.PADRE_K, NULL, NULL, B2.CODICE) AS CODICE_PADRE, 
           A.F_ORDINE, 
           DECODE(A.GEN1, ''2'', ''I'', A.GEN1) AS GEN1, 
           A.GEN2, 
           A.GEN3, 
           '[' || B1.CODICE || '] - ' ||  B1.DESCR AS DESCR, 
           '[' || B1.CODICE || '] - ' || B1.DESCR AS DESCRIZIONE, 
            package_name.v_function_name (pDimensionId ,pPeriodId , A.FIGLIO_K) AS HAS_CHILDREN 
      FROM DM_GERARCHIE A, DIM_BO_CANALE_PRIMARIA B1, DIM_BO_CANALE_PRIMARIA B2 
      WHERE A.FIGLIO_K = B1.CODICE_K 
      AND A.PADRE_K = B2.CODICE_K 
      AND UPPER(A.DIMENSION_ID) = UPPER( pDimensionId )  
      AND UPPER(B1.DIMENSION_ID) =  UPPER( pDimensionId )  
      AND UPPER(B2.DIMENSION_ID) = UPPER( pDimensionId )  
      AND A.PERIODO_K = pPeriodId
      AND A.FIGLIO_K = pNodeId;