We are upgrading our Informix databases to Oracle 9i(v2). I was the DBA
(Informix/MSSQL) and I have been "Oracle DBA" for 3 months.

I am writing a Oracle database connection layer (a VB class using
Oracle OLEDB provider) for my vb developers. I have a little problem
here, see whether you have some idea about it.

Say I have table called "conthist", it has columns
createdate varchar2(20),
createtime timestamp(6),
createop varchar2(20),
contresult varchar2(40,
contpurpos varchar2(40),
contchan varchar2(40),
personid varchar2(10),
contprod varchar2(40),
sequence not null number

I have a sequence call conthist_sequence used for column "sequence".

Now, the developers want to insert a new row into this table, I have
asked them to use this statement:

sSQL = "insert into conthist (createdate,createtime,createop,"
sSQL = sSQL & "contresult,contpurpos,contchan,personid,contprod, sequence)
sSQL = sSQL & "values(to_date('" & Date & "', 'dd/mm/yyyy'), "
sSQL = sSQL & "to_date('" & Format(Now, "dd/mm/yyyy hh:mm:ss") &
"', 'dd/mm/yyyy'), "
sSQL = sSQL & "'GZ', 'result for oracle','purpose for oracle', "
sSQL = sSQL & "'chan for oracle','0101785451', 'prod for oracle',
sSQL = sSQL & "conthist_sequence.nextval)"

and they confirm it works fine.

The developers used to use "addnew" method of recordset object to
insert a new row into a table and ask whether they can keep doing
that. Before I can say yes to them, I want to make sure I can do it by
myself. So I use .Nextval to get the new sequence number and then want
to insert the new row by doing the following:

'goLocalDb is the class
'newid is the new sequence
'rsConthist is the recordset

sSQL = "select conthist_sequence.nextval as newid from dual"
Set rsConthist = goLocalDB.ExecuteLocalSQL(sSQL)
newid = rsConthist!newid
sSQL = "select * from conthist where sequence=" & newid
Set rsConthist = goLocalDB.ExecuteLocalSQL(sSQL)
rsConthist("createdate") = Format(Date, "dd/mm/yyyy")
rsConthist("createtime") = Format(Now, "dd/mm/yyyy "hh:mm:ss")
rsConthist("createop") = "GZ"
rsConthist("contresult") = "result for Oracle"
rsConthist("contpurpos") = "purpose for Oracle"
rsConthist("contchan") = "chan for Oracle"
rsConthist("personid") = "0101785450"
rsConthist("contprod") = "prod for Oracle"
rsConthist("sequence") = newid

The result is I can insert this new row into the table with all data
updated correctly except the timestamp in column "createtime". I just
couldn't set the data in the recordset and the data passed from the provider to Oracle for this column is always Null. I am still trying to find the
solution, in the meantime, does anybody have some experience on the

Any input is appreciated.