This content has been marked as final. Show 14 replies
SQL*Loader is probably the fastest way-- I believe that utility is part of a standard client install. The Oracle Utilities manual has a number of chapters on SQL*Loader-- there are a lot of configuration options depending on the data you want to load.
Distributed Database Consulting, Inc.
Thank you for the answer. I have tried using the SQL*Loader but I can not access the loader from my machine. I only have the ActiveX Data Object (ADO v2.8) installed and through that I can access the OracleDB. If I try using the SQLLDR command in a command prompt nothing happens. So perhaps a stored procedure (I do not know how to make these in Oracle) and through ADOX calling the stored procedure running the SQL*Loader (or another loader or file).
The SQL*Loader, as I understand it, only takes files on a the computer on which the DB is located.
If there was a way for me to just BULK INSERT a complete ADO Recordset that would be the preffered thing.
Hope the above makes sense.
Hi, Christian Leth:
You can NOT do bulk insert with ADO with VB. That is one of ADO limitations. Oracle OLEDB supports bulk inert nicely, but you must use Raw OLEDB interfaces from C++.
Yuancai (Charlie) Ye
RDB, a tool for fast and securely accessing remote databases with dial-up, cable, DSL and wireless modems anywhere
Fast and securely accessing all of remote data sources anywhere with SocketPro using batch/queue, non-blocking and parallel computation
See 30 well-tested and real OLEDB examples
Again thank you for a quick response. Unfortunately the only thing I have learned so far is that I can not 'post' it directly from ADO to the Oracle DB.
But as I have no SQL*Loader on my machine - I am trying to find an alternate way to do it.
Perhaps saving file om machine that has the OracleDB and then activating a stored procedure. But this is where I require help - as I do not know how to make these stored procedures or calling them from VB6 (which I could probably figure out using the ADOX).
ADOX has some limitations.
You may want to try Recordset Save and Open methods.
Can you please give me some pointers on how to use Recordset save in relation to an Oracle DB. A link to a webpage or some example code would be much appreciated.
Has this issue ever been resolved?
I would also like to pass several records to Oracle for processing. I would like to be able to create a stored procedure that will accept a recordset as a parameter.
The procedure could then process that recordset and insert all the records.
Does anyone know if this can be done?
This may not be "bulk insert" by your definition, but it can transfer data as you want.
A simple VB code for demo purpose:
Dim con As New ADODB.Connection
Dim con2 As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim rst3 As New ADODB.Recordset
con.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=scott;Password=tiger;Data Source=db_one;"
rst.Open "select * from dept", con, adOpenDynamic, adLockOptimistic
'save to a file using ADTG format. You may choose other format.
rst.Save "c:\myfile.txt", adPersistADTG
'dept2 is an empty table with the same table definition as dept. You can create it using SQL*Plus.
'add rows by reading from the saved file.
con2.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=xyz;Password=xyz;Data Source=db_two;"
'open the saved file
'rst3 is an empty recordset because dept2 is empty at this time.
rst3.Open "select * from dept2", con2, adOpenDynamic, adLockOptimistic
'adding rows into dept2.
Do Until rst2.EOF
rst3.AddNew Array("deptno", "dname", "loc"), Array(rst2.Fields("deptno"), rst2.Fields("dname"), rst2.Fields("loc"))
Do you have any idea how this might be done using a procedure?
You may use dblink to achieve similar task.
1. create a database link between two database.
2. create the target table (dept2) that has the same definition as source table scott.dept.
3. create a simple stored procedure.
create or replace procedure testsp is
insert into dept2 select * from scott.dept@mydblink;
con.ConnectionString = "...;"
cmd.ActiveConnection = con
cmd.CommandText = "MY.TESTSP"
cmd.CommandType = adCmdStoredProc
Ihad a similar problem, and i used oo4o to insert about 100000 records from a Foxpro Database to an Oracle 9. With Oracle OLEDB i spent about 7 mins, while with oo4o the same task was completed in about 40 seconds.
I pulled up your post during a search on Oracle OLEDB loading. You note that "Oracle OLEDB supports bulk-insert nicely", which is something I am trying to do. It seems that the straight IRowsetChange interface has terribly slow loading. I only get a few hundred rows per second inserted. Is there something that can make this run faster?
PS: My email is firstname.lastname@example.org
Another way to get as many records as you want is to use the oracle supplied package UT_FILE. You would export data to a file (such as execl or CSV) and read the file into a table. This requires some knowledge od PL/SQL. Check the documentation on how to use the package
How can one insert a batch of records either using PLSQL or JAVA.
Say, I have a set of records in an array or a type [record type / table type]. I do not want to call insert every time for every record but once for all the records in that array. How can I acchieve this.
Thanks & regards,