Home » SQL & PL/SQL » SQL & PL/SQL » How to call compiled procedure and function in a procedure (Toad 11.5 with Oracle 10g)
How to call compiled procedure and function in a procedure [message #677465] Sun, 22 September 2019 12:27 Go to next message
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;
/
My function:
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;
/
The new procedure to use the first procedure and the function:
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;      
/
Please assist as I'm new in using PL SQL.


[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 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.
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
NUM_ADD is a procedure and so has no return value, SQL expects a function there to use this returned value.
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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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;
you'd just
NUM_ADD(3, 3);

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 #677474 is a reply to message #677467] Mon, 23 September 2019 03:44 Go to previous messageGo to next message
oluranti
Messages: 9
Registered: September 2019
Junior Member
Thanks for the help. I tired the code in Toad but still got an error.

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 /
Error at line 6:PLS-00306:wrong number or types of arguments in call to 'NUM_ADD'.
Re: How to call compiled procedure and function in a procedure [message #677475 is a reply to message #677474] Mon, 23 September 2019 03:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
"PLS-00306:wrong number or types of arguments in call to" means you are either passing the wrong number of arguements (paramters) or you are passing arguements of the wrong datatype.
So what's the current definition of num_add?
Re: How to call compiled procedure and function in a procedure [message #677477 is a reply to message #677475] Mon, 23 September 2019 04:42 Go to previous messageGo to next message
oluranti
Messages: 9
Registered: September 2019
Junior Member
NUM_ADD is number
Re: How to call compiled procedure and function in a procedure [message #677478 is a reply to message #677477] Mon, 23 September 2019 04:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you read what I've written? NUM_ADD now has the 3rd parameter:

 p_sum    out      number
Did you add it to your procedure?
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
oluranti wrote on Mon, 23 September 2019 10:42
NUM_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)
That's your original - it shows the number of parameters (2) and the datatype of each (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 got three, and one is an out parameter.

That's what you need to show us.
Re: How to call compiled procedure and function in a procedure [message #677490 is a reply to message #677478] Mon, 23 September 2019 10:22 Go to previous messageGo to next message
oluranti
Messages: 9
Registered: September 2019
Junior Member
Yes, it worked. Thanks for the support.
Re: How to call compiled procedure and function in a procedure [message #677491 is a reply to message #677490] Mon, 23 September 2019 10:25 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So I assume you hadn't updated num_add?
Previous Topic: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
Next Topic: RETURNING clause returns wrong value upon UPDATE statement
Goto Forum:
  


Current Time: Fri Mar 29 09:05:06 CDT 2024