Home » SQL & PL/SQL » SQL & PL/SQL » Function call in ref cursor (Oracle 11gR2)
icon4.gif  Function call in ref cursor [message #679950] Tue, 14 April 2020 11:12 Go to next message
Bhushan.Mahajan
Messages: 4
Registered: April 2020
Location: Mumbai India
Junior Member
Hi,
I am trying to call private function of package in ref cursor of same package procedure.
My code shows error while identifying that function.
My code is as below.

CREATE OR REPLACE PACKAGE PR_SAL_AND_COMM
AS
    PROCEDURE PR_ADD_SAL_COMM(TABLE_NAME VARCHAR);
END;
/

CREATE OR REPLACE PACKAGE BODY PR_SAL_AND_COMM
AS
    FUNCTION MAKE_MUL(SAL NUMBER, COMM NUMBER)
    RETURN NUMBER
    AS
    BEGIN
        RETURN SAL + ( SAL * NVL(COMM, 0));
    END;
        
    PROCEDURE PR_ADD_SAL_COMM(TABLE_NAME VARCHAR)
    AS
        TYPE REC_DATA IS RECORD 
        ( 
            EMPLOYEE_ID HR.EMPLOYEES.EMPLOYEE_ID%TYPE,
            SAL_COMM NUMBER
        );
        TYPE NT_DATA IS TABLE OF REC_DATA;
        TYPE CURSOR1 IS REF CURSOR;
        
        NT NT_DATA;
        C1 CURSOR1;
        
        ALTER_SQL_STMT VARCHAR2(100);
        UPDATE_SQL_STMT VARCHAR2(100);
        CURSOR_SELECT_STMT VARCHAR2(100);
        CREATE_SQL_STMT VARCHAR2(100);
        
    BEGIN
        
        CREATE_SQL_STMT := 'CREATE TABLE '||TABLE_NAME||' AS SELECT * FROM HR.EMPLOYEES';
        EXECUTE IMMEDIATE CREATE_SQL_STMT;
    
        ALTER_SQL_STMT := 'ALTER TABLE '||TABLE_NAME||' ADD (SAL_COMM NUMBER)';
        EXECUTE IMMEDIATE ALTER_SQL_STMT;
                
        CURSOR_SELECT_STMT := 'SELECT EMPLOYEE_ID, MAKE_MUL(SALARY, COMMISSION_PCT) FROM '||TABLE_NAME;
        OPEN C1 FOR CURSOR_SELECT_STMT;
        FETCH C1 BULK COLLECT INTO NT;
        CLOSE C1;
        
        UPDATE_SQL_STMT := 'UPDATE '||TABLE_NAME||' SET SAL_COMM = :1 WHERE EMPLOYEE_ID = :2';
        FORALL I IN 1..NT.COUNT
            EXECUTE IMMEDIATE UPDATE_SQL_STMT USING NT(I).SAL_COMM, NT(I).EMPLOYEE_ID;
        
        COMMIT;
        
    END;
END;
/

EXEC PR_SAL_AND_COMM.PR_ADD_SAL_COMM('EMP20200431');
Thanks & Regards
Bhushan Mahajan


[Edit MC: add code tags]

[Updated on: Tue, 14 April 2020 11:20] by Moderator

Report message to a moderator

Re: Function call in ref cursor [message #679953 is a reply to message #679950] Tue, 14 April 2020 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

We need all your code to be able to reproduce what you get:
SQL> CREATE OR REPLACE PACKAGE PR_SAL_AND_COMM
  2  AS
  3      PROCEDURE PR_ADD_SAL_COMM(TABLE_NAME VARCHAR);
  4  END;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY PR_SAL_AND_COMM
  2  AS
  3      FUNCTION MAKE_MUL(SAL NUMBER, COMM NUMBER)
  4      RETURN NUMBER
  5      AS
  6      BEGIN
  7          RETURN SAL + ( SAL * NVL(COMM, 0));
  8      END;
  9
 10      PROCEDURE PR_ADD_SAL_COMM(TABLE_NAME VARCHAR)
 11      AS
 12          TYPE REC_DATA IS RECORD
 13          (
 14              EMPLOYEE_ID HR.EMPLOYEES.EMPLOYEE_ID%TYPE,
 15              SAL_COMM NUMBER
 16          );
 17          TYPE NT_DATA IS TABLE OF REC_DATA;
 18          TYPE CURSOR1 IS REF CURSOR;
 19
 20          NT NT_DATA;
 21          C1 CURSOR1;
 22
 23          ALTER_SQL_STMT VARCHAR2(100);
 24          UPDATE_SQL_STMT VARCHAR2(100);
 25          CURSOR_SELECT_STMT VARCHAR2(100);
 26          CREATE_SQL_STMT VARCHAR2(100);
 27
 28      BEGIN
 29
 30          CREATE_SQL_STMT := 'CREATE TABLE '||TABLE_NAME||' AS SELECT * FROM HR.EMPLOYEES';
 31          EXECUTE IMMEDIATE CREATE_SQL_STMT;
 32
 33          ALTER_SQL_STMT := 'ALTER TABLE '||TABLE_NAME||' ADD (SAL_COMM NUMBER)';
 34          EXECUTE IMMEDIATE ALTER_SQL_STMT;
 35
 36          CURSOR_SELECT_STMT := 'SELECT EMPLOYEE_ID, MAKE_MUL(SALARY, COMMISSION_PCT) FROM '||TABLE_NAME;
 37          OPEN C1 FOR CURSOR_SELECT_STMT;
 38          FETCH C1 BULK COLLECT INTO NT;
 39          CLOSE C1;
 40
 41          UPDATE_SQL_STMT := 'UPDATE '||TABLE_NAME||' SET SAL_COMM = :1 WHERE EMPLOYEE_ID = :2';
 42          FORALL I IN 1..NT.COUNT
 43              EXECUTE IMMEDIATE UPDATE_SQL_STMT USING NT(I).SAL_COMM, NT(I).EMPLOYEE_ID;
 44
 45          COMMIT;
 46
 47      END;
 48  END;
 49  /

Package body created.

SQL> EXEC PR_SAL_AND_COMM.PR_ADD_SAL_COMM('EMP20200431');
BEGIN PR_SAL_AND_COMM.PR_ADD_SAL_COMM('EMP20200431'); END;

*
ERROR at line 1:
ORA-00904: "MAKE_MUL": invalid identifier
ORA-06512: at "MICHEL.PR_SAL_AND_COMM", line 37
ORA-06512: at line 1
Re: Function call in ref cursor [message #679968 is a reply to message #679950] Tue, 14 April 2020 18:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
This can never work by definition. When you call SQL from PL/SQL you switch context to SQL and now all that SQL can see is named PL/SQL objects - procedures, functions, packages. Package private functions are not visible from outside package body. Therefore all that SQL can reference is package functions declared in package specification and in form of package_name.function_name, just function name will be interpreted as standalone name.

SY.
Re: Function call in ref cursor [message #679969 is a reply to message #679968] Wed, 15 April 2020 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
just function name will be interpreted as standalone name.

... as I did, not seeing the function is in the package. Laughing

Re: Function call in ref cursor [message #679971 is a reply to message #679969] Wed, 15 April 2020 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So solution is:
SQL> CREATE OR REPLACE PACKAGE PR_SAL_AND_COMM
  2  AS
  3      PROCEDURE PR_ADD_SAL_COMM(TABLE_NAME VARCHAR);
  4      FUNCTION MAKE_MUL(SAL NUMBER, COMM NUMBER) RETURN NUMBER;
  5
  6  END;
  7  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY PR_SAL_AND_COMM
  2  AS
  3      FUNCTION MAKE_MUL(SAL NUMBER, COMM NUMBER)
  4      RETURN NUMBER
  5      AS
  6      BEGIN
  7          RETURN SAL + ( SAL * NVL(COMM, 0));
  8      END;
  9
 10      PROCEDURE PR_ADD_SAL_COMM(TABLE_NAME VARCHAR)
 11      AS
 12          TYPE REC_DATA IS RECORD
 13          (
 14              EMPLOYEE_ID HR.EMPLOYEES.EMPLOYEE_ID%TYPE,
 15              SAL_COMM NUMBER
 16          );
 17          TYPE NT_DATA IS TABLE OF REC_DATA;
 18          TYPE CURSOR1 IS REF CURSOR;
 19
 20          NT NT_DATA;
 21          C1 CURSOR1;
 22
 23          ALTER_SQL_STMT VARCHAR2(100);
 24          UPDATE_SQL_STMT VARCHAR2(100);
 25          CURSOR_SELECT_STMT VARCHAR2(100);
 26          CREATE_SQL_STMT VARCHAR2(100);
 27
 28      BEGIN
 29
 30          CREATE_SQL_STMT :=
 31            'CREATE TABLE '||dbms_assert.enquote_name(TABLE_NAME)||
 32            ' AS SELECT * FROM HR.EMPLOYEES';
 33          EXECUTE IMMEDIATE CREATE_SQL_STMT;
 34
 35          ALTER_SQL_STMT :=
 36            'ALTER TABLE '||dbms_assert.enquote_name(TABLE_NAME)||
 37            ' ADD (SAL_COMM NUMBER)';
 38          EXECUTE IMMEDIATE ALTER_SQL_STMT;
 39
 40          CURSOR_SELECT_STMT :=
 41            'SELECT EMPLOYEE_ID, PR_SAL_AND_COMM.MAKE_MUL(SALARY, COMMISSION_PCT)'||
 42            ' FROM '||dbms_assert.enquote_name(TABLE_NAME);
 43          OPEN C1 FOR CURSOR_SELECT_STMT;
 44          FETCH C1 BULK COLLECT INTO NT;
 45          CLOSE C1;
 46
 47          UPDATE_SQL_STMT :=
 48            'UPDATE '||dbms_assert.enquote_name(TABLE_NAME)||
 49            ' SET SAL_COMM = :1 WHERE EMPLOYEE_ID = :2';
 50          FORALL I IN 1..NT.COUNT
 51              EXECUTE IMMEDIATE UPDATE_SQL_STMT USING NT(I).SAL_COMM, NT(I).EMPLOYEE_ID;
 52
 53          COMMIT;
 54
 55      END;
 56  END;
 57  /

Package body created.

SQL> EXEC PR_SAL_AND_COMM.PR_ADD_SAL_COMM('EMP20200431');

PL/SQL procedure successfully completed.
Note the use of DBMS_ASSERT package to prevent from SQL injection.

[Updated on: Wed, 15 April 2020 10:08]

Report message to a moderator

Re: Function call in ref cursor [message #679976 is a reply to message #679971] Wed, 15 April 2020 07:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or, assuming OP in on at least 12C, function MAKE_MUL can be defined in CTE:

SQL> VARIABLE V_CUR REFCURSOR
SQL> BEGIN
  2      OPEN :V_CUR
  3        FOR
  4          'WITH FUNCTION MAKE_MUL(
  5                                  P_SAL NUMBER,
  6                                  P_COMM NUMBER)
  7                  RETURN NUMBER
  8                  AS
  9                  BEGIN
 10                      RETURN P_SAL + P_SAL * NVL(P_COMM, 0);
 11                END;
 12           SELECT  ENAME,
 13                   SAL,
 14                   COMM,
 15                   MAKE_MUL(SAL,COMM) SAL_PLUS_COMM
 16             FROM  EMP';
 17  END;
 18  /

PL/SQL procedure successfully completed.

SQL> PRINT :V_CUR

ENAME             SAL       COMM SAL_PLUS_COMM
---------- ---------- ---------- -------------
SMITH             800                      800
ALLEN            1600        300        481600
WARD             1250        500        626250
JONES            2975                     2975
MARTIN           1250       1400       1751250
BLAKE            2850                     2850
CLARK            2450                     2450
SCOTT            3000                     3000
KING             5000                     5000
TURNER           1500          0          1500
ADAMS            1100                     1100

ENAME             SAL       COMM SAL_PLUS_COMM
---------- ---------- ---------- -------------
JAMES             950                      950
FORD             3000                     3000
MILLER           1300                     1300

14 rows selected.

SQL>
SY.
Re: Function call in ref cursor [message #679978 is a reply to message #679976] Wed, 15 April 2020 09:57 Go to previous messageGo to next message
Bhushan.Mahajan
Messages: 4
Registered: April 2020
Location: Mumbai India
Junior Member
Razz Hi,

Thank you for responses.
Re: Function call in ref cursor [message #679979 is a reply to message #679976] Wed, 15 April 2020 10:07 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Or, assuming OP in on at least 12C, function MAKE_MUL can be defined in CTE:
He said 11gR2 in the title (but this is a solution to keep in mind).

Previous Topic: Create Materialized View throws "ORA-01031: insufficient privileges"
Next Topic: Text to number conversion
Goto Forum:
  


Current Time: Fri Mar 29 09:44:35 CDT 2024