12 Replies Latest reply on Oct 24, 2013 10:14 PM by rp0428

    Loading a file via sql developer

    DisolocatedCurry


      I have a file a csv file on my pc.

       

      I want to load this to a database which is on a unix system.

       

      I have a connection set up to the database area on unix and I can see my empty table that I want to load my data into(in sql developer).

      Usually I would right click on the table and import the data. However, the file is too big I think. It freezes when I try to do this.

       

      I am not an administrator on my pc and will not be able to get such access so I cannot use sql loader as I see it.

       

      Is there a way that that I can load this file into this database without being an administrator on my pc??

       

      Thanks in advance

        • 1. Re: Loading a file via sql developer
          thatJeffSmith-Oracle

          How big is the CSV?

           

          How are you able to use SQL Developer and not SQL Loader?

          • 2. Re: Loading a file via sql developer
            DisolocatedCurry

            The csv is about 1/10 th of a gig.

             

            I ran the sqldeveloper.exe that I got off a colleague on my pc. I then connect to a database on server that the IT department set up via sql developer.

             

            Apparently it is on unix. I'm not too familiar with unix though and how it operates.

             

            Ordinarily I would be an admin on my pc and download my own oracle database and I would have this added layer of bureaucracy!! But this is where I am.

             

            I am not an administrator on the pc.

            • 3. Re: Loading a file via sql developer
              thatJeffSmith-Oracle

              Let's try adding more memory:

               

              find the sqldeveloper.conf file in the sql developer/bin subdirectory where you copied the application to.

               

              Add VMOption -XX:MaxPermSize=256M -- try changing this to 512M and see if you're able to load the file

               

              Ammend the Xmx line such:

              AddVMOption  -Xmx1024M

              • 4. Re: Loading a file via sql developer
                Usually I would right click on the table and import the data. However, the file is too big I think. It freezes when I try to do this.

                Can we assume that you then tried the obvious solution and broke the file into smaller pieces?

                 

                Either write a simple Java app to split the file, download a file splitter or use an editor (e.g. Textpad) and create several smaller files. Then load the files one at a time.

                • 5. Re: Loading a file via sql developer
                  DisolocatedCurry

                  I tried to add more memory. No data was seen to load into the table....yet the next button was able to be pushed. Then when pushed there were no columns to select.

                   

                  Putting the file into smaller files...and loading them up separately is something I dont want to do if this is a repeatable exercise.

                   

                  Is there no bulk load option for me here?

                  • 6. Re: Loading a file via sql developer
                    DisolocatedCurry

                    I have got a guy in the IT department to load up this file via PL/SQL. But I may have to repeat this exercise so I dont want to be bothering him again.

                     

                    He just logs on to the database where the table is stored and can import from the tools menu then in PL/SQL Developer.

                     

                    He gave me the executable to run in order to use PL/SQL myself. When I run the executable it asks for an oracle log on. I enter the username, password and database that I use in sql developer to connect to that database. But it then gives me an error saying :

                     

                    "Initialization error

                    SQL*Net not properly installed

                     

                    OracleHomeKey:

                    OracleHomeDir:"

                     

                    I do not have oracle on my pc as I said above.

                     

                    Is there a way I can use PL/SQL??

                    • 7. Re: Loading a file via sql developer
                      Jim Smith

                      As far as I know, PL/SQL Developer requires an Oracle client installation, whereas SQL Developer doesn't.

                       

                      I would have thought SQL Developer would be able to cope with a 100MB file.   Try running sqldeveloper from <sqldev>\sqldeveloper\bin\sqldeveloper.exe.  That will display a console which may show underlying errors.

                      • 8. Re: Loading a file via sql developer
                        thatJeffSmith-Oracle

                        I'm wondering if the problem is something other than the size of the file, rather, what encoding do you have selected in the wizard? If it's not correct the record preview won't show anything or will show 'corrupted' looking data.

                         

                        Also if you're going to be doing this on a regular basis with that much data, I'd advise setting up an External Table or SQL*Loader routine to do it, will be more efficient than using SQL Dev to generate a load of INSERTs.

                        • 9. Re: Loading a file via sql developer
                          Gary Graham-Oracle

                          But if it is a memory issue, then the OP can try increasing the JVM memory limit from the default (I believe) of 640M to 1024M or higher ...

                          AddVMOption  -Xmx1024M

                          as long as the machine has sufficient physical memory.

                           

                          Jeff's earlier comment related to the perm memory, which is much less often a problem.

                           

                          Regards,

                          Gary

                          • 10. Re: Loading a file via sql developer
                            thatJeffSmith-Oracle

                            Thanks for the catch Gary, I'll correct my answer!

                            • 11. Re: Loading a file via sql developer
                              Also if you're going to be doing this on a regular basis with that much data, I'd advise setting up an External Table or SQL*Loader routine to do it, will be more efficient than using SQL Dev to generate a load of INSERTs.

                              I gathered from OPs original post that the data file is NOT on the database server so an external table solution is not available (caveat - Oracle has an agent that can be installed on remote pcs that allows this but that will seldom be allowed on a client pc).

                              • 12. Re: Loading a file via sql developer
                                I have got a guy in the IT department to load up this file via PL/SQL. But I may have to repeat this exercise so I dont want to be bothering him again.

                                 

                                If you are talking about 'repeats' then there are a couple of issues you haven't mentioned: 1) The possible need to someone other than you to do the load if you are not available and 2) the need for exception and/or error handling.

                                 

                                A client/PC oriented solution is often not appropriate if either of those two considerations need to be dealt with since no one else (including the DBA) will have access to your PC where any log/bad/etc files will be located. Production data loads should most certainly NOT be performed from ANY unmonitored/protected client machine; that would be a serious breach of security.

                                 

                                For institutional architecture it would be better to create a process that allows you to move the file to the database server.

                                 

                                Then you can use an external table or sql*loader approach. Either of those can easily conform to the security and auditing requirements of the org and would have visibility to the entire development team.