using objects in plsql varray

Question

I'm trying to create an object in pl/sql using sql developer. I'm just messing around with the basics to get a hang of it. I keep getting a error

Invalid reference to variable 'I'

SET serveroutput ON


create or replace type conditions as object
(var_name varcher (100) ,
extract_method varchar(100),
default_value varchar (100),
idList varchar (100));


   DECLARE
condition conditions;
TYPE namesarray IS VARRAY(1) OF conditions; 
names namesarray := namesarray();--figure out why this is.
BEGIN
 condition := conditions('a', 'b', 'c', 'd');
 names.extend;
  names(names.last):= condition;
 FOR i IN names.FIRST .. names.LAST
  LOOP
     DBMS_OUTPUT.PUT_line(i.idList);
    END LOOP;
end;

how can i get this to work?

I'm not trying to achieve seeing how to use varray and access objects in plsql, I think I'm using oracle 10g though I'm note sure. Either way your solution worked for me, thank you so much. — manic bubble, Mar 29 '16 at 17:01

score 1 · Accepted Answer · edited May 23 '17 at 11:59

Try to use the datatype VARCHAR2 instead of VARCHAR See: What is the difference between varchar and varchar2?
FOR..LOOP The implicit iteration variable I, and in this case, contains only the current index of your collection. In fact of this, you should use this variable as an index of your collection.

Please consider following approach:

SET serveroutput ON;

--Use VARCHAR2 instead of VARCHAR
CREATE OR REPLACE type conditions
AS object
  (
    var_name varchar2(100),
    extract_method VARCHAR2(100),
    default_value  VARCHAR2(100),
    idList         VARCHAR2(100)
  ) ;
/


DECLARE
  condition conditions;
  TYPE namesarray IS VARRAY(1) OF conditions;
  names namesarray := namesarray() ;--figure out why this is.
BEGIN
  condition := conditions('a', 'b', 'c', 'd') ;
  names.extend;
  names(names.last) := condition;
  FOR i IN names.FIRST .. names.LAST
  LOOP
    DBMS_OUTPUT.PUT_line(names(i) .idList); -- use I as the index for your collection
  END LOOP;
END;
/

Output:

score 1 · Answer 2 · answered Mar 30 '16 at 06:39

As illustrated perfectly there can be another alternative of VARRAY. TABLE Type can also be used here. The difference between all colection types are explained at https://community.oracle.com/thread/457834?start=0&tstart=0

Hope this helps.

SET serveroutput ON;
DECLARE
TYPE namesarray
IS
  TABLE OF conditions;
  names namesarray := namesarray() ;--NULL Constructor
BEGIN
  names:=namesarray(conditions('a', 'b', 'c', 'd'));
  FOR i IN names.FIRST .. names.LAST
  LOOP
    DBMS_OUTPUT.PUT_line(names(i).idList); -- use I as the index for your collection
  END LOOP;
END;

using objects in plsql varray

2 Answers2