Re: {Spam?} Re: BLOB Insert problem

From: Nirmalya Das <nirmalya_at_hln.com>
Date: Thu, 7 Sep 2006 17:19:14 -0700
Message-ID: <20060907171914.n7lkw0ok4cog4wc8@www.hln.com>

Vlad,

My bad. I put the wrong table.....

The following is the table giving trouble.

A little history...I did a RMAN recovery of the database. After that it stopped
working.

CREATE TABLE MEGA_OWNER_01.OBJECTSTORE
(

     CNY#       NUMBER(8)     NOT NULL,
     PROPERTY   VARCHAR2(255) NOT NULL,
     TYPE       CHAR(1)       DEFAULT 'S'     NULL,
     SESSIONID  VARCHAR2(40)      NULL,
     UPDATED    VARCHAR2(80)      NULL,
     TIMETOLIVE NUMBER(8)         NULL,
     OBJECTDATA BLOB              NULL,
     FSIZE      NUMBER(9)         NULL,
     CONSTRAINT CK_OBJECTSTORE_TYPE
     CHECK (TYPE IN ('S','U','C')),
     CONSTRAINT FK_OBJECTSTORE_CNY
     FOREIGN KEY (CNY#)
     REFERENCES MEGA_OWNER_01.COMPANY (RECORD#)
     ON DELETE CASCADE
     DEFERRABLE ENABLE,
     CONSTRAINT FK_OBJECTSTORE_SESSIONID
     FOREIGN KEY (SESSIONID)
     REFERENCES MEGA_OWNER_01.USERPROF (SESSION#)
     ON DELETE CASCADE
     DEFERRABLE ENABLE

)
LOB(OBJECTDATA) STORE AS SYS_LOB0000047180C00007$$ (

     TABLESPACE ACCTDATA
     STORAGE(INITIAL 512K NEXT 512K MINEXTENTS 1 MAXEXTENTS UNLIMITED
             PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
     ENABLE STORAGE IN ROW
     NOCACHE
     NOLOGGING
     CHUNK 8192
     PCTVERSION 10

)
TABLESPACE ACCTDATA
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE Regards,

Nirmalya

Quoting Vlad Sadilovskiy <vlovsky_at_gmail.com>:

> Nirmalya,
>
> Did you look this up on Metalink?
>
> Could you try and store bigger lobs into the same column of the smae table
> using plain pl/sql?
>
> By looking at your table the statement you've maid sounds fishy. How did you
> determine that the lob wasn't stored? Anyone would wanted to verify the
> accuracy of the test. Version of server and the OCI libraries
> also important.
>
> I think 3964 is the exact number of bytes that can be stored in-line if "in
> row" storage is enabled. So, with that assumption, what you are saing is
> this particular inctance can store "in-line" size blobs although the "in
> row" is disabled, which ultimetely puts them out of line into a blob
> segment, and still cannot save beigger blobs out of line. That is really
> strange.
>
> Check the lob properties and try your test with redefined lob clause to
> permit "in row" storage.
>
> - Vlad
>
> On 9/7/06, Nirmalya Das <nirmalya_at_hln.com> wrote:
>>
>> Trying to insert data in the "DATA" column through OCI.
>>
>> The problem is in one instance this only stores if the length is 3964
>> characters
>> or less. The same application code can store bigger objects in another
>> database.
>>
>> The two databases are identical and the table structure given below is
>> also
>> identical.
>>
>> Trying to find an answer. Don't know where else or what else to look for.
>>
>> Here's the table structure
>>
>> CREATE TABLE MEGA_OWNER_01.BLOBSTORE
>> (
>> CNY# NUMBER(8) NOT NULL,
>> RECORD# NUMBER(8) NOT NULL,
>> PARENTID VARCHAR2(60) NOT NULL,
>> TYPE VARCHAR2(30) NOT NULL,
>> DATA BLOB NULL,
>> STATUS CHAR(1) DEFAULT 'T' NULL,
>> CONSTRAINT FK_BLOBSTORE_CNY
>> FOREIGN KEY (CNY#)
>> REFERENCES MEGA_OWNER_01.COMPANY (RECORD#)
>> ON DELETE CASCADE
>> DEFERRABLE ENABLE
>> )
>> LOB(DATA) STORE AS LOBD_BLOBSTORE_DATA
>> (
>> TABLESPACE ACCTLOB
>> STORAGE(INITIAL 5120K NEXT 5120K MINEXTENTS 1 MAXEXTENTS UNLIMITED
>> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
>> DISABLE STORAGE IN ROW
>> NOCACHE
>> NOLOGGING
>> CHUNK 8192
>> PCTVERSION 10
>> )
>> TABLESPACE ACCTDATA
>> LOGGING
>> PCTFREE 10
>> PCTUSED 0
>> INITRANS 1
>> MAXTRANS 255
>> STORAGE(BUFFER_POOL DEFAULT)
>> NOPARALLEL
>> NOCACHE
>>
>> TIA,
>>
>> Nirmalya
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 07 2006 - 19:19:14 CDT