5 Replies Latest reply on Jul 6, 2015 6:58 PM by thatJeffSmith-Oracle

    SQLCL Delimited format- Pipe

    Kofi

      Hi,

      I have a requirement to export an extremely large file using the pipe delimiter.

      I find that for extremely large files, SQLCL works much better than SQLDeveloper, in that, it doesn't crash or hang ;-).

      It's a pretty good tool in many other ways, but I digress.

      I see a /*delimiter*/ option for SQLCL but I don't know how to set the delimiter to pipe in SQLCL. Using the loader format is close to what I need but it leaves out the headings and I'm loathe to add another query just for headings due to the nature of what I'm doing.

      How do we set the particular delimiter that SQLCL should use?

       

      I looked at the link below and when it talked about delimiters, the answer shifted back to SQLDeveloper

      http://www.thatjeffsmith.com/archive/2015/02/oracle-sqlcl-slidedeck-overview-of-our-new-command-line-interface/

       

      I have a feeling I'm missing something small.

      Any help will be appreciated.

      Kofi

        • 1. Re: SQLCL Delimited format- Pipe

          I have a requirement to export an extremely large file using the pipe delimiter.

          I find that for extremely large files, SQLCL works much better than SQLDeveloper, in that, it doesn't crash or hang ;-).

          It's a pretty good tool in many other ways, but I digress.

          I see a /*delimiter*/ option for SQLCL but I don't know how to set the delimiter to pipe in SQLCL.

          See this link for an explanation of the formats available using a 'comment' in the query

          http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/

          Using the loader format is close to what I need but it leaves out the headings and I'm loathe to add another query just for headings due to the nature of what I'm doing. 

          I don't understand that at all. What does 'the nature' of what you are doing have to do with it?

           

          Adding a simple query to get the headers is SIMPLE compared to unloading large amounts of data without error. You only need the headers once so you can easily store them in a file and just concatenate that one line file eveytime you export.

          • 2. Re: SQLCL Delimited format- Pipe
            thatJeffSmith-Oracle

            >>I see a /*delimiter*/ option for SQLCL but I don't know how to set the delimiter to pipe in SQLCL

            You can't. If you want to change up the delimiter, it's back to SQL Developer. So not to go off-topic, but I'd like to help you get it working there as well. If it's hanging or crashing - two very different things - we should find out why and make sure it's not a bug.

            • 3. Re: SQLCL Delimited format- Pipe
              Kofi

              True, adding a simple query is indeed not difficult but I'm trying to avoid it because primarily I want to use inbuilt functionality as much as possible and avoid having to change queries just because of different outputs. Also,  there are a ton of tables with a lot of columns and while you can easily generate headers using SQL Developer or SQL itself, I'll try to avoid it if I can.

              Kofi

              • 4. Re: SQLCL Delimited format- Pipe
                Kofi

                Thanks Jeff.

                I'll try and quantify the issue. I've had some luck today by adding AddVMOption -Xmx2048M to sqldeveloper.conf so I'll continue to monitor the situation.

                Some of the files I'm creating are 800MB plus and SQLDeveloper would jbecome unresponsive after a while whereas SQLCL would happily chug along. I don't know if it's the overhead of all the things that SQLDeveloper is doing. Typically I have 3-4 tabs open and looking at it now, my SQLDev is using 854MB.

                Kofi

                • 5. Re: SQLCL Delimited format- Pipe
                  thatJeffSmith-Oracle

                  SQLcl is definitely doing less, it's only a 12MB download.

                   

                  However, it should still be able to generate very large export files though. I've generated multi-gig files when exporting data in the cart to SQL*Loader formats - and it's basically the same code used to do delimited format.

                  1 person found this helpful