CODEWITHSUNDEEP
oracle-databaseplsql
Ajax
Ajax

Reputation: 150

plsql procedure is not a procedure or is undefined

I want to create a plsql procedure to calculate the factorial of a given number.

This is the procedure:

CREATE OR REPLACE PROCEDURE fact(x IN number, fact OUT number)
IS
BEGIN
while x > 0 loop
fact := x*fact;
x := x-1;
END loop;
END;
/

Warning: Procedure created with compilation errors.

and this is where I'm calling the function

DECLARE
x number := &x;
fact number  := 1;
BEGIN
fact(x,fact);
dbms_output.put_line('Factorial is: '||fact);
END;

and this is the error I'm getting:

Enter value for x: 5
old   2: x number := &x;
new   2: x number := 5;
fact(x,fact);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00221: 'FACT' is not a procedure or is undefined
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

Upvotes: 1

Views: 7357

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65138

You need to convert your creation of procedure like this :

SQL> CREATE OR REPLACE PROCEDURE fact(x IN OUT number, fact OUT number) IS
BEGIN
  while x > 0 loop
    fact := x * nvl(fact, 1);
    x    := x - 1;
  END loop;
END;
/

and call :

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  x      number := &x;
  v_fact number := 1;
BEGIN
  fact(x, v_fact);
  dbms_output.put_line('Factorial is: ' || v_fact);
END;

Factorial is: 120
  • You can not use a variable of type IN as an assignment target
  • You need to initialize a null variable fact as nvl(fact,1) or fact := 1; just before the while x > 0

Actually, you even do not need an extra parameter fact OUT number for procedure named fact, and make that as local. So, your procedure may be replaced with this :

SQL> CREATE OR REPLACE PROCEDURE fact(x IN OUT number) IS
  fact number := 1;
BEGIN
  while x > 0 loop
    fact := x * fact;
    x    := x - 1;
  end loop;
    x := fact;
END;
/

Therefore, should be invoked as :

SQL> DECLARE
  x      number := &x;
BEGIN
  fact(x);
  dbms_output.put_line('Factorial is: ' || x);
END;

Factorial is: 120

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You might consider rewriting this as a function along the lines of the following:

CREATE OR REPLACE FUNCTION fact(pinX IN INT)
  RETURN INT
IS
  nResult INT := 1;
BEGIN
  FOR i IN 2..pinX LOOP
    nResult := i * nResult;
  END LOOP;

  RETURN nResult;
END FACT;

dbfiddle here

Best of luck.

Upvotes: 1

Related Questions