Table with object column, errors 22913 and 22912

From: Vince <vinnyop_at_yahoo.com>
Date: 13 Dec 2006 09:48:19 -0800
Message-ID: <1166032099.523377.253560@j72g2000cwa.googlegroups.com>

I have a program which reads data files from disk. The data contains ecg information contains the following structure (and more, but this should be enough to illustrate the problem I have):

ECG

	Diagnosis Data (n=1)
		Modality
		Description
		Diagnosis Statements n = 0..?
	Measurements
                                Leads (n=3..12)
                PatientInfo
                ...

I am loading the data first into an object type (constructor functions know how to parse the data)and then within this, load into the corresponding tables. This works very well and is surprisingly efficient.

When errors are encountered, I would like to be able to store the object in a table and, after fixing what is wrong (mostly setup issues), will reprocess. I want to store the object in a table in order to not have to reparse the data into its various components.

Now the problem. With this Object type which contains other object types and tables of object types, I cannot figure out how to define the column for my table.

If I do not give a storage clause for the object, I get error ORA-22913: must specify table name for nested table column or attribute

If I give a storage clause fo the object column, I get error ORA-22912: specified column or attribute is not a nested table type

Are there solutions/suggestions other than not storing this object and when we're ready to reprocess, load the file and reparse?

Thanks,
Vince

Partial object creations scripts:

create or replace type diagnosis_statment_ot as object
(

	StatementMsg1	varchar2(10),
	StatementMsg2	varchar2(10),
	Statement	                varchar2(255),

constructor function,
...
);

create or replace type diagnosis_statement_tbl is table of diagnosis_statement_ot;

create or replace type diagnosis_ot as object
(

  	Modality   	                varchar2(10),
	StatementCount 	integer,
	Statements	diagnosis_statement_tbl,

constructor function....
);

create or replace type ecg_ot as object
(

  	Diagnoses    	diagnosis_ot,
	Measurements 	measurements_ot,
	Patient		patient_ot,

...

constructor function...
member function ins...
);

create table load_errors
(

  id		number(10) not null,
  load_id	                number(10) not null,
  file_name	                varchar2(255) not null,
  error_message	varchar2(1000) not null,
  error_log	                clob not null,
  date_created	date not null,
  error_file	                ecg_ot

);

ORA-22913: must specify table name for nested table column or attribute

create table load_errors
(

  id		number(10) not null,
  load_id	                number(10) not null,
  file_name	                varchar2(255) not null,
  error_message	varchar2(1000) not null,
  error_log	                clob not null,
  date_created	date not null,
  error_file	                ecg_ot

)
nested table error_file store as error_file_tbl;

ORA-22912: specified column or attribute is not a nested table type Received on Wed Dec 13 2006 - 11:48:19 CST