Search code examples
oracle-databaseplsqlplsql-package

Error ORA-00904 while calling procedure inside package using EXECUTE IMMEDIATE

I'm working on a PLSQL package that calls another PLSQL package that returns separated results (i.e OUT variables) as follows:

  • (1) SYS_REFCURSOR
  • (1) NUMBER
  • (1) VARCHAR2

This is the dbfiddle I build.

Full code of the linked dbfiddle:

/* Main table - it contains the data to use in a cursor: */
CREATE TABLE tpos_retbenf
(
id_serial NUMBER (9,0),
serial_nmb NUMBER(12,0)
);

/* Destination of the records detected on "tpos_retbenf": */
CREATE TABLE tbl_debug 
(
msg_text VARCHAR2(1000),
record_date DATE
);

/* Add values to the main table: */
INSERT INTO tpos_retbenf (id_serial, serial_nmb) 
VALUES (1, 202108311635);

/* Package that contains the code to execute: */
create or replace PACKAGE                 PCK_POS_UNO is

PROCEDURE SP_POS_UNO (ID_RECORD IN NUMBER,
                                  CUR_RET_BENF OUT SYS_REFCURSOR,
                                  IDERROR OUT NUMBER,
                                  DSERROR OUT VARCHAR2);

end PCK_POS_UNO;
/

create or replace PACKAGE BODY                 PCK_POS_UNO is

/* This is the procedure that returns results in separated variables: */
PROCEDURE SP_POS_UNO (ID_RECORD IN NUMBER,
                                  CUR_RET_BENF OUT SYS_REFCURSOR,
                                  IDERROR OUT NUMBER,
                                  DSERROR OUT VARCHAR2) AS


v_temp number(6) := 0;
v_S varchar2(1) := 'S';

BEGIN

    if ID_RECORD is null or ID_RECORD <= 0 then
      IDERROR := -1;
      DSERROR := 'Id no valido para la operacion';
      goto finalizar;
    end if;

    select count(1) into v_temp
    from tpos_retbenf r
    where r.id_serial = ID_RECORD;

    if v_temp = 0 then
      IDERROR := -1;
      DSERROR := 'Id no encontrado';
      goto finalizar;
    end if;

    OPEN CUR_RET_BENF FOR
    select r.id_serial, r.serial_nmb
    from tpos_retbenf r 
     where r.id_serial = ID_RECORD;


<<finalizar>>
 null;

END SP_POS_UNO;

END PCK_POS_UNO;
/

/* Package that calls the "SP_POS_UNO" procedure from the "PCK_POS_UNO" package: */
create or replace PACKAGE PKG_BH_ONLINE_INFORMATION IS
  PROCEDURE ONLINENOVELTYBEN
   (
      V_NID_DEV IN NUMBER,  
        CV_1 IN OUT SYS_REFCURSOR
    );
END PKG_BH_ONLINE_INFORMATION;
/

create or replace PACKAGE BODY PKG_BH_ONLINE_INFORMATION IS 
PROCEDURE ONLINENOVELTYBEN
(
  V_NID_DEV IN NUMBER,
    CV_1 IN OUT SYS_REFCURSOR
) IS
    
    V_USER VARCHAR2(10 CHAR) := 'INTERNET';
    V_QUERY VARCHAR2(10000 CHAR);
    -- Variables:
    V_OUT_CUR_RET_BENF SYS_REFCURSOR;
    V_OUT_IDERROR NUMBER;
    V_OUT_DSERROR VARCHAR2(10000 CHAR);
BEGIN

    /* 
        Here, the "PCK_POS_UNO.SP_POS_UNO" is called 
        from "PKG_BH_ONLINE_INFORMATION" as follows:
    */
    V_QUERY := 'SELECT APPLICATION_POS.PCK_POS_UNO.SP_POS_UNO(:V_NID_DEV, :V_OUT_CUR_RET_BENF, :V_OUT_IDERROR, :V_OUT_DSERROR) FROM DUAL';
    EXECUTE IMMEDIATE V_QUERY INTO V_OUT_CUR_RET_BENF, V_OUT_IDERROR, V_OUT_DSERROR USING V_NID_DEV, V_OUT_CUR_RET_BENF, V_OUT_IDERROR, V_OUT_DSERROR ;
    
    /*
      An this error occurs:
      Error: ORA-00904: "PCK_POS_UNO"."SP_POS_UNO": invalid identifier - StackTrace: ORA-06512: in line 24
    */

    -- After getting the results in (V_OUT_CUR_RET_BENF, V_OUT_IDERROR, V_OUT_DSERROR) variables, 
    -- a LOOP is executed for retrieve the records in "V_OUT_CUR_RET_BENF" cursor...
    -- It doesn't continue here due to error shown above.

END ONLINENOVELTYBEN;

END PKG_BH_ONLINE_INFORMATION;
/

