array in oracle block

Question

I have oracle block like

 DECLARE
       TYPE emp_array IS VARRAY(100) OF VARCHAR2(30);
        VAR_PRESENTADDRESS1 varchar2(100);
        emps emp_array;
        inx1 PLS_INTEGER;

    BEGIN
        VAR_PRESENTADDRESS1 := 'test,test1';
        emps := emp_array (select REGEXP_SUBSTR(VAR_PRESENTADDRESS1, '[^,]+', 1, rownum) addressword 
                                from DUAL
                                connect by level <= length (regexp_replace (VAR_PRESENTADDRESS1, '[^,]+'))  + 1);

       FOR inx1 IN 1..2 LOOP
           DBMS_OUTPUT.PUT_LINE(emps(inx1));
       END LOOP;
  END;
   /

This gives error like :

Error report:
ORA-06550: line 9, column 28:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   ( ) - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   table continue avg count current exists max min prior sql
   stddev sum variance execute multiset the both leading
   trailing forall merge year month day hour minute second
   timezone_hour timezone_minute timezone_region timezone_abbr
   time timestamp interval date
   <a string literal with character set specifica
ORA-06550: line 11, column 112:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

   * & - + ; / at for mod remainder rem <an exponent (**)> and
   or group having intersect minus order start union where
   connect ||
ORA-06550: line 17, column 3:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   end not pragma final instantiable order overriding static
   member constructor map
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I want output like:

test
test1

APC · Accepted Answer · 2011-12-21 11:53:57Z

1

That's not how PL/SQL syntax works. Try this:

  select REGEXP_SUBSTR(VAR_PRESENTADDRESS1, '[^,]+', 1, rownum) 
  bulk collect into emps
  from DUAL
  connect by level <= length (regexp_replace (VAR_PRESENTADDRESS1, '[^,]+'))  + 1);

answered Dec 21, 2011 at 11:53

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

array in oracle block

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related