11 Replies Latest reply: Mar 31, 2014 9:09 AM by User286067 RSS

    SET MARKUP HTML Fails

    Syswizard

      I started this discussion in the general forum....my bad.

      SET MARKUP HTML fails

       

      I am trying to understand how SQL Developer hands-off scripts to SQL Plus.

      I thought any SQL Plus script commands were supported in SQL Developer.

       

      When I tried to invoke an SQL Plus session from the command line, I got:

      Ora 12560 TNS:Protocol Adapter Error

        • 1. Re: SET MARKUP HTML Fails
          Jeff Smith Sqldev Pm-Oracle

          We don't hand anything off to SQL*Plus. We have built our own SQL*Plus engine to emulate SQL*Plus. You can see the SQL*Plus supported commands in the help. You are using CONNECT? That should be good in SQL Developer.

          • 2. Re: SET MARKUP HTML Fails
            Syswizard

            re: "We don't hand anything off to SQL*Plus"

            Well Jeff, that explains why the SET MARKUP HTML is failing under SQL Developer and works fine under SQL &Plus.

             

            re: "You are using CONNECT? That should be good in SQL Developer."

            I have no idea what you are referring to.

            • 3. Re: SET MARKUP HTML Fails
              Jeff Smith Sqldev Pm-Oracle

              >>When I tried to invoke an SQL Plus session from the command line, I got:

              I was guessing you were trying to establish a session via CONNECT in SQL Developer, but tell me what you're actually doing and I'll give a better answer

              • 4. Re: SET MARKUP HTML Fails
                Syswizard

                Well Jeff, I'm just trying to run this cool script to export to an Excel file.

                Someone said it works fine in SQL *Plus.

                However, apparently SQL Developer doesn't support SET MARKUP HTML.

                 

                SET LINESIZE 4000

                SET VERIFY   OFF

                SET FEEDBACK OFF

                SET PAGESIZE 99999

                 

                SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF

                SET TERMOUT OFF ECHO OFF

                -- SET MARKUP HTML ON TABLE ""

                -- ENTMAP ON SPOOL ON PREFORMAT OFF

                 

                SPOOL C:\temp\test_xls.xls

                 

                SELECT    object_type ObjType   

                ,         SUBSTR( object_name, 1, 30 ) ObjectName

                ,         created        

                ,         last_ddl_time  

                ,         status        

                FROM      user_objects

                ORDER BY  1, 2

                ;

                SPOOL OFF

                 

                -- SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT ON

                 

                SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT ON

                SET LINESIZE 2000 VERIFY ON FEEDBACK ON PAGESIZE 60 TERMOUT ON

                • 5. Re: SET MARKUP HTML Fails
                  Jeff Smith Sqldev Pm-Oracle

                  Nope, but we do support exporting to an Excel file w/o worrying about any fancy formatting.

                   

                  And you could also say select /*csv*/ object_type... and sqldev would spool the resultset to a CSV file for you, again w/o any formatting required in your script.

                  • 6. Re: SET MARKUP HTML Fails
                    Gary Graham-Oracle

                    To add to Jeff's comments...

                     

                    1. Use SHOW ALL in the worksheet before running a new script to see the list of emulated / supported SQL*Plus SET  options

                    2. To view a query in HTML format (but not quite as nice as the format your script exhibits) ...

                         a) Use the Reports view to create a User Defined Report script for your SELECT statement.

                         b) From the Reports navigator tree, right-click on the script and select the HTML... context menu option.

                     

                    Regards,

                    Gary

                    SQL Developer Team

                    • 7. Re: SET MARKUP HTML Fails
                      Syswizard

                      Wow Gary - thanks for that !!!

                      It actually rendered beautifully with 2 small issues:

                      1) first 3 rows of the Excel worksheet were left blank (data started in Row #4)

                      2) It appears it attempted to style the rows odd-even with an alternating fill color...

                      however, only rows #1 and #2 rendered in that regard.

                      Rows #3 and beyond kept the same fill color as row #2.

                      • 8. Re: SET MARKUP HTML Fails
                        User286067

                        I see that following works in 4.1 as long as I run it as a script (F5).

                         

                        select /*html*/ * from my_table_or_query;



                        • 9. Re: SET MARKUP HTML Fails
                          Gary Graham-Oracle

                          Glad you like it, and I hope you can tweak it to solve the other issues.  But my comment about

                          not quite as nice as the format your script exhibits

                          was comparing how the following two things render in Firefox...

                          1. The report HTML (using only the SELECT statement) option as I describe in my prior post.

                          2. Running your script in SQL*Plus after changing the output file extension from xls to html, then opening it in Firefox

                           

                          So I'm a bit confused by your comments, but wish you all the best going forward.

                           

                          -Gary

                          • 10. Re: SET MARKUP HTML Fails
                            Syswizard

                            select /*html*/ * from my_table_or_query; 


                            Interesting...was that documented somewhere ?


                            • 11. Re: SET MARKUP HTML Fails
                              User286067

                              Not sure, I just tried it