Initialize and loop through twodimensional array

Question

I'm trying to write a diff procedure in pl/sql. The error message never says what the structure should be like, only it expected some other symbol, usually that would indicate the end of the line. Also I keep getting errors on example code...

Why does it stop at the first error instead of giving a list of all errors found?
CREATE TYPE EditCost, found EditCost, expecting some other symbol.
cost_matrix INTEGER[, found [, expecting some other symbol.
original.length, invalid reference.
cost_matrix[, found [, expecting some other symbol.
return cost_matrix, no value allowed after return, then how am I to return values?

This code gives me many errors, I removed parts only to find errors in other parts.

CREATE OR REPLACE PROCEDURE diff_string(original in varchar2, other in varchar2, result out varchar2)
is -- Maybe should be as, don't know.
    CREATE TYPE EditCost { 'Change', 'Copy', 'Delete', 'Insert', 'Kill' };
    cost_matrix INTEGER[original.length, other.length];
BEGIN
   IF original = NULL THEN
      result := other;
   ELSIF other = NULL then
      result := original;
   else
       for i in 1 .. length(original)
       loop
           for j in 1 .. length(other)
           loop
               cost_matrix[i, j] := i * j;
           end loop;
       end loop;
       return cost_matrix;
   end if;
END diff_string;
/

Nick Krasnov · Accepted Answer · 2012-10-10 14:28:37Z

2

Collections are one-dimensional structure. You can implement multi-dimensional arrays by creating collection whose elements are also collection.

Here is an example of how you can implement two dimensional array(matrix):

SQL> set serveroutput on;
SQL> 
SQL> create or replace procedure Matrix(p_m in number, p_n in number)
  2  is
  3    type T_Array is table of integer;
  4    type T_Matrix is table of T_Array;
  5    l_Matrix T_Matrix := T_Matrix();
  6    l_m varchar2(101);
  7  begin
  8    for i in 1..p_m
  9    loop
 10      l_m  := '';
 11      l_matrix.extend;
 12      l_Matrix(i) := T_Array();
 13      for y in 1..p_n
 14      loop
 15        l_matrix(i).extend;
 16        l_matrix(i)(y) := y;
 17        l_m := l_m || ' | ' || To_Char(l_matrix(i)(y));
 18      end loop;
 19      dbms_output.put_line(l_m);
 20    end loop;
 21  end;
 22  /

Procedure created

SQL> exec matrix(5,5);

 | 1 | 2 | 3 | 4 | 5
 | 1 | 2 | 3 | 4 | 5
 | 1 | 2 | 3 | 4 | 5
 | 1 | 2 | 3 | 4 | 5
 | 1 | 2 | 3 | 4 | 5

PL/SQL procedure successfully completed

SQL> exec matrix(5,7);

 | 1 | 2 | 3 | 4 | 5 | 6 | 7
 | 1 | 2 | 3 | 4 | 5 | 6 | 7
 | 1 | 2 | 3 | 4 | 5 | 6 | 7
 | 1 | 2 | 3 | 4 | 5 | 6 | 7
 | 1 | 2 | 3 | 4 | 5 | 6 | 7

Find out more about collections here and here

Response to the comment

You can successfully use nested table as well. So your code might look like this:

SQL> set serveroutput on;
SQL> 
SQL> CREATE OR REPLACE PROCEDURE diff_string(original in varchar2, other in varchar2, result out varchar2)
  2  is
  3    type T_Array is table of integer;
  4    type T_Matrix is table of T_Array;
  5    cost_matrix T_Matrix := T_Matrix();
  6    --antwoord T_Matrix;
  7    l_res varchar2(301);
  8  BEGIN
  9     IF original is NULL THEN
 10        result := other;
 11     ELSIF other is NULL then
 12        result := original;
 13     else
 14         for i in 1 .. length(original)
 15         loop
 16             l_res := '';
 17             cost_matrix.extend;
 18             cost_matrix(i) := T_Array();
 19             for j in 1 .. length(other)
 20             loop
 21                cost_matrix(i).extend;
 22                cost_matrix(i)(j) := i * j;
 23                l_res := l_res || ' | ' || To_char(cost_matrix(i)(j));
 24            end loop;
 25             dbms_output.put_line(l_res);
 26         end loop;
 27     end if;
 28  END diff_string;
 29  /

Procedure created

SQL> variable res varchar2(31);
SQL> exec diff_string('String1', 'String2', :res);

 | 1 | 2  | 3  | 4  | 5  | 6  | 7
 | 2 | 4  | 6  | 8  | 10 | 12 | 14
 | 3 | 6  | 9  | 12 | 15 | 18 | 21
 | 4 | 8  | 12 | 16 | 20 | 24 | 28
 | 5 | 10 | 15 | 20 | 25 | 30 | 35
 | 6 | 12 | 18 | 24 | 30 | 36 | 42
 | 7 | 14 | 21 | 28 | 35 | 42 | 49

PL/SQL procedure successfully completed
res
---------

edited Oct 10, 2012 at 14:28

answered Oct 10, 2012 at 10:23

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MrFox Over a year ago

Ok thanks but it still says ORA-06533: Subscript beyond count. So how do I increase the array size so it can actually hold a value? I already tried moving the extend forward to before assigning values.

MrFox Over a year ago

It helps, but still many of my question are not answered, Also it refuses to print out the table contents. But really what I want is to view the table inside the procedure, so debugging, is this possible?

MrFox Over a year ago

let us continue this discussion in chat

MrFox · Accepted Answer · 2012-10-10 12:13:25Z

I actually managed to remove the errors by using VARRAY. But it does not allow dynamic sizing (that is VARRAY(length(original)) or VARRAY(original.length) are not allowed. So now if someone gives an argument bigger than 10 it will fail, also I don't get any results. Was hoping "antwoord := cost_matrix" would make it show the resulting values. Is there another way to do this?

CREATE OR REPLACE PROCEDURE diff_string(original in varchar2, other in varchar2, result out varchar2)
is -- Maybe should be as, don't know.

    type T_Array is VARRAY(10) of integer;
    type T_Matrix is VARRAY(10) of T_Array;
    cost_matrix T_Matrix := T_Matrix(null);
    antwoord T_Matrix;
BEGIN
   IF original = NULL THEN
      result := other;
   ELSIF other = NULL then
      result := original;
   else

       for i in 1 .. length(original)
       loop
           cost_matrix.extend;
           cost_matrix(i) := T_Array(null);
           for j in 1 .. length(other)
           loop
               cost_matrix(i).extend;
               cost_matrix(i)(j) := i * j;
           end loop;
       end loop;
       antwoord := cost_matrix;
   end if;
END diff_string;
/

CALL diff_string('hoi', 'hob', :verschil);

Collectives™ on Stack Overflow

Initialize and loop through twodimensional array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related