Looping in PL/SQL variable is not a cursor

Question

I'm trying to run the following loop:

DECLARE
v_banknumber varchar2(9) := '123456789';
v_counter number := 9;
v_result number;
begin
for i in v_banknumber 
loop
    v_result := v_counter * TO_NUMBER(i) + v_result;
    v_counter := v_counter - 1;
 end loop;
 end;

I'm getting a error at line 2:

Error report -
ORA-06550: line 6, column 10:
PLS-00456: item 'V_BANKNUMBER' is not a cursor
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

If I read this well, it seems like it should work. Anyone here that can explain me why it's not working?

The first digit must be multiplied by 9, the second with 8, the third with 7, and so on and save the sum of it in a result variable.

Answer 1

Did you mean to do it as an array?

DECLARE
  type array_t is varray(9) of number;
  a_banknumber array_t := array_t (1,2,3,4,5,6,7,8,9);
  v_counter number := a_banknumber.count;
  v_result number := 0;
begin

  for i in 1..a_banknumber.count  
  loop
    v_result := v_counter * a_banknumber(i) + v_result;
    v_counter := v_counter - 1;
  end loop;
  dbms_output.put_line('Result: ' || v_result);
end;

Answer 2

At a guess, what you want to do is

DECLARE
  v_banknumber varchar2(9) := '123456789';
  v_counter number := 9;
  v_result number := 0;
begin
  for i in 1..LENGTH(v_banknumber)
  loop
    v_result := v_counter * TO_NUMBER(SUBSTR(v_banknumber, i, 1)) + v_result;
    v_counter := v_counter - 1;
   end loop;
 end;

This gives a result of 165.

Best of luck.

EDIT

Or you could really use a cursor:

DECLARE
  v_banknumber varchar2(9) := '123456789';
  v_counter number := 9;
  v_result number := 0;
begin
  for aRow in (SELECT LEVEL AS I FROM DUAL CONNECT BY LEVEL <= LENGTH(v_banknumber)) 
  loop
    v_result := v_counter * TO_NUMBER(SUBSTR(v_banknumber, aRow.I, 1)) + v_result;
    v_counter := v_counter - 1;
   end loop;
 end;

Produces 165 as the result.

EDIT #2

Or, because there's no kill like overkill, you could just do it all in SQL:

WITH cteBank_number AS (SELECT '123456789' AS BANK_NUMBER FROM DUAL),
     cteI AS (SELECT LEVEL AS I
                FROM DUAL d
                CROSS JOIN cteBank_number b
                CONNECT BY LEVEL <= LENGTH(b.BANK_NUMBER)),
     cteNums AS (SELECT TO_NUMBER(SUBSTR(b.BANK_NUMBER, LENGTH(b.BANK_NUMBER)-i.I+1, 1)) AS DIGIT,
                        i.I AS I,
                        TO_NUMBER(SUBSTR(b.BANK_NUMBER, LENGTH(b.BANK_NUMBER)-i.I+1, 1)) * i.I AS NUM
                   FROM cteBank_number b
                   CROSS JOIN cteI i)
SELECT SUM(NUM)
  FROM cteNums n;

Still produces 165 as the result.

Answer 3

Your v_banknumber variable is a string not a cursor. You need to loop over each character in that string, and treat that character as a digit.

You could do this as:

 set serveroutput on

declare
  v_banknumber varchar2(9) := '123456789';
  v_result number := 0;
begin
  for v_counter in reverse 1..length(v_banknumber)
  loop
    v_result := v_result
      + (v_counter * to_number(substr(v_banknumber, -v_counter, 1)));
  end loop;

  dbms_output.put_line('The result is: ' || v_result);
end;
/

The result is: 165

PL/SQL procedure successfully completed.

With extra debugs to try to show what is happening on each iteration:

declare
  v_banknumber varchar2(9) := '123456789';
  v_result number := 0;
begin
  dbms_output.put_line('length(v_banknumber) is: ' || length(v_banknumber));
  for v_counter in reverse 1..length(v_banknumber)
  loop
    dbms_output.put_line('v_counter is: ' || v_counter);
    dbms_output.put_line('  Digit is substr(v_banknumber, v_counter, 1): '
      || substr(v_banknumber, -v_counter, 1));
    dbms_output.put_line('  Calculation for digit is: '
      || v_counter * to_number(substr(v_banknumber, -v_counter, 1)));
    v_result := v_result
      + (v_counter * to_number(substr(v_banknumber, -v_counter, 1)));
    dbms_output.put_line('  Running total: ' || v_result);
  end loop;

  dbms_output.put_line('The result is: ' || v_result);
end;
/

length(v_banknumber) is: 9
v_counter is: 9
  Digit is substr(v_banknumber, v_counter, 1): 1
  Calculation for digit is: 9
  Running total: 9
v_counter is: 8
  Digit is substr(v_banknumber, v_counter, 1): 2
  Calculation for digit is: 16
  Running total: 25
v_counter is: 7
  Digit is substr(v_banknumber, v_counter, 1): 3
  Calculation for digit is: 21
  Running total: 46
v_counter is: 6
  Digit is substr(v_banknumber, v_counter, 1): 4
  Calculation for digit is: 24
  Running total: 70
v_counter is: 5
  Digit is substr(v_banknumber, v_counter, 1): 5
  Calculation for digit is: 25
  Running total: 95
v_counter is: 4
  Digit is substr(v_banknumber, v_counter, 1): 6
  Calculation for digit is: 24
  Running total: 119
v_counter is: 3
  Digit is substr(v_banknumber, v_counter, 1): 7
  Calculation for digit is: 21
  Running total: 140
v_counter is: 2
  Digit is substr(v_banknumber, v_counter, 1): 8
  Calculation for digit is: 16
  Running total: 156
v_counter is: 1
  Digit is substr(v_banknumber, v_counter, 1): 9
  Calculation for digit is: 9
  Running total: 165
The result is: 165