Re: Table with object column, errors 22913 and 22912

From: Vince <vinnyop_at_yahoo.com>
Date: 14 Dec 2006 14:19:40 -0800
Message-ID: <1166134780.234188.126930@16g2000cwy.googlegroups.com>

Maxim Demenko wrote:
> Vince schrieb:
> > 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
> >

>
>

> Does that help?

>

> scott_at_ORA102> create or replace type diagnosis_statement_tbl
> 2 is table of diagnosis_statement_ot;
> 3 /

>

> Type created.

>

> scott_at_ORA102>
> scott_at_ORA102> create or replace type diagnosis_ot as object
> 2 (
> 3 Modality varchar2(10),
> 4 StatementCount integer,
> 5 Statements diagnosis_statement_tbl
> 6 );
> 7 /

>

> Type created.

>

> scott_at_ORA102>
> scott_at_ORA102> create or replace type ecg_ot as object
> 2 (
> 3 Diagnoses diagnosis_ot
> 4 );
> 5 /

>

> Type created.

>

> scott_at_ORA102>
> scott_at_ORA102> create table load_errors
> 2 (
> 3 id number(10) not null,
> 4 load_id number(10) not null,
> 5 file_name varchar2(255) not null,
> 6 error_message varchar2(1000) not null,
> 7 error_log clob not null,
> 8 date_created date not null,
> 9 error_file ecg_ot
> 10 )
> 11 nested table error_file.diagnoses.statements store as
> whatever_nested_table;

>

> Table created.

>
>

> Best regards

>
> Maxim

Maxim,
Thank you for the assistance. That did it. Additionally, there were a few other tbl objects, but by adding a separate nested table clause for each, I am able to do create the table as desired.

Thanks again. Received on Thu Dec 14 2006 - 16:19:40 CST