How to create a .gms file(GAMS) in excel vba using oracle stored Procedure.
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