How to use DCOracle and LOBs in Oracle 8i ????

[Andrew Csillag]

Andreas Jung wrote:
> 
> After reading old postings concerning DCOracle and LOBs I
> can not solve the following problem:
> 
> I have  a table under Oracle 8i with a NCLOB field. The base
> character set for the complete database it UTF8. My first
> problem with DCOracle was an ORA-12704 character set mismatch error when
> I tried to insert an instance of DCOracle.dbi.dbiRaw() in to the database.
> 
> My current code looks like this (the table contains the NCLOB field 'content'):
> 
> lob = 'very long text .....'
> raw = DCOracle.dbi.dbiRaw(lob)
> 
> com = 'insert into tab (docnum,content) values(:x,translate(:y using nchar_cs))'
> curs.execute(com,x='1234',y=raw)
> 
> This works for LOBs with less than 4000 bytes. For longer LOBs I get the following error
> message: ORA-01461 can bind a LONG value only for insert into a LONG column.
> 
> Now...how can I store larger LOBs with more than 4000 bytes ? I have read something
> about a dbms_lob package but I have really no idea how to use it.
> 
> Has anyone a working solution or a code example ?!
> 

What you have to do on insert or update is something like this:
a table defined as such:
create table foo_table (pkoid NUMBER, blobval BLOB)

conn = DCOracle.connect('user/pw')
cur = conn.cursor()
cur.execute('insert into foo_table (pkoid, blobval) values (1,
EMPTY_BLOB())')
cur.execute('select blobval from foo_table where pkoid = 1 for update')
blob = cur.fetchone()[0] #can't use fetchall or is blobval is converted
to string!
blob.write(stringToPutIntoBLOB)
conn.commit()

The weird INSERT/SELECT dance is just the way Oracle works, and not a
problem so much with DCOracle.  To do an UPDATE, you update the lob row
with EMPTY_BLOB() and do the select for update dance again.

Unfortunately, there seems to be an issue with dealing with more than
one lob at a time in terms of read()ing and write()ing.  So if you're
only dealing with one, that's ok, but if you try to deal with more than
one at a time, you need to play with it a bit to see if it will work. 
This may not normally be a problem, but there is a hidden issue: 
cursor.fetchall() won't work if there is more than one lob in the result
set that isn't empty (it tries to stringify lobs on the result rows), so
you have to use cursor.fetchone() to cycle through the result rows
(which is probably what you'd do anyhow, so it's not such a big deal,
but one to be aware of in any case).

Hope this helps,
Drew
-- 
print(lambda(q,p):'pmt:$%0.2f'%(q*p/(p-1)))((lambda(a,r,n),t:(a*r/
t,pow(1+r/t,n*12)))(map(input,('amt:','%rate:','years:')),1200.0))