When the following code is going to be executed:

V_QUERY := 'SELECT APPLICATION_POS.PCK_POS_UNO.SP_POS_UNO(:V_NID_DEV, :V_OUT_CUR_RET_BENF, :V_OUT_IDERROR, :V_OUT_DSERROR) FROM DUAL';
EXECUTE IMMEDIATE V_QUERY INTO V_OUT_CUR_RET_BENF, V_OUT_IDERROR, V_OUT_DSERROR USING V_NID_DEV, V_OUT_CUR_RET_BENF, V_OUT_IDERROR, V_OUT_DSERROR ;

The error says:

Error: ORA-00904: "APPLICATION_POS"."PCK_POS_UNO"."SP_POS_UNO": invalid identifier - StackTrace: ORA-06512: in line 24

I've tried so far:

  • Search for ORA-00904 error - in this answer says "proper permissions on objects involved in the query" - which I share, but, I don't know how to argument this option (since I can do a simple SELECT to that table and the results are shwon, hence, they might not accept this argument). Related to this argument, I can't get listed the PCK_POS_UNO package "since the OWNER is different from the one I'm usually using (that is APPLICATION)".
  • I made a copy of this package/procedure and was able to execute the procedure/package via SQL Developer - see screenshot, but, the same error ORA-00904 occurs.

Screentshot of the execution of the package:

screenshot

Results:

enter image description here

  • Change the code that calls the procedure that has OUT parameters, but, I'm unable to get a successful combination that allows the compilation and execution of the code as a whole.

Examples - all based on internet searchs and my own "instinct":

(1): Added (;) at the end of the dynamic-sql string:

V_QUERY := 'SELECT APPLICATION_POS.PCK_POS_UNO.SP_POS_UNO((:V_NID_DEV, :V_OUT_CUR_RET_BENF, :V_OUT_IDERROR, :V_OUT_DSERROR) FROM DUAL;';

(2): Removing the OWNER - in this case "APPLICATION_POS":

V_QUERY := 'SELECT PCK_POS_UNO.SP_POS_UNO(:V_NID_DEV, :V_OUT_CUR_RET_BENF, :V_OUT_IDERROR, :V_OUT_DSERROR) FROM DUAL';

(3): Calling the procedure directly - it shows SP2-0552: bind variable "V_NID_DEV" not declared - but, how?, in a separated sample, the variable "V_NID_DEV" is declared and with value "2462013":

PCK_POS_UNO.SP_POS_UNO(:V_NID_DEV, :V_OUT_CUR_RET_BENF, :V_OUT_IDERROR, :V_OUT_DSERROR);

(4): Calling the procedure directly (removing also the points) - in this case, ORA-01001 - invalid cursor error is generated - which I think it doesn't make sense - since the OUT cursor is not being opened for read or operated somehow.

PCK_POS_UNO.SP_POS_UNO(V_NID_DEV, V_OUT_CUR_RET_BENF, V_OUT_IDERROR, V_OUT_DSERROR);

I'm really run out of ideas - since I'm not familiar with this type of creating packages and passing values between packages and I didn't created this code.

Is there any way to make this code work?

Solution

  • Package modified to call procedure SP_POS_UNO:

    CREATE OR REPLACE PACKAGE BODY PKG_BH_ONLINE_INFORMATION
IS 
  PROCEDURE ONLINENOVELTYBEN(V_NID_DEV IN NUMBER,
                             CV_1      IN OUT SYS_REFCURSOR
                            )
  IS
    V_USER  VARCHAR2(10 CHAR) := 'INTERNET';
    V_QUERY VARCHAR2(10000 CHAR);
    -- Variables:
    V_OUT_CUR_RET_BENF   SYS_REFCURSOR;
    V_OUT_IDERROR        NUMBER;
    V_OUT_DSERROR       VARCHAR2(10000 CHAR);
  BEGIN
      /* 
          Here, the "PCK_POS_UNO.SP_POS_UNO" is called 
          from "PKG_BH_ONLINE_INFORMATION" as follows:
      */
      V_QUERY:='Begin
 PCK_POS_UNO.SP_POS_UNO(:V_NID_DEV, :V_OUT_CUR_RET_BENF, :V_OUT_IDERROR, :V_OUT_DSERROR);
End;';
      --
      EXECUTE IMMEDIATE V_QUERY
      USING     V_NID_DEV, 
            out V_OUT_CUR_RET_BENF, 
            out V_OUT_IDERROR, 
            out V_OUT_DSERROR ;
      Dbms_Output.Put_Line('V_OUT_IDERROR='||V_OUT_IDERROR);
      Dbms_Output.Put_Line('V_OUT_DSERROR='||V_OUT_DSERROR);
      --
      CV_1:=V_OUT_CUR_RET_BENF;
  END ONLINENOVELTYBEN;
  --
END PKG_BH_ONLINE_INFORMATION;