14 Replies Latest reply: Feb 15, 2006 10:34 PM by sanju2 RSS

    Using Bulk insert or SQL Loader in VB6

    415721
      Hi,
      I am quite new to the Oracle world and also forums. But I am looking for some directions in how i get a dataset of 10000 records into a table the quickest way. I have the recordset in an ADO Recordset (or a textfile if that is easier) and I want to insert them in an empty Oracle table. The problem is - I don't know how to.

      Situation
      The Oracle DB is on another computer I have nothing special installed on the computer running the VB6 application.
      Can anyone please provide code example or guidelines...

      Regards,
      Christian
        • 1. Re: Using Bulk insert or SQL Loader in VB6
          JustinCave
          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.

          Justin
          Distributed Database Consulting, Inc.
          http://www.ddbcinc.com/askDDBC
          • 2. Re: Using Bulk insert or SQL Loader in VB6
            415721
            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.
            Regards,
            Christian
            • 3. Re: Using Bulk insert or SQL Loader in VB6
              6560
              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
              www.udaparts.com


              • 4. Re: Using Bulk insert or SQL Loader in VB6
                415721
                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).
                Regards,
                Christian
                • 5. Re: Using Bulk insert or SQL Loader in VB6
                  15208
                  Christian,
                  ADOX has some limitations.
                  You may want to try Recordset Save and Open methods.

                  Sinclair
                  • 6. Re: Using Bulk insert or SQL Loader in VB6
                    415721
                    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.
                    • 7. Re: Using Bulk insert or SQL Loader in VB6
                      216205
                      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?
                      • 8. Re: Using Bulk insert or SQL Loader in VB6
                        15208
                        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;"
                        con.Open
                        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;"
                        con2.Open

                        'open the saved file
                        rst2.Open "c:\myfile.txt"
                        '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"))
                        rst2.MoveNext
                        Loop

                        rst.Close
                        rst2.Close
                        rst3.Close
                        con.Close
                        con2.Close

                        Sinclair
                        • 9. Re: Using Bulk insert or SQL Loader in VB6
                          216205
                          Do you have any idea how this might be done using a procedure?
                          • 10. Re: Using Bulk insert or SQL Loader in VB6
                            15208
                            Hi Alexander,
                            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
                            begin
                            insert into dept2 select * from scott.dept@mydblink;
                            end;
                            /

                            VB code:
                            con.ConnectionString = "...;"
                            con.Open
                            cmd.ActiveConnection = con
                            cmd.CommandText = "MY.TESTSP"
                            cmd.CommandType = adCmdStoredProc
                            cmd.Execute

                            Sinclair


                            • 11. Re: Using Bulk insert or SQL Loader in VB6
                              421618
                              Hi,

                              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.
                              • 12. Re: Using Bulk insert or SQL Loader in VB6
                                444061
                                Charlie,
                                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 jlilley@empathy.com
                                Thanks,
                                John Lilley
                                • 13. Re: Using Bulk insert or SQL Loader in VB6
                                  3324
                                  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
                                  • 14. Re: Using Bulk insert or SQL Loader in VB6
                                    sanju2
                                    Hi,

                                    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,
                                    Sanjeev.