7 Replies Latest reply: Nov 24, 2012 11:07 AM by Coleman Leviter RSS

    Select hint /*csv*/ not working in 3.2.09.30 for script output formatting

    498849
      The query hint for formatting output when running a script in sql developer is no longer working in the latest version 3.2.09.30.

      Does not work in 3.2.09.30
      Does work in 3.1.07.42

      Hint information was taken from personal blog by Jeff Smith ("I’m currently a Product Manager for Oracle, working on the SQL Developer team.") with these examples presented:

      SELECT /*csv*/ * FROM scott.emp;
      SELECT /*xml*/ * FROM scott.emp;
      SELECT /*html*/ * FROM scott.emp;
      SELECT /*delimited*/ * FROM scott.emp;
      SELECT /*insert*/ * FROM scott.emp;
      SELECT /*loader*/ * FROM scott.emp;
      SELECT /*fixed*/ * FROM scott.emp;
      SELECT /*text*/ * FROM scott.emp;

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

      (His latest screen cap of Developer version on his homepage is 3.1.05 so he may be ex-Oracle.)

      I did not see these select hints in Oracle docs, so it seems to be Sql Developer specific. Also, the lex for hints in a select is /*+ keyword */, while these, as show above, are /*keyword*/ (spaces may be optional). [Oracle documentation on hints|http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF00219]

      Was this functionality broken or is it coupled with some preference that I've missed? Has anyone else seen and reported it?
        • 1. Re: Select hint /*csv*/ not working in 3.2.09.30 for script output formatting
          Jeff Smith Sqldev Pm-Oracle
          Reports of my demise have been greatly exaggerated, I'm still Oracle...I think.

          I see this from time to time, but when it does happen, an application restart always fixes it. I'm guessing something 'screwy' in the JVM is happening.

          I've confirmed this is working for me in the 3.2.09 release.
          • 2. Re: Select hint /*csv*/ not working in 3.2.09.30 for script output formatting
            Gary Graham-Oracle
            Hi Jeff,

            Learned something new -- didn't know of the existence of those formatting hints. You may at least want to recheck this case:
            SELECT /*loader*/ * FROM scott.emp;
            It produces output like this in 3.1.07.42
             7369|"SMITH"|"CLERK"|7902|17-DEC-80|800||20|
             7499|"ALLEN"|"SALESMAN"|7698|20-FEB-81|1600|300|30|
            ...
            but produces no output in 3.2.x.x. The others seem OK, although sometimes F5 stops working and I need to use the Run Script button.

            Also, occasionally (hard to reproduce), I do see a broken pipe error. An untitled dialog pops up with this message:
            An error has occurred. Click Details for information that may be useful when diagnosing or reporting this problem.
            and these details:
            java.io.IOException: Pipe closed
                    at java.io.PipedInputStream.checkStateForReceive(PipedInputStream.java:244)
                    at java.io.PipedInputStream.receive(PipedInputStream.java:210)
                    at java.io.PipedOutputStream.write(PipedOutputStream.java:132)
                    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
                    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
                    at oracle.dbtools.raptor.newscriptrunner.WrapListenBufferOutputStream.flush(WrapListenBufferOutputStream.java:81)
                    at sun.nio.cs.StreamEncoder.implFlush(StreamEncoder.java:278)
                    at sun.nio.cs.StreamEncoder.flush(StreamEncoder.java:122)
                    at java.io.OutputStreamWriter.flush(OutputStreamWriter.java:212)
                    at oracle.dbtools.raptor.format.ResultsFormatter.checkAndFlush(ResultsFormatter.java:485)
                    at oracle.dbtools.raptor.format.ResultsFormatter.write(ResultsFormatter.java:470)
                    at oracle.dbtools.raptor.format.InsertFormatter.write(InsertFormatter.java:232)
                    at oracle.dbtools.raptor.format.InsertFormatter.startRow(InsertFormatter.java:98)
                    at oracle.dbtools.raptor.format.ResultSetFormatterWrapper.print(ResultSetFormatterWrapper.java:174)
                    at oracle.dbtools.raptor.format.ResultSetFormatterWrapper.print(ResultSetFormatterWrapper.java:150)
                    at oracle.dbtools.raptor.format.ResultsFormatter.print(ResultsFormatter.java:440)
                    at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:446)
                    at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:367)
                    at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:49)
                    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:207)
                    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:146)
                    at oracle.dbtools.worksheet.commands.scriptrunner.task.executor.DefaultStatementExecutorTask.runMe(DefaultStatementExecutorTask.java:46)
                    at oracle.dbtools.worksheet.commands.scriptrunner.task.DBCommandRunnerTask.runTaskInThisThread(DBCommandRunnerTask.java:271)
                    at oracle.dbtools.worksheet.commands.scriptrunner.task.ScriptRunnerTask.tailoredRunCommand(ScriptRunnerTask.java:84)
                    at oracle.dbtools.worksheet.commands.scriptrunner.task.DBCommandRunnerTask.runCommand(DBCommandRunnerTask.java:324)
                    at oracle.dbtools.worksheet.commands.scriptrunner.task.DBCommandRunnerTask.loopThroughAllStatements(DBCommandRunnerTask.java:125)
                    at oracle.dbtools.worksheet.commands.scriptrunner.task.ScriptRunnerTask.handleNoSelection(ScriptRunnerTask.java:55)
                    at oracle.dbtools.worksheet.commands.scriptrunner.task.DBCommandRunnerTask.doWork(DBCommandRunnerTask.java:212)
                    at oracle.dbtools.worksheet.commands.scriptrunner.task.DBCommandRunnerTask.doWork(DBCommandRunnerTask.java:54)
                    at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
                    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
                    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
                    at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:515)
                    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
                    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
                    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
                    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
                    at java.lang.Thread.run(Thread.java:662)
            Regards,
            Gary
            • 3. Re: Select hint /*csv*/ not working in 3.2.09.30 for script output formatting
              rp0428
              >
              I did not see these select hints in Oracle docs, so it seems to be Sql Developer specific. Also, the lex for hints in a select is /*+ keyword */, while these, as show above, are /*keyword*/ (spaces may be optional). [Oracle documentation on hints|http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF00219]
              >
              You are correct - those are not SQL hints but are comments that apparently have special meaning only to sql developer. The Oracle parser will ignore any comments including actual hints that have syntax errors.
              • 4. Re: Select hint /*csv*/ not working in 3.2.09.30 for script output formatting
                Jeff Smith Sqldev Pm-Oracle
                Gary, that is something Kris built for himself that I thought everyone else should enjoy too!

                It's not perfect - for example the delimited option doesn't obey the Export dialog delimiter character preference, nor can I tell it not to quote strings, but it's still very useful.
                • 5. Re: Select hint /*csv*/ not working in 3.2.09.30 for script output formatting
                  498849
                  Two different machines at my site with 3.2.09 do not work with these hints. Running a script with the hint produces no output.

                  Restarting the application does not correct the problem.

                  I think the hints are very useful and hope you can identify the issue. If you like, I'll export the config and post it.

                  btw, env is win 7 enterprise 32 bit.

                  Did not find sql developer help content which described the hint, it seems a shame.

                  For your moment's attention, I am puzzled why check for updates will updated everything but a new version. Had to manually download 3.2.x when I was running 3.1.x. Isn't that the purpose of the whole check for updates functionality?

                  Ok. It's a new day. I logged into test environment and it works on my machine.
                  I'm absolutely unsure whether this is intermittent. The other machine is not in my control, so I cannot test it there.

                  I did find /*csv*/ and /* csv */ behave differently as well.

                  I do apologize if I'm missing something and spinning others' cycles. It is so useful

                  /*
                  final notes
                  well, one other thing, the hints are case sensitive
                  sometimes you will get nothing in the output window. then, maybe a restart will correct it.
                  */

                  Edited by: user495846 on Sep 21, 2012 1:28 PM
                  • 6. Re: Select hint /*csv*/ not working in 3.2.09.30 for script output formatting
                    Jeff Smith Sqldev Pm-Oracle
                    Your question about check for updates - we have to direct folks through the proper download channels for our software, license agreement, etc.
                    • 7. Re: Select hint /*csv*/ not working in 3.2.09.30 for script output formatting
                      Coleman Leviter
                      It works in 3.2.09.30 as

                      SELECT * /*xml*/ FROM scott.emp;