1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to select varray data

Discussion in 'SQL PL/SQL' started by bangla123, Jul 5, 2012.

  1. bangla123

    bangla123 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    1.
    CREATE TYPE ITProject AS OBJECT (
    p_no NUMBER(2),
    title VARCHAR2(35),
    cost NUMBER(7,2),
    manager varchar2(23));
    CREATE TYPE AllProject AS VARRAY(60) OF ITProject;

    2. CREATE TABLE deptIT (
    dept_id NUMBER(2),
    name VARCHAR2(15),
    budget NUMBER(11,2),
    projects AllProject );

    3.
    BEGIN
    INSERT INTO deptIT VALUES(1, 'Comp Science', 100000,
    AllProject (ITProject (1, 'Hire Developer', 70000.8, 'John'),
    ITProject (2, 'buy hardwares', 3456.5, 'Mike')));
    INSERT INTO deptIT
    VALUES(2, 'Network setup', 100000,
    AllProject (ITProject (1, 'Hire network engineer', 87655, 'Kevin'),
    ITProject (2, 'buy cables and servers', 87650, 'Sean')));
    END;


    4.
    declare
    addcourses AllProject :=
    AllProject (ITProject (1, 'Hire network engineer', 87655, 'Kevin'),
    ITProject (2, 'buy cables and servers', 87650, 'Sean'),
    ITProject (3, 'Buy software license', 87630, 'Jim'));

    begin
    update deptIT
    set projects = addcourses
    where name = 'Network setup';
    end;

    5. I am trying to select data from the varray my_projects. How do I select the data in sql or plsql?

    DECLARE
    my_projects AllProject ;
    BEGIN
    SELECT projects INTO my_projects FROM deptIT
    where name = 'Network setup';

    DBMS_OUTPUT.PUT_LINE(my_projects( ITProject.p_no) );
    END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to reference the record in the varray and you're not doing that; examine this modification to your example:

    Code (SQL):
    SQL> CREATE TYPE ITProject AS OBJECT (
      2          p_no NUMBER(2),
      3          title VARCHAR2(35),
      4          cost NUMBER(7,2),
      5          manager varchar2(23));
      6  /
     
    TYPE created.
     
    SQL>
    SQL> CREATE TYPE AllProject AS VARRAY(60) OF ITProject;
      2  /
     
    TYPE created.
     
    SQL>
    SQL> CREATE TABLE deptIT (
      2          dept_id NUMBER(2),
      3          name VARCHAR2(15),
      4          budget NUMBER(11,2),
      5          projects AllProject );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          INSERT INTO deptIT VALUES(1, 'Comp Science', 100000,
      3                  AllProject (ITProject (1, 'Hire Developer', 70000.8, 'John'),
      4                          ITProject (2, 'buy hardwares', 3456.5, 'Mike')));
      5          INSERT INTO deptIT
      6          VALUES(2, 'Network setup', 100000,
      7                  AllProject (ITProject (1, 'Hire network engineer', 87655, 'Kevin'),
      8                          ITProject (2, 'buy cables and servers', 87650, 'Sean')));
      9  END;
     10  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> DECLARE
      2  addcourses AllProject :=
      3  AllProject (ITProject (1, 'Hire network engineer', 87655, 'Kevin'),
      4          ITProject (2, 'buy cables and servers', 87650, 'Sean'),
      5          ITProject (3, 'Buy software license', 87630, 'Jim'));
      6
      7  BEGIN
      8          UPDATE deptIT
      9          SET projects = addcourses
     10          WHERE name = 'Network setup';
     11  END;
     12  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> DECLARE
      2  my_projects AllProject ;
      3  BEGIN
      4          SELECT projects INTO my_projects FROM deptIT
      5          WHERE name = 'Network setup';
      6
      7          FOR i IN my_projects.FIRST..my_projects.LAST loop
      8                  DBMS_OUTPUT.PUT_LINE(my_projects(i).p_no);
      9          END loop;
     10  END;
     11  /
    1
    2
    3
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    Notice the loop in what was your original query and how the variable i is assigned values from the varrays first through last record index. Notice also that the p_no values are corectly reported for the data in question.
     
  3. bangla123

    bangla123 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Many Thanks.