0

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?

MT0
  • 143,790
  • 11
  • 59
  • 117
TMK
  • 61
  • 1
  • 7

2 Answers2

2

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.

APC
  • 144,005
  • 19
  • 170
  • 281
1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Perhaps everything you wrote is correct (I didn't read) - but how is that related to the OP's question? Suppose for example he is practicing writing PL/SQL blocks - how will a SELECT statement, even if correct, help with that? Moreover, it seems the problem is that LiveSQL doesn't understand substitution variables; if he tried the same thing with a SELECT statement, he would run into the same problem. I don't find this answer to be helpful. –  Oct 17 '20 at 17:52