Home>
Introduction

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
        objData.MoveFirst
        Do While objData.EOF = False
            strOut = ""
            For j = 0 To intFldCount
                strOut = strOut&CORA20_FieldsValue (objData, objDataFld, j)&","
            Next
            strOut = Left (strOut, Len (strOut)-1)
            .WriteLine strOut
            'Error here
            objData.movenext
        Loop
    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.

Request

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))
        Else
            Ls_Ret = CStr (IIf (Ho_Fields (Hn_Idx) .OraIDataType = 1, vbNullString, 0))
        End If
    Else
        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
        Else
            Ls_Ret = CStr (Ho_Fields (Hn_Idx) .Value)
        End If
    Else
        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
CORA20_FieldsValue_ERR:
    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
CORA00_DB_Connect_Err:
    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 * FROM
      (
        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
    objData.movenext

    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

    The

    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.

    postscript

    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
                DoEvents
            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
            Next
            TS.WriteLine Mid (strOut, 2)
            count = count + 1
            If (count Mod 100) = 0 Then
                DoEvents
            End If
            objData.MoveNext
        Loop
        objData.Close
        TS.Close
    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
    con.Open
    OutputCSV con, schema name 1, table name 1, output folder name 1
    OutputCSV con, schema name 2, table name 2, output folder name 2