Skip to Main Content
  • Questions
  • How to implement a collection of type object

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mukti.

Asked: September 03, 2015 - 5:08 am UTC

Last updated: September 14, 2015 - 4:19 pm UTC

Version: Oracle 10

Viewed 1000+ times

You Asked

Hi,
I have create a object like

Create or replace Obj_Bulk AS Object
(
customer_id NUMBER(6)
, cust_first_name VARCHAR2(20)
, cust_last_name VARCHAR2(20)
, cust_address CUST_ADDRESS_TYP
, phone_numbers PHONE_LIST_TYP
, nls_language VARCHAR2(3)
);

Then I created a type of this object like

Create or replace TYPE Type_name is table of Obj_Bulk;

Please help me out to implement this collection using the anonymous block.

Awaiting the response.

and Connor said...

There's a couple of errors in the script you provided, and you did not provide definitions for all of the types, so I've made a few adjustments, but you can still get the idea of how object code works in PLSQL

SQL> Create or replace
  2  type Obj_Bulk AS Object
  3  (
  4    customer_id NUMBER(6)
  5  , cust_first_name VARCHAR2(20)
  6  , cust_last_name VARCHAR2(20)
  7  , cust_address int
  8  , phone_numbers int
  9  , nls_language VARCHAR2(3)
 10  );
 11  /

Type created.

SQL>
SQL> Create or replace TYPE Type_name is table of Obj_Bulk
  2  /

Type created.

SQL> declare
  2    my_collection Type_name;
  3  begin
  4    --
  5    -- initialise to empty object
  6    --
  7    my_collection := Type_name();
  8
  9    --
 10    -- add an index to the array
 11    --
 12    my_collection.extend;
 13
 14    --
 15    -- fill that with data (which must be of type Obj_Bulk)
 16    --
 17    my_collection(1) :=
 18       Obj_Bulk(123,
 19                'John',
 20                'Smith',
 21                12,
 22                123456,
 23                'NLS');
 24
 25    --
 26    -- assign with multiple rows
 27    --
 28    my_collection :=
 29      Type_name(
 30         Obj_Bulk(123,
 31                  'John',
 32                  'Smith',
 33                  12,
 34                  123456,
 35                  'NLS'),
 36         Obj_Bulk(456,
 37                  'Sue',
 38                  'Brown',
 39                  12,
 40                  123456,
 41                  'NLS')
 42          );
 43
 44    --
 45    -- assign from table
 46    --
 47    select Obj_Bulk(object_id,
 48                  object_name,
 49                  object_type,
 50                  data_object_id,
 51                  123,
 52                  'NLS')
 53    bulk collect into  my_collection
 54    from all_objects
 55    where rownum < 10;
 56  end;
 57  /

PL/SQL procedure successfully completed.

SQL>


Hope this helps

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Mukti Bhargava, September 03, 2015 - 5:42 am UTC

Thanks for the quick and accurate response.

Another question I have is that "how can i use for loop to insert the values and use DBMS.OUTPUT to print the values".


Connor McDonald
September 04, 2015 - 3:34 am UTC

Search the asktom site for "object type", you'll find plenty of useful examples.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library