Getting a PLS-00103 error while excecting a program to find factorial in PL/SQL

Question

So this is my code which I executed in sqllive online ide.

declare 
    fac number := 1;    
    n number := &1;  
begin         
    while n > 0 loop   
        fac := n*fac;         
        n := n-1;
    end loop;          

    dbms_output.put_line(fac);   
end;           

Its giving me a "PLS-00103: Encountered the symbol "&" when expecting one of the following:" error

What is wrong with it?

Answer 1

SQL Live is not SQL*Plus. The ampersand is for SQL*Plus substitution variables and does not work in SQL Live. You need to edit your anonymous block and supply a value each time before you run it.

Answer 2

I don't really see the need for a pl/sql loop here. You could very well do this with a recursive query:

with cte (n, fac) as (
    select ? n, 1 fac from dual
    union all 
    select n - 1, fac * n from cte where n > 1
)
select max(fac) as result from cte

The question mark represents the parameter for the query, that is the number whose factorial you want to compute.

Demo on DB Fiddle: when given parameter 4 for example, the query returns:

<pre>
| RESULT |
| -----: |
|     24 |
</pre>

... which is the result of computation 1 * 2 * 3 * 4.

You could also phrase this as:

with cte (n, fac) as (
    select 4 n, 4 fac from dual
    union all 
    select n - 1, fac * (n - 1) from cte where n > 1
)
select max(fac) as result from cte