PL/SQL Table question

Hi folks

Has anyone experienced this problem...

I have declared a PL/SQL Table of records i.e each created row in the table will be a record.

For example the record will be made up of 3 items a,b and c. The items can be of any type eg Varchar2,number....

If I insert data into a row of the table assigning values for items a and b and then attempt to read/reference the value of c I get the Exception VALUE_ERROR. If however I assign NULL to c, I can then read/reference it OK.

I also tried declaring the record items ALL with default values of NULL but this does not fix the problem. I believe that this may be a bug in PL/SQL?   This appears to be the only time PL/SQL doesn't like you referencing a variable if it is not defined with a value. The same code for an identical record (ie not within a PL/SQL Table) doesn't produce this error. Nor does referencing an undefined variable....

Obviously referencing undefined variable is bad programming practice but in the case of a PL/SQL Table of records which is passed to my procedure to process it is likely that not all items in the record/row will be set and even performing an NVL on such an item causes VALUE_ERROR.

The only solution seems to be to explicitly set all record items in the row of the table to NULL if you are going to assign values to any item in the row. Unfortunately in my case I have to rely on the programmer passing me the table to initialise the every item in every row.

Does anyone have a better solution?

Thanks in advance

Graeme King
Please reply to graeme.king_at_scottishpower.plc.uk Received on Tue Apr 21 1998 - 17:25:25 CDT