Bulk collect hangs...

[slicendice]

Hi
I'm trying to do a simple (at least I *thought* it was simple!) BULK COLLECT in 9i but when that actual BULK COLLECT statement runs, it just seems to hang (I've run it in SQL*Plus and PL/SQL Developer debugger). The code I'm running is as follows:

  TYPE typVacRec  IS TABLE OF vac_table%ROWTYPE;
  tabVacRec       typVacRec := typVacRec();

BEGIN

  SELECT *
  BULK COLLECT INTO tabVacRec
  FROM   vac_table
  WHERE  id = 300547;
  
  DBMS_OUTPUT.PUT_LINE('Bulk collected: '||tabVacRec.COUNT);

END;

This compiles OK but when running, it gets to the BULK COLLECT line and just stops. Anyone have any ideas what I'm doing wrong or what the problem might be?

Thanks

 

[engineer2100]

How big is the table or how many rows do you anticipate the query to return?

 

[slicendice]

The full table has ~2 million rows, but the query itself only returns ~100 or so

 

[slicendice]

Tried slightly different approach by using a cursor:

  CURSOR cGetVac IS
    SELECT * 
    FROM   vac_table
    WHERE  id = 300547;

  TYPE typVacRec    IS TABLE OF vac_table%ROWTYPE;
  tabVacRec         typVacRec;

BEGIN
  OPEN  cGetVac;
  FETCH cGetVac BULK COLLECT INTO tabVacRec;
  DBMS_OUTPUT.PUT_LINE('Bulk collected: '||tabVacRec.COUNT);
END;

This still has the same prob though....the BULK COLLECT hangs.

I've just tried using a single column query, as follows:

  CURSOR cGetProg IS
    SELECT given_prog
    FROM   vac_table
    WHERE  id = 300547;

  TYPE typProgRec   IS TABLE OF vac_table.given_prog%TYPE;
  tabProgRec        typProgRec;

BEGIN
  OPEN  cGetProg;
  FETCH cGetProg BULK COLLECT INTO tabProgRec;
  DBMS_OUTPUT.PUT_LINE('Bulk collected: '||tabProgRec.COUNT);
END;

...and this works just fine! So it seems to be something to do with using ROWTYPE. Are there any issues when using ROWTYPE in conjunction with a BULK COLLECT? I know it wasn't possible to use ROWTYPE in 8i, but I thought in 9i it should be OK...?

 

[engineer2100]

Is the ID column indexed? if it is not, then there is a possibility that the query is doing a full table scan which might be filling up the buffer....

 

[slicendice]

Yes it's indexed

I can run the select query as a simple standalone SQL statement and it comes back instantaneously

 

[engineer2100]

I heard someone saying that there could be problems while using large objects like LONG, LOB data type; though I dont personally see it as an issue.

There is no issue using %ROWTYPE. I just tested a similar program and it seems to work perfectly fine. So thats ruled out.

My comment above regarding filling up buffer is skewed! What I wanted to say was it might be taking time to do the Full table scan. Apologize for any confusions caused.

 

[slicendice]

Thanks for the info, much appreciated...

There don't appear to be any wacky data types in the table - it's just NUMBERs, CHARs and DATEs.

This table was imported into the database so I guess it might be possible that that's something to do with it...? I'll try creating a fresh table from scratch and see if that makes any difference

Thanks again

 

[slicendice]

If I use just one row of data it still fails. I also tried updating the table one column at a time until all values were NULL, and it still fails.

If I delete all rows from this table and insert a new row manually, it works fine - so it's something to do with the original data contained in the table....but I have no idea what!

Does anyone have any ideas??

 

[Dagon]

You could try adding a LIMIT to your bulk collect and varying the limit to see at what point it starts to hang. That way, you might be able to identify which rows are causing it to hang.

 

[engineer2100]

hey check the date value in every date column. See if you find anything unusual. I am just hoping to see if its the date value that's causing the problem.

 

[slicendice]

Dagon: I've tried using a LIMIT of various different values (1, 10, 100, etc) but to no avail. I've also tried not using LIMIT at all, but same outcome

engineer2100: I've tried deleting various column values, even down to just having one column populated (NUMBER field) but still same result.

I'm starting to wonder if it's maybe something to do with Oracle itself, or my installation of it...?

 

[engineer2100]

Dagon, what about SAVE EXCEPTIONS!!! i think that way you will understand what went wrong!!

 

[Dagon]

How will save exceptions help when the job is hanging rather than producing errors ?

 

[engineer2100]

dumb me!! I forgot the basic problem statement

 

[slicendice]

Sorry to resurrect this one again, but I've had to come back to it and I'm still beating my head against it without any joy.

I've tried deleting all rows from the table (vac_table) and the SQL works then - doesn't hang at all. However if I put so much as one row in there with only one value populated, then it hangs again.