ORA-06531: Reference to uninitialized collection

[dbaliker]

ORA-06531: Reference to uninitialized collection
-------------------------------------------------------------------------
All, can you please explain me where i should change to fix this error. Very urgent -

Declare
lcount number;
TYPE emp_name IS Varray(1000) OF varchar2(40);
emp emp_name;
user_name varchar2(200);
BEGIN
select count(*) into lcount from SUSERIDTABLE where "UserID" like 'karthik.%';
IF(lcount > 0) THEN
FOR i in 1..lcount LOOP
select "UserID" into emp(i) from SUSERIDTABLE where "UserID" like 'karthik.%';
user_name := '""Key" not like"';
IF (i=lcount) THEN
user_name := emp(i);
ELSE
user_name := emp(i) || 'and';
dbms_output.put_line('The GetValue with value ' || user_name);
END IF;
END LOOP;
END IF;
exception
when others then
dbms_output.put_line('The GetValue with message ' || sqlerrm);
END;
/

The GetValue with message ORA-06531: Reference to uninitialized collection
PL/SQL procedure successfully completed.

I am just trying to form a string like this:
"Key" not like 'UserID.%' and "Key" not like 'UserID.1.%'.
But I am not able to get that string...

[gandolf989]

What are you really trying to accomplish? The whole idea behind using a pl/sql collection is that you fetch a lot of data into a memory structure in the database and then you do something with that data.

You can't access anything in a pl/sql collection until you put data in it, which means select * bulk collect into. For the code you wrote you might as well have used a cursor. Take a look at the doc and think about what you need and you should be able to come up with something better.


http://download.oracle.com/docs/cd/B...olls.htm#34607

DECLARE
   lcount        NUMBER;
   TYPE emp_name IS Varray(1000) OF VARCHAR2(40);
   emp emp_name;
   user_name     VARCHAR2(200);
BEGIN
   SELECT userid
     BULK COLLECT INTO emp
     FROM SUSERIDTABLE 
    WHERE "UserID" LIKE 'karthik.%';
  IF(emp.COUNT > 0) THEN
     FOR i  IN 1..lcount
     LOOP
        user_name := emp(i) || 'and';
        dbms_output.put_line('The GetValue with value ' 
                            || user_name);
     END LOOP;
  END IF;
END;
/

[dbaliker]

Hey thanks for the response. My requirement was quite simple.
I have a table which contain values like test,test.1,test.2 etc.

I have another table which contains these names appended with some more other parameters like the values in this table will be like
test.IsUser,test.1.IsUser and test.2.IsUser.Like this I will have some 100 values for all the 3 names in the other table.

Now I had to form a string like this:
"Key" not like 'test.1.%. and "Key" not like 'test.2.%'.
Also I wanted this string to be passed and used for another purpose.

So I used the method that I had previously posted. I hope there was smething missing in it. But I was not able to figure out as I am new for Oracle.
So if u can me the code which works and gives me the string as above then I would be grateful.Hope I am much clearer now.

[gandolf989]

Hope I am much clearer now.

No not really.

If you just want to update a column,
then use the update with the replace function.

update SUSERIDTABLE
   set key=replace(key, 'this', 'that')
 where username like '%theother%';

If you want to generate new rows,
then do an insert from a select.

insert into SUSERIDTABLE ( username )
select 'test'||rownum||'.IsUser'
  from user_objects
 WHERE rownum < 101;

[dbaliker]

I think u havent understood my requirement. I wanted to frame a string from sql and use that string elsewhere.
It is not as simple as you have said above. I do not want to update/replace. The values are already present in the table but I want to form a query string from SQL procedure.

[gandolf989]

I think u havent understood my requirement. I wanted to frame a string from sql and use that string elsewhere.
It is not as simple as you have said above. I do not want to update/replace. The values are already present in the table but I want to form a query string from SQL procedure.

It seems like you want dynamic sql. But who can tell.

[PAVB]

... I want to form a query string from SQL procedure.

Posted code does not appear to be building a dynamic sql string... plenty of documentation on the web about how to do it, just google "oracle dynamic sql"

[dbaliker]

It seems like you want dynamic sql. But who can tell.

Hey the first code u gave is working absolutely fine. Thanks for that.

[gandolf989]

Hey the first code u gave is working absolutely fine. Thanks for that.

Your welcome. I assume that you used it as
a frame work to stick the rest of you logic into.