CLOB Problems with polymorphic insertion.

From: Ghannodahn Kirrel <ghannodahn_at_icitadel.com>
Date: 30 Nov 2001 12:34:58 -0800
Message-ID: <68227810.0111301234.58142139@posting.google.com>

I am using an object-relational structure in Oracle 9i database to create base class tables for ragged rowsets. Unfortunately, I am having issues with the CLOB datatype on INSERT statements. My source code is as follows:

Types

---

CREATE TYPE TRecord AS OBJECT
(

   id  		NUMBER,
   created	TIMESTAMP,

   modified TIMESTAMP
) NOT FINAL; CREATE TYPE TUser UNDER TRecord
(

   handle	VARCHAR2( 20 ),
   password	VARCHAR2( 20 )

) FINAL; CREATE TYPE TContent UNDER TRecord
(

   title VARCHAR( 100 ),

   defn		CLOB,
   owner	REF TUser

) NOT FINAL; CREATE TYPE TImage UNDER TContent
(

   filename VARCHAR( 100 ),
   thumbnail VARCHAR( 100 )
) NOT FINAL; CREATE TYPE TNews UNDER TContent
(

   teaser CLOB
) NOT FINAL; Tables

---

CREATE TABLE tblUser OF TUser;

CREATE TABLE tblContent OF TContent
(

   owner SCOPE IS tblUser
);

Test Data

---

• Users INSERT INTO tblUser VALUES ( TUser( 1, TIMESTAMP'1997-01-31 09:26:50.124', NULL, 'ghannodahn', 'a' ) ); INSERT INTO tblUser VALUES ( TUser( 2, TIMESTAMP'1998-01-31 09:26:50.124', NULL, 'drakra', 'b' ) ); INSERT INTO tblUser VALUES ( TUser( 3, TIMESTAMP'1999-01-31 09:26:50.124', NULL, 'aracnix', 'c' ) );
• Content INSERT INTO tblContent VALUES ( TImage ( 1, TIMESTAMP'2001-10-01 08:00:00.000', NULL,
  'Image',
  'Impression of nothingness.',
  ( SELECT REF( usr ) FROM tblUser usr WHERE usr.id = 3 ),
  'test.jpg',
  'test_small.jpg'
  ) );

INSERT INTO tblContent VALUES
(

   TNews
   (

      2, 
	  TIMESTAMP'2001-10-01 08:00:00.000', 
	  NULL, 


	  'News', 

	  'Oracle 9i provides some very interesting capabilities through

object/relational structure.',
	  ( SELECT REF( usr ) FROM tblUser usr WHERE usr.id = 1 ), 


	  'My thoughts on Oracle 9i'

   )
);

COMMIT; The two tblContent insertion statements return errors of:

ORA-00932: inconsistent datatypes

on the CLOB columns. NULL will work in the literal's place without a problem.

Thanks in advance for any comments/suggestions.

Ghannodahn Kirrel
Librarian, The Infinity Citadel
http://www.icitadel.com Received on Fri Nov 30 2001 - 14:34:58 CST