Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2004
    Posts
    76

    Unanswered: Using Package VARRAY variable in procedures

    Hi All,

    I am puzzled using VARRAY variables defined in a Package

    I understand that Package variables can be used across Procedures and the values are global. i.e It is possible to assign a value to a Package variable in one procedure and access its value in another procedure.

    But is it possible to use a variable of a VARRAY type across different procedures? I tried it but Initialization of VARRAY variable is required in all procedures thus making it as a local variable.

    Please let me know if my understanding is correct.

    Thanks,
    cheeku

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, it is global:
    Code:
    SQL> create or replace package test_pkg as
      2    procedure p1;
      3    procedure p2;
      4  end;
      5  /
    
    Package created.
    
    SQL> create or replace package body test_pkg as
      2    type va_t is varray(10) of number;
      3    va va_t := va_t();
      4    procedure p1 is
      5    begin
      6      for i in 1..10 loop
      7        va.extend;
      8        va(i) := i*2;
      9      end loop;
     10    end;
     11    procedure p2 is
     12    begin
     13      for i in 1..va.count loop
     14        dbms_output.put_line(va(i));
     15      end loop;
     16    end;
     17  end;
     18  /
    
    Package body created.
    
    SQL> exec test_pkg.p1
    
    PL/SQL procedure successfully completed.
    
    SQL> exec test_pkg.p2
    2
    4
    6
    8
    10
    12
    14
    16
    18
    20
    
    PL/SQL procedure successfully completed.

  3. #3
    Join Date
    Dec 2004
    Posts
    7

    Example

    I houp that example will help you ..

    package TEST_ARRAY;
    procedure TEST;

    CREATE OR REPLACE PACKAGE TEST_ARRAY
    IS
    TYPE T_Array IS VARRAY(10) OF NUMBER;
    myArray T_Array;
    PROCEDURE SET_VALUE;
    FUNCTION GET_VALUE(element_id IN NUMBER) RETURN NUMBER;
    PROCEDURE SET_VALUE(element_id IN NUMBER,new_value IN NUMBER);
    END TEST_ARRAY;
    /
    CREATE OR REPLACE PACKAGE BODY TEST_ARRAY
    IS

    FUNCTION GET_VALUE(element_id IN NUMBER) RETURN NUMBER IS
    BEGIN
    RETURN (myArray(element_id));
    END GET_VALUE;

    PROCEDURE SET_VALUE(element_id IN NUMBER,new_value IN NUMBER) IS
    BEGIN
    myArray(element_id):=new_value;
    END;

    PROCEDURE SET_VALUE IS
    BEGIN
    myArray:=T_Array(null,null,null,null,null,null,nul l,null,null,null);
    FOR i IN 1..10 LOOP
    myArray(i):=i*100;
    END LOOP;
    END SET_VALUE;
    BEGIN
    SET_VALUE;
    END TEST_ARRAY;
    /

    CREATE OR REPLACE PROCEDURE TEST IS
    result NUMBER;
    BEGIN

    dbms_output.put_line('Get initial value for element 5 ...');
    result:=test_array.GET_VALUE(5);
    dbms_output.put_line('Result '||result);
    dbms_output.put_line('Set new value for element 5 ...');
    test_array.SET_VALUE(5,555);
    dbms_output.put_line('Get new value for element 5 ...');
    result:=test_array.GET_VALUE(5);
    dbms_output.put_line('Result '||result);

    END;
    /

  4. #4
    Join Date
    Aug 2004
    Posts
    76

    thanks jeny and tony

    from your examples I believe that the VARRAY can be global under below condition:

    * When you initialize the variable in a Package Body

    correct me if my understanding is correct.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by cheeku
    from your examples I believe that the VARRAY can be global under below condition:

    * When you initialize the variable in a Package Body

    correct me if my understanding is correct.
    But then again, that's the only place where they should be.

  6. #6
    Join Date
    Dec 2004
    Posts
    7
    I think that you can create your own type with CREATE TYPE command. Then you can pass variables of this type between different procedures as arguments.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by cheeku
    from your examples I believe that the VARRAY can be global under below condition:

    * When you initialize the variable in a Package Body

    correct me if my understanding is correct.
    How are you initialising yours then? Without seeign your code it is hard to visualise your problem.

  8. #8
    Join Date
    Aug 2004
    Posts
    76
    I had been using Index by tables in my Packages and was curious to know more about VARRAY as am in the learning process. Any way thanks for all your inputs which are indeed very useful and helped me to great extent.

    -cheeku

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    It's like the more useful Nested Table type but with an awkward length limit that makes it more fiddly to work with. Possibly it has uses when creating database tables or views containing array columns. I've not yet found a use for VARRAYs in PL/SQL.

Posting Permissions

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