MultiDimensional Array
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: MultiDimensional Array

  1. #1
    Join Date
    Nov 2001
    Posts
    55

    Question

    All,

    Someone please help me. I am trying to receive an array of 5 columns and 20 rows into a procedure. Please help me with some sample code of PACKAGE & PACKAGE BODY.

    ThanX
    Raj

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    If you are talking about a PL/SQL table you'll need something like this:

    CREATE OR REPLACE PACKAGE definitions AS

    TYPE my_record_type IS RECORD (
    col1 number(10),
    col2 number(10),
    col3 number(10),
    col4 number(10),
    col5 number(10));

    TYPE my_table_type IS TABLE OF my_record_type INDEX BY BINARY_INTEGER;

    END Definitions;
    /

    CREATE OR REPLACE PACKAGE table_param AS

    PROCEDURE call;
    PROCEDURE receive (p_table IN definitions.my_table_type);

    END table_param;
    /

    CREATE OR REPLACE PACKAGE BODY table_param AS

    PROCEDURE call IS
    my_table definitions.my_table_type;
    BEGIN
    FOR i IN 1 .. 20 LOOP
    my_table(i).col1 := i*1;
    my_table(i).col2 := i*2;
    my_table(i).col3 := i*3;
    my_table(i).col4 := i*4;
    my_table(i).col5 := i*5;
    END LOOP;

    receive (p_table => my_table);
    END call;

    PROCEDURE receive (p_table IN definitions.my_table_type) IS
    BEGIN
    FOR i IN 1 .. p_table.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col1 := ' || p_table(i).col1);
    DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col2 := ' || p_table(i).col2);
    DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col3 := ' || p_table(i).col3);
    DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col4 := ' || p_table(i).col4);
    DBMS_OUTPUT.PUT_LINE('p_table(' || i || ').col5 := ' || p_table(i).col5);
    END LOOP;
    END receive;

    END table_param;
    /


    SET SERVEROUTPUT ON SIZE 1000000

    EXEC table_param.call;

    Cheers!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Nov 2001
    Posts
    55
    ThanX a lot. U guys r great.

    I would love to see a varray sample too.

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    It's almost identical:

    CREATE OR REPLACE PACKAGE definitions AS

    TYPE my_record_type IS RECORD (
    col1 number(10),
    col2 number(10),
    col3 number(10),
    col4 number(10),
    col5 number(10));

    TYPE my_varray_type IS VARRAY(20) OF my_record_type;

    END Definitions;
    /

    CREATE OR REPLACE PACKAGE varray_param AS

    PROCEDURE call;
    PROCEDURE receive (p_varray IN definitions.my_varray_type);

    END varray_param;
    /

    CREATE OR REPLACE PACKAGE BODY varray_param AS

    PROCEDURE call IS
    my_varray definitions.my_varray_type := definitions.my_varray_type();
    BEGIN
    FOR i IN 1 .. 20 LOOP
    my_varray.extend;
    my_varray(i).col1 := i*1;
    my_varray(i).col2 := i*2;
    my_varray(i).col3 := i*3;
    my_varray(i).col4 := i*4;
    my_varray(i).col5 := i*5;
    END LOOP;

    receive (p_varray => my_varray);
    END call;

    PROCEDURE receive (p_varray IN definitions.my_varray_type) IS
    BEGIN
    FOR i IN 1 .. p_varray.count LOOP
    DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col1 := ' || p_varray(i).col1);
    DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col2 := ' || p_varray(i).col2);
    DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col3 := ' || p_varray(i).col3);
    DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col4 := ' || p_varray(i).col4);
    DBMS_OUTPUT.PUT_LINE('p_varray(' || i || ').col5 := ' || p_varray(i).col5);
    END LOOP;
    END receive;

    END varray_param;
    /


    SET SERVEROUTPUT ON SIZE 1000000

    EXEC varray_param.call;

    Cheers!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

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