2 d arrays????
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: 2 d arrays????

  1. #1
    Join Date
    Jul 2000
    Posts
    53
    Hi All,

    I am trying to construct a variable of, what would be in C++ called a 2 dimensional array.

    I need to be able to store maybe the name and age of many people in an area (memory) temp tablespace?? And then manipulate those.

    I have looked into using VARRAY and nested tables. I have written a little plsql program to try and get two values from a table and write them to the screen as a test. I can't seem to do this by referencing what I think is the subscript and element of that array element.

    So for example:


    TYPE p_emp_type IS TABLE OF emp%ROWTYPE;
    p_emp_tab p_emp_type; -- variable declaration--------------------------
    cursor c1 is
    select emp_id, age
    from emp
    where emp_id in (888888, 3333);
    exit_count number := 0;
    BEGIN
    dbms_output.put_line('Started : ');
    for i in c1
    loop
    p_emp_tab(i).emp_id := i.emp_id;
    p_emp_tab(i).age := i.age;
    end loop;
    dbms_output.put_line('finished loop : ');
    loop
    exit_count := exit_count + 1;
    dbms_output.put_line('empid: ' ||p_emp_tab(exit_count).emp_id || ' age: ' || p_emp_tab(exit_count).age);
    if exit_count > 10 then
    exit;
    end if;
    end loop;
    END;

    I am getting errors to the effect of

    ERROR at line 25:
    ORA-06550: line 25, column 21

    Can anyone help please?????

    Even if this is not correct can anyone help me to achieve what I am trying please?

    Linked list or 2 dimensional array???

    Thanks very much in advance.
    Steve

  2. #2
    Join Date
    Feb 2001
    Posts
    125
    Hi,

    Your procedure has been partially modified. check if it will
    do.

    First crete a Object Type

    SQL> create type empstr as object
    2 (empid number(6),
    3 age number(4));
    4 /

    Type created.


    -- follwing is pl/sql block

    declare
    -- Create a type for above empstr object
    Type p_emp_type is table of empstr ;

    -- declare a variable on type and initilize.
    p_emp_tab p_emp_type := p_emp_type(empstr(10,20));

    cursor c1 is
    select
    emp_id, age
    from
    emp
    where
    emp_id in (888888, 3333);
    exit_count number := 0;

    -- extra variable added for counter in "for loop"
    ctr number := 1;
    BEGIN
    dbms_output.put_line('Started : ');
    for i in c1
    loop
    -- counter variable "ctr" is used here
    p_emp_tab(ctr).emp_id := i.emp_id;
    p_emp_tab(ctr).emp_id := i.age;
    ctr := ctr + 1;
    end loop;

    dbms_output.put_line('finished loop : ');
    loop
    exit_count := exit_count + 1;
    dbms_output.put_line('empid: ' ||p_emp_tab(exit_count).emp_id || ' age: ' || p_emp_tab(exit_count).age);

    -- old condition was exit_count > 10
    if exit_count >= ctr-1 then
    exit;
    end if;
    end loop;
    END;


    run this procedure you will find output like this

    Started :
    finished loop :
    empid: 10 age: 20

    PL/SQL procedure successfully completed.



    Thanks
    P. Soni

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width