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.
What was wrong?
'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
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.
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)?
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
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 hereIs 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 = adUseClientThe
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.
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
- [python] an error message appears when trying to convert a string of numbers entered in input to a float function
- visualstudio2017 professinal is trying to define a typed dataset that tries to get oracle data from sql issuance, but it causes
- vb - how to convert multiple words by replace command
- C # methods for casting and trying to convert
- i'm trying to create a virtual environment using virtualbox, type: linux, version: oracle32bit!