VB 6 Code to handle CLOB data Returned from an ORACLE 8i Stored Procedure

출처 : http://www.planetsourcecode.com/URLSEO/vb/Discussion/AskAProShowPost!asp/lngTopicId!33566/TopicCategory!XML_XSL+Questions/Flag!2/lngWId!1/blnURLSEO!true/Posting.htm

This isn't a question but a solution I found something that is worth a share here at this forum.

It had been a tedious job to handle CLOB data returned from Oracle 8i Stored Procedure into VB6 \ Visual Basic 6 using Microsoft ODBC. The support provided for handling CLOB is ONLY for SQL QUERIES where you fire to SELECT\INSERT\DELETE\UPDATE a CLOB COLUMN from a table, but NOBODY has really bothered about a RETURN VARIABLE as CLOB from ORACLE STORED PROCEDURE. Search on internet but it's useless.

I had this particular Stored Proc that was accepting a Key and depending on that Key, it would FETCH Data, and Make an XML string. This XML would extend upto 40-50K characters which is really large for VARCHAR2 \ LONG datatype to single handedly manage in oracle. Hence I had to use CLOB, but in VB (ADO 2.8) there was no associated DATATYPE or data-structre support that would accept this return value from my stored proc. (adVariant was most perfect but that is useless too)

.... (Poor Me I would bang my head against PC monitor that would show me sites providing help on CLOB Table, Oracle ODBC for CLOB or even CLOB JDBC support in JAVA but they would make me more helpless)

This is what I was doing

# Oracle Code


PROCEDURE GetXML
(temp IN NUMBER,
OutputXML OUT CLOB)
IS
--.....Variable Declaration
BEGIN
OutputXML :=EMPTY_CLOB();
DBMS_LOB.CREATETEMPORARY(OutputXML,TRUE);

stCurrentXMLNode := ''; --Load XML Denotion
--Code to get data, make XML and store into CLOB variable goes here....
DBMS_LOB.WRITEAPPEND(OutputXML, LENGTH(stCurrentXMLNode),stCurrentXMLNode); --Finally complete Loading Data in OUTPUT CLOB
COMMIT;
END GetXML;

But VB 6 was not supporting this OUT CLOB parameter OutputXML and would throw Microsoft ODBC inconsistent Datatype or Access Violation Error for every datatype I tried (VB adDataypes)

Then what I did is divided this collected CLOB in distinct ARRAY (Table) of VARCHAR2 variables of some fixed size(say 4000) Using this code

CREATE OR REPLACE PACKAGE BODY MYPACKEGE
AS
TYPE tblXML IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; --VRACHAR2 ARRAY Table

PROCEDURE GetXML
(temp IN NUMBER,
OutputXML OUT tblXML)
IS
--.....Variable Declaration
BEGIN
OutputXML :=EMPTY_CLOB();
DBMS_LOB.CREATETEMPORARY(OutputXML,TRUE);

stCurrentXMLNode := ''; --Load XML Denotion
--Code to get data, make XML and store into CLOB variable goes here....
DBMS_LOB.WRITEAPPEND(OutputXML, LENGTH(stCurrentXMLNode),stCurrentXMLNode); --Finally complete Loading Data in OUTPUT CLOB

--********************************************************---
output_var := ' '; -- NonEmpty String
offset_var := 0; -- Offset To start chopping with
amount_var := 4000; -- Fixed SIze Varchar2

nCount := 0;
WHILE output_var <> ''
LOOP
DBMS_LOB.READ(OutputXML,amount_var,offset_var,output_var);
--Load character Chunk 'output_var' Cut from previous statement into Array element
OutputXML(nCount) := output_var;
nCount := nCount + 1;
offset_var:= amount_var + offset_var + 1;
END LOOP;
--********************************************************---
COMMIT;
END GetXML;
End MYPACKAGE;

Now use following VB 6 code to load this into a Recordset, then loope thru recordset and load it into a String (max size 65K characters) or variant(max size 2G Characaters)

# VB Code

Dim objCmd As New ADODB.Command
Dim objRS As New ADODB.Recordset

Dim vXML As Variant
With objCmd
.ActiveConnection = "{Microsoft ODBC DSN \ ConnectionString}"
.CommandType = adCmdText
.CommandTimeout = 30
.CommandText = "{call MYPACKAGE.GetXML(?, {resultset 10000, OutputXML})}"
.Parameters.Append .CreateParameter("temp", adNumeric, adParamInput, , 1234) 'Input Paramneter

Set objRS = .Execute 'Load Output VARCHAR2 Array into RecordSet

vXML = ""
While Not objRS.EOF
vXML = vXML & objRS(0).Value 'Loading Large CLOB (by looped appending) into a Variant
objRS.MoveNext
Wend
End With

vXML variable has your CLOB now ....

What Say ...