Thank you very much.
This time we will ask about converting a large number of databases to CSV.

We needed to convert the Oracle database to CSV using VB6.
(I want to migrate Oracle to Postgre, but the only way was to read CSV with psql)
So, I made it as follows.

'Data output
    strSQL = ""
    strSQL = strSQL&"SELECT *"
    strSQL = strSQL&"FROM"&strSchema&"."&strTable&""
    Set objData = Po_DataBase.DbCreateDynaset (strSQL, ORADYN_DEFAULT)
    'File open
    Set FSO = CreateObject ("Scripting.FileSystemObject")
    Set Ts = FSO.OpenTextFile (strOutput&strTable&"_"&Format (DateTime.Date, "yyyyMMdd")&".csv", ForWriting, True)
    'File output
    With Ts
        Dim i As Integer
        Dim j As Integer
        Do While objData.EOF = False
            strOut = ""
            For j = 0 To intFldCount
                strOut = strOut&CORA20_FieldsValue (objData, objDataFld, j)&","
            strOut = Left (strOut, Len (strOut)-1)
            .WriteLine strOut
            'Error here
    End With
What was wrong?

The above program worked.
If the number of data is a certain amount, it can be discharged as CSV.
However, an error occurred when the number of output data exceeded 226,482 KB.
CSV could only be converted to intermediate data.


This time, I'm half-successful, so I'm looking for a way to do it without changing the language or technique.
Please give me a professor if you like.

Append 1

Try to add Err.Description to the error location
"Out of memory" is displayed.
In this case, can you deal with it by expanding the memory?
Or can it be avoided by tuning the SQL part or split output (I do not know how)?

Append 2

The location of the error is added to the above code.
FieldValue is as follows.

Public Function CORA20_FieldsValue (ByRef Ho_DBObject As Object _
                                    , Ho_Fields () As Object _
                                    , Hn_Idx As Integer) As String
    Dim Ls_Ret As String
    On Error GoTo CORA20_FieldsValue_ERR
    '/ * Return value clear
    If Not Ho_Fields (Hn_Idx) Is Nothing Then
        If Pb_ADO_ConnectFlg = True Then
            Ls_Ret = CStr (IIf (Ho_Fields (Hn_Idx) .Type = 202, vbNullString, 0))
            Ls_Ret = CStr (IIf (Ho_Fields (Hn_Idx) .OraIDataType = 1, vbNullString, 0))
        End If
        Ls_Ret = vbNullString
    End If
    'Use IsNull whenever there is a Null value.
    If Not IsNull (Ho_Fields (Hn_Idx) .Value) Then
        If Pb_ADO_ConnectFlg = True Then '/ * ADO
            Dim Ls_Value As String
            Ls_Value = CStr (Ho_Fields (Hn_Idx) .Value)
            If Len (Trim (Ls_Value)) = 0 Then
                Ls_Value = vbNullString
            End If
            Ls_Ret = Ls_Value
            Ls_Ret = CStr (Ho_Fields (Hn_Idx) .Value)
        End If
        If Pb_ADO_ConnectFlg = True Then '/ * ADO
            Ls_Ret = IIf (Ho_Fields (Hn_Idx) .Type = 202, vbNullString, 0)
        Else '/ * Oracle Object for OLE
            Ls_Ret = IIf (Ho_Fields (Hn_Idx) .OraIDataType = 1, vbNullString, 0)
        End If
    End If
    CORA20_FieldsValue = Ls_Ret
    Exit Function
    If Err.Number<>0 And Err.Number<>3021 Then
        Call CORA20_ORAErr (Po_DataBase, "CORA20_FieldsValue")
        Call CMSG00_ORAErr ("CORA20_FieldsValue")
    End If
    CORA20_FieldsValue = Ls_Ret
End Function

The connection code is as follows.

Function CORA00_DB_Connect (ByRef Ho_DBObject As Object _
                            , ByVal Hs_DatabaseName As String _
                            , ByVal Hs_UserName As String _
                            , ByVal Hs_Password As String) As Integer
    Dim Ls_Connect As String
    'Error processing is performed to determine a VB error.
    On Error GoTo CORA00_DB_Connect_Err
    CORA00_DB_Connect = Ret_NG
    '/ * Connection type check
    Pb_ADO_ConnectFlg = CheckAdoConnection
    If Pb_ADO_ConnectFlg = True Then '/ * ADO
        Set Ho_DBObject = New clsADO
        'I'm passing here
        Ls_Connect = "Provider = OraOLEDB.Oracle;Data Source ="&Hs_DatabaseName&";" _
                            &"User Id ="&Hs_UserName&";" _
                            &"Password ="&Hs_Password&";"
        '/ * Use client control
        Ho_DBObject.CursorLocation = adUseClient
        '/ * Connection string set
        Ho_DBObject.ConnectionString = Ls_Connect
        '/ * DB connection
        Ho_DBObject.ConnectDatabase Ho_DBObject.ConnectionString, Hs_UserName, Hs_Password
    Else '/ * Oracle Object for OLE
        Ls_Connect = Trim $(Hs_UserName)&"/"&Hs_Password
        'Lo_OraSession object is used in the application
        'OraDatabase and OraConnection, OraDynaset objects
        Manage the set of '
        'Recognize Oralce as Object.
        Dim Lo_OraSession As Object
        Set Lo_OraSession = CreateObject ("OracleInProcServer.XOraSession")
        Set Ho_DBObject = Lo_OraSession.OpenDatabase (Hs_DatabaseName $, Ls_Connect $, ORADB_DEFAULT)
    End If
    CORA00_DB_Connect = Ret_OK
    Exit Function
    Call CORA20_ORAErr (Po_DataBase, "ORA000_DB_Connect")
    Call CMSG00_ORAErr ("ORA000_DB_Connect")
End Function
Append 3

We adopted YAmaGNZ's proposal.
I'm going to change the method to output data divided into 1 to 10,000 lines, 10001 to 20000 lines, etc.
I tried ROWNUM immediately, but it is as follows.

  • Answer # 1

    Isn't the number of records obtained by SELECT too large?
    Why not try paging using ROWNUM

        SELECT a. *, ROWNUM num FROM (SELECT * FROM table name) a
      ) b
    WHERE b.num BETWEEN start position AND end position

    For more information, search for "Oracle paging process".

    However, if it is a database migration, it should be executed once, so I think that it is better to output it with tools as Orlofsky says.

  • Answer # 2

    >I'm half-successful this time, so I'm looking for a way to do it without changing the language or technique.

    Please do your best.

    If the above assumptions can be ignored,

    Use paid software that can be used free of charge for 30 days. 5 ways to output Oracle table data It seems to be quick to drop it into an INSERT statement.

    I drop it to cvs with sqlplus

  • Answer # 3

    'An error occurred here

    Is that okay with


    It seems that the connection uses ADO and the provider uses Oracle OLEDB.

    What's going to be a problem is

    Ho_DBObject.CursorLocation = adUseClient


    part probably uses a client cursor.

    Using a client cursor creates a cache and is expected to run out of memory.

    Try using a server cursor.
    To use the server cursor

    Ho_DBObject.CursorLocation = adUseServer


    Because DbCreateDynaset is not oo4o but a function of its own, if there is a place where CursorLocation is specified, please do the same.


    If you are using a form to display progress, etc., set the Enabled property of the form to False to disable operation, and insert DoEvents appropriately in the loop.
    If not, it will harden.

    count = count + 1
            If (count Mod 100) = 0 Then
            End If

    It is like this.

    Addition 2

    Does this happen?

    Append 3

    This is a sample. (4M, 460MB CSV output successful)

    Sub OutputCSV (ByVal con As ADODB.Connection, ByVal strSchema As String, ByVal strTable As String, ByVal strOutput As String)
        Dim strFileName As String
        Dim FSO As Scripting.FileSystemObject
        Dim TS As Scripting.TextStream
        Dim strSQL As String
        Dim objData As ADODB.Recordset
        Dim objDataFld As ADODB.Field
        Dim count As Long
        Dim strOut As String
        Set FSO = New Scripting.FileSystemObject
        strFileName = FSO.BuildPath (strOutput, strTable&"_"&Format (Now, "yyyyMMdd")&".csv")
        Set TS = FSO.OpenTextFile (strFileName, ForWriting, True)
        strSQL = "SELECT * FROM"&strSchema&"."&strTable
        Set objData = con.Execute (strSQL)
        Do Until objData.EOF
            strOut = ""
            For Each objDataFld In objData.Fields
                strOut = strOut&","&objDataFld.value
            TS.WriteLine Mid (strOut, 2)
            count = count + 1
            If (count Mod 100) = 0 Then
            End If
    End Sub

    How to use

    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.ConnectionString = "Provider = OraOLEDB.Oracle;Data Source = (TNS name);User Id = (user name);Password = (password);"
    con.CursorLocation = adUseServer
    OutputCSV con, schema name 1, table name 1, output folder name 1
    OutputCSV con, schema name 2, table name 2, output folder name 2