Re: Table with object column, errors 22913 and 22912

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 14 Dec 2006 21:41:28 +0100
Message-ID: <elsctn$1cs$00$1@news.t-online.com>

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 Received on Thu Dec 14 2006 - 14:41:28 CST