Write N Read *.doc file Into ORACLE Database (CLOB)

This ths the Working Example Using Oradynaset to write .doc file into Oracle CLOB field
===============================================

Put this Code in the form Top

Global gSession As OraSession
Global gDatabase As OraDatabase

Call This Function to save document
===============================================
Private Sub SaveDoc()
' On Error Resume Next
intDoc_type = Me.cboDocumentName.ItemData(cboDocumentName.ListIndex)
strDocName = Replace(Trim(Me.cboDocumentName.Text), "'", "''")
StrCourt = Replace(Trim(Me.cboCourt.Text), "'", "''")
lngCaseNo = Val(txtCaseNo.Text)
Dim objrst As OraDynaset
Set objrst = gDatabase.DbCreateDynaset("Select *from DOC_ARCHIVE where CASE_NO=" & lngCaseNo & " and DOC_TYPE=" & intDoc_type & "", &H2)
If Not objrst.EOF Then
MsgBox "Document file is already uploaded for this type", vbOKOnly
Exit Sub
objrst.Close
OraDynaset = Nothing
Else
Me.CommonDialog1.Filter = "Word Files|*.doc"
Me.CommonDialog1.ShowOpen
Dim PDesc As OraClob
Dim buffer As String
Dim chunksize As Long
Dim amount_written As Long
'On Error Resume Next
Dim a As Word.Application

'Create the OraSession Object.

If gSession Is Nothing Then
Set gSession = CreateObject("OracleInProcServer.XOraSession")
End If
'Connect to database
Set gDatabase = gSession.OpenDatabase("SUPREME", "ABC/****", &H3)
gDatabase.ExecuteSQL ("insert into DOC_ARCHIVE (CASE_NO,DOC_NAME,DOC_TYPE,UPLOAD_FROM,doc_desc) values ( '" & lngCaseNo & "','" & strDocName & "','" & intDoc_type & "' ,'" & StrCourt & "',empty_clob())")

Set OraDynaset = gDatabase.DbCreateDynaset("select doc_desc from DOC_ARCHIVE where case_no='" & Caseno & "'and DOC_TYPE = '" & intDoc_type & "'", 0&)
Set PDesc = OraDynaset.Fields("doc_desc").Value
chunksize = 32000

'Re adjust the buffer size
buffer = String$(chunksize, 32)

FNum = FreeFile
'Open the file.
Open CommonDialog1.FileName For Binary As #FNum
'set the offset and PollingAmount properties for piece wiseWrite operation
PDesc.offset = 1
PDesc.PollingAmount = LOF(FNum)
remainder = LOF(FNum)

If (LOF(FNum) = 0) Then
MsgBox "File size is zero. Make sure that existence of File and its path are correct"
Exit Sub
End If
'Lock the row for write operation
OraDynaset.Edit
Get #FNum, , buffer
'Do first write operation
amount_written = PDesc.Write(buffer, chunksize, ORALOB_FIRST_PIECE)
While PDesc.Status = ORALOB_NEED_DATA
remainder = remainder - chunksize
If remainder < chunksize Then piecetype = ORALOB_LAST_PIECE chunksize = remainder Else piecetype = ORALOB_NEXT_PIECE End If Get #FNum, , buffer amount_written = PDesc.Write(buffer, chunksize, piecetype) Wend Close FNum OraDynaset.Update 'MsgBox PDesc.Size MsgBox "File of" & PDesc.Size & "Size is uploaded", vbInformation doc_type = 0 strDocName = "" StrCourt = "" lngCaseNo = 0 End If End Sub =======Read More For More On CLOB =============
This ths the Working Example Using Oradynaset to read .doc file into Oracle CLOB field

1.Create desc.doc word file in the Application folder to write into
2.Create grid in form level and put this code

===============================================
Private Sub grdCaseHistory_DblClick()
clickcount = clickcount + 1
If clickcount > 1 Then

Else
gDatabase.Parameters.Add "DOCDESC", Null, ORAPARM_OUTPUT
gDatabase.Parameters("DOCDESC").serverType = ORATYPE_CLOB
End If

If grdCaseHistory.Rows > 1 Then
IntDoc_id = grdCaseHistory.TextMatrix(grdCaseHistory.Row, 5)
intDoc_type = grdCaseHistory.TextMatrix(grdCaseHistory.Row, 6)
lngCaseNo = Val(txtCaseNo.Text)

'Declare variables as OLE Objects.

Dim AmountRead As Long
Dim buffer As Variant
Dim buf As String

'Execute the statement returing 'PartDesc'
gDatabase.ExecuteSQL ("BEGIN select DOC_DESC into:DOCDESC from DOC_ARCHIVE where case_no='" & lngCaseNo & "' and DOC_ID=" & IntDoc_id & " and DOC_TYPE =" & intDoc_type & " for update NOWAIT; END;")
'Get 'PartDesc' from Parameters collection
Set PartDesc = gDatabase.Parameters("DOCDESC").Value
'Get a free file number
FNum = FreeFile
'Open the file.
Open App.Path & "\desc.doc" For Binary As #FNum
'Read entire CLOB value, buffer must be a Variant
AmountRead = PartDesc.Read(buffer)
'put will not allow Variant type
buf = buffer
Put #FNum, , buf
Close FNum
MsgBox "Read operation successfull"
Dim appWord As New Word.Application
strFileName = App.Path & "\desc.doc"
appWord.Documents.Open (strFileName)
Set appWord = Nothing
Else
Exit Sub
End If
End Sub

=========================================
Note:
Create Table:
CREATE TABLE SCADMIN.DOC_ARCHIVE
(
DOC_ID NUMBER(15) NOT NULL,
CASE_NO NUMBER(15) NOT NULL,
DOC_NAME VARCHAR2(100 BYTE),
UPLOADED_DATE DATE,
UPLOADED_BY VARCHAR2(100 BYTE),
DOC_TYPE NUMBER(2),
UPLOAD_FROM VARCHAR2(300 BYTE),
DOC_DESC CLOB
)
===========================================
Create Tiger:

CREATE OR REPLACE TRIGGER SCADMIN.TRG_DOCARCHIVE
BEFORE INSERT OR UPDATE
ON SCADMIN.DOC_ARCHIVE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN

IF inserting THEN
select nvl(max(DOC_id),0)+1 into :new.DOC_id
from DOC_ARCHIVE;
:new.uploaded_date:=sysdate;
:new.uploaded_by:=SYS_CONTEXT('USERENV','SESSION_USER');
END IF;
END;
/