-1

This is the Oracle 11g PL/SQL code I am trying to run:

declare
    a char;
    count_n number(4):=0;
    average number(4):=0;
    sum_n number(4):=0;
begin
    loop
        a:=&a;
        if a='Z' then
            goto end_input;
            exit;
        end if;
        count_n:=count_n+1;
        sum_n:=sum_n+to_number(a);
        end loop;
        <<end_input>>
       average:=sum_n/count_n;
end;
/

I am getting this error: ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 13

Can anyone help me out please?

xQbert
  • 34,733
  • 2
  • 41
  • 62
Mayank Aggarwal
  • 139
  • 1
  • 4
  • 14
  • A `number(4)` variable cannot hold a number larger than 9999. You could just remove the `(4)` from the declarations, then they will allow very large numbers indeed. – Tony Andrews Feb 25 '16 at 16:53
  • 1
    What are you trying to accomplish? `&a` is a substitution variable that SQL*Plus will prompt for exactly once before the anonymous block is submitted to the database. PL/SQL cannot prompt the user for input in a loop which is what I'm guessing you are trying to accomplish. – Justin Cave Feb 25 '16 at 16:53
  • 1
    when does &a get reset in your loop? since it doesn't it will never equal z and your count quickly exceeds 9999 – xQbert Feb 25 '16 at 16:54
  • How to take input in a in the loop? – Mayank Aggarwal Feb 25 '16 at 16:58
  • Probably you have to specify an exit condition; for example EXIT WHEN sum_n = 1000; end loop; – user1 Feb 25 '16 at 16:58
  • 1
    You can't take input in a PL/SQL block, in a loop or not. As Justin said the &a substitution is done before the block is compiled and executed, not during it running. It isn't designed to be interactive. You could potentially use recursive calls to scripts to perform this sort of thing but it's messy. This is what client applications are for; why are you trying to do this in the database? – Alex Poole Feb 25 '16 at 17:06
  • I thought I'd done something like this before but [this](http://stackoverflow.com/a/34789647/266304) is the closest I can find; it isn't recursive but might give you some pointers anyway. Actually [this](http://stackoverflow.com/a/1870887/266304) or [this](http://stackoverflow.com/a/1873019/266304) (both answers to the same question) might be closer to what you need. But a front-end scripting language would be better and easier. – Alex Poole Feb 25 '16 at 17:29

1 Answers1

0

Quote your parameter like this :

declare
    a char;
    count_n number(4):=0;
    average number(4):=0;
    sum_n number(4):=0;
begin
    loop
        a:='&a';
        if a='Z' then
            goto end_input;
            exit;
        end if;
        count_n:=count_n+1;
        sum_n:=sum_n+to_number(a);
        end loop;
        <<end_input>>
       average:=sum_n/count_n;
end;
/
Marco Polo
  • 728
  • 5
  • 10