DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ORA-06531: Reference to uninitialized collection

  1. #1
    Join Date
    Sep 2009
    Posts
    6

    ORA-06531: Reference to uninitialized collection

    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...

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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

    Code:
    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;
    /

  3. #3
    Join Date
    Sep 2009
    Posts
    6
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

    Code:
    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.

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

  5. #5
    Join Date
    Sep 2009
    Posts
    6
    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.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by dbaliker View Post
    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.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by dbaliker View Post
    ... 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"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Sep 2009
    Posts
    6
    Quote Originally Posted by gandolf989 View Post
    It seems like you want dynamic sql. But who can tell.

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

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by dbaliker View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width