| How to call compiled procedure and function in a procedure [message #677465] |
Sun, 22 September 2019 12:27  |
 |
oluranti
Messages: 9
Registered: September 2019
|
Junior Member |
|
|
I have compiled a procedure and a function and want to use both in a new procedure but got the 'ORA-00904: : invalid identifier' error.
My first procedure:
CREATE OR REPLACE PROCEDURE NUM_ADD(P_NUM1 NUMBER,P_NUM2 NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(P_NUM1 + P_NUM2);
END;
/
CREATE OR REPLACE FUNCTION NUM_MULTI(F_NUM1 NUMBER,F_NUM2 NUMBER,F_NUM3 NUMBER)
RETURN NUMBER
AS
V_NUM NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(F_NUM1 * F_NUM2 * F_NUM3);
RETURN V_NUM;
END;
/
CREATE OR REPLACE PROCEDURE NUM_ADD_MULTI
AS
V_1 NUMBER;
V_2 NUMBER;
BEGIN
BEGIN
SELECT NUM_MULTI(2,2,2) INTO V_1 FROM DUAL;
BEGIN
SELECT NUM_ADD(3,3) INTO V_2 FROM DUAL;
END;
END;
DBMS_OUTPUT.PUT_LINE(V_1 - V_2);
END;
/
[EDITED by LF: applied [code] tags]
[Updated on: Sun, 22 September 2019 13:13] by Moderator Report message to a moderator |
|
|
|
| Re: How to call compiled procedure and function in a procedure [message #677466 is a reply to message #677465] |
Sun, 22 September 2019 13:14  |
 |
Michel Cadot
Messages: 68417
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.
Indent the code, use code tags and align the columns in result.
SQL> CREATE OR REPLACE PROCEDURE NUM_ADD(P_NUM1 NUMBER,P_NUM2 NUMBER)
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(P_NUM1 + P_NUM2);
5 END;
6 /
Procedure created.
SQL> CREATE OR REPLACE FUNCTION NUM_MULTI(F_NUM1 NUMBER,F_NUM2 NUMBER,F_NUM3 NUMBER)
2 RETURN NUMBER
3 AS
4 V_NUM NUMBER;
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE(F_NUM1 * F_NUM2 * F_NUM3);
7 RETURN V_NUM;
8 END;
9 /
Function created.
SQL> CREATE OR REPLACE PROCEDURE NUM_ADD_MULTI
2 AS
3 V_1 NUMBER;
4 V_2 NUMBER;
5 BEGIN
6 BEGIN
7 SELECT NUM_MULTI(2,2,2) INTO V_1 FROM DUAL;
8
9 BEGIN
10 SELECT NUM_ADD(3,3) INTO V_2 FROM DUAL;
11
12 END;
13 END;
14 DBMS_OUTPUT.PUT_LINE(V_1 - V_2);
15 END;
16 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE NUM_ADD_MULTI:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------
10/1 PL/SQL: SQL Statement ignored
10/8 PL/SQL: ORA-00904: : invalid identifier
In short, you can't use a procedure in SQL, only functions.
|
|
|
|
| Re: How to call compiled procedure and function in a procedure [message #677467 is a reply to message #677465] |
Sun, 22 September 2019 13:22 |
 |
Littlefoot
Messages: 21759
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
NUM_ADD is a procedure so you don't SELECT it, but call it, i.e. instead of
SELECT NUM_ADD(3,3) INTO V_2 FROM DUAL;
Though, that wouldn't do anything as NUM_ADD procedure just displays information and doesn't return anything. The same goes for NUM_MULTI function which returns NULL as you didn't store anything into V_NUM variable.
I suppose that you meant to do something like this:
SQL> create or replace procedure num_add(
2 p_num1 number,
3 p_num2 number,
4 p_sum out number
5 )
6 is
7 begin
8 p_sum := p_num1 + p_num2;
9 end;
10 /
Procedure created.
SQL> create or replace function num_multi(
2 f_num1 number,
3 f_num2 number,
4 f_num3 number
5 )
6 return number as
7 v_num number;
8 begin
9 v_num := f_num1 * f_num2 * f_num3;
10 return v_num;
11 end;
12 /
Function created.
SQL> create or replace procedure num_add_multi as
2 v_1 number;
3 v_2 number;
4 begin
5 v_1 := num_multi(2,2,2);
6 num_add(3,3,v_2);
7 dbms_output.put_line(v_1 - v_2);
8 end;
9 /
Procedure created.
SQL> set serveroutput on
SQL> exec num_add_multi;
2
PL/SQL procedure successfully completed.
SQL>
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to call compiled procedure and function in a procedure [message #677479 is a reply to message #677477] |
Mon, 23 September 2019 05:19 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
oluranti wrote on Mon, 23 September 2019 10:42NUM_ADD is number
That's not the definition of the procedure.
This is:
CREATE OR REPLACE PROCEDURE NUM_ADD(P_NUM1 NUMBER,P_NUM2 NUMBER)
Then there's LF's version:
create or replace procedure num_add(
2 p_num1 number,
3 p_num2 number,
4 p_sum out number
That's what you need to show us.
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
