Function call in ref cursor [message #679950] |
Tue, 14 April 2020 11:12 |
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 |
|
Michel Cadot
Messages: 68653 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 |
Solomon Yakobson
Messages: 3273 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 #679971 is a reply to message #679969] |
Wed, 15 April 2020 02:55 |
|
Michel Cadot
Messages: 68653 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 |
Solomon Yakobson
Messages: 3273 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 #679979 is a reply to message #679976] |
Wed, 15 April 2020 10:07 |
|
Michel Cadot
Messages: 68653 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).
|
|
|