How to create a .gms file(GAMS) in excel vba using oracle stored Procedure.

610373

hi

I have written the code.

Please tell me how to create a .gms file. I am able to read the clob data but not able to write the clob into a file and saving the file as a .gms

Please help me. Refer the code below:

Private Function ClobReading(Ccode As String, Sid As String)
Dim OraSession As Object 'Declare variables as OLE Objects
Dim OraDatabase As Object
Dim OraDynaset As Object

Dim objItem As Object
Dim strname As String

Dim buffer As Variant
Dim buf As String

'Dim rs As ADO.Recordset

Const ClobDestPath = "C:\"

Dim ArrCursorOutput(0) As String ' This array will actually be used for wrting the recordset to the excel workbook
ArrCursorOutput(0) = "gams"

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("db_SERVER", "final/owner_6789", 0)

OraDatabase.Parameters.Add "CountryCode", Ccode, ORAPARM_INPUT
OraDatabase.Parameters("CountryCode").ServerType = ORATYPE_VARCHAR2

OraDatabase.Parameters.Add "scenarioid", Sid, ORAPARM_INPUT
OraDatabase.Parameters("scenarioid").ServerType = ORATYPE_VARCHAR2

OraDatabase.Parameters.Add "gams", 0, ORAPARM_OUTPUT
OraDatabase.Parameters("gams").ServerType = ORATYPE_CLOB

OraDatabase.Parameters.Add "status", 0, ORAPARM_OUTPUT
OraDatabase.Parameters("status").ServerType = ORATYPE_VARCHAR2

Set OraSQLStmt = OraDatabase.CreateSql("begin scenario_pck.get_gams_file(:CountryCode,:scenarioid,:gams,:status); end;", ORASQL_FAILEXEC)

Set rs= OraDatabase.Parameters("gams").Value

'OrderDynaset.Save
'Get a free file number
'FNum = FreeFile
'Read entire CLOB value, buffer must be a Variant
'AmountRead = OrderDynaset.Read(buffer)
'buf is string holding the buffer

' Create ADO stream object
Set mstream = New ADODB.Stream

' Set the character set for you particular type of text data
mstream.Charset = "us-ascii"

' Set it to a text file type
mstream.Type = adTypeText

' Open it
mstream.Open

----------------------------------------getting an error at this line-------------------------
' This writes the text file from the clob field to the buffer
mstream.WriteText 'rs.Fields(0).Value



' This saves the stream to a file on disk
'mstream.SaveToFile ClobDestPath & rs.Fields("FIELD").Value
mstream.SaveToFile ClobDestPath, adSaveCreateOverWrite


End Function