7 Replies Latest reply: Jul 3, 2009 1:42 PM by 710143 RSS

    \G equivalent : display the sql result vertically

    710143
      Hi all,
      I was wondering if there's is way to show sql output result (in sqlplus terminal) in vertical format (the same as mysql's \G) ?
      thanx
        • 1. Re: \G equivalent : display the sql result vertically
          BluShadow
          You assume we know MySql.... ?:|

          Perhaps if you give an example of what you expect (input and output) we may get an idea.

          Are you referring to Pivoting of data? If so, search this forum for "pivot" as there are many many many threads on doing this.
          • 2. Re: \G equivalent : display the sql result vertically
            Walter Fernández
            Hi,

            Welcome to the forum!

            Please follow recommendations of BluShadow, you expect something like?:
            Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
            Connected as hr
            
            SQL> 
            SQL> SELECT location_id || CHR(13) || street_address || CHR(13) || postal_code || CHR(13) || city || CHR(13) ||
              2         state_province || CHR(13) || country_id
              3  FROM   locations l
              4  WHERE  l.location_id = 3000;
            
            LOCATION_ID||CHR(13)||STREET_A
            --------------------------------------------------------------------------------
            3000
            Murtenstrasse 921
            3095
            Bern
            BE
            CH
            
            
            SQL> 
            There is a command in sqlplus to set the column separator, but I couldn't set a new line :(

            Regards,
            • 3. Re: \G equivalent : display the sql result vertically
              MichaelS
              Here one might get an idea on how it should look like ...
              • 4. Re: \G equivalent : display the sql result vertically
                MichaelS
                Unfortunately I don't think there's such an easy switch to turn on vertical printing in SQL*Plus.
                You need to program it yourself - one way could be
                SQL> select * from xmltable('ROWSET/ROW/*' passing xmltype(cursor(select * from emp where rownum = 1
                )) columns name varchar2(30) path 'node-name(.)', value varchar2(30) path '.');
                /
                NAME                           VALUE
                ------------------------------ ------------------------------
                EMPNO                          7369
                ENAME                          SMITH
                JOB                            CLERK
                MGR                            7902
                HIREDATE                       17.12.80
                SAL                            800
                DEPTNO                         20
                
                7 rows selected.
                • 5. Re: \G equivalent : display the sql result vertically
                  710143
                  thanx all for your help.

                  @BluShadow : you're right, It was my fault, sorry. plz take a look at the link which michaels2 posted.

                  @Walter Fernández : thanx for your solution. but I tried chr(11) inorder to give the corresponding result. I think, maybe it's related to our operating system. I use linux , and I think you're currently using windows ? by the way, consider the situation in which you want to simply use "select *" ;)

                  @michaels2 : thanx, but sir, I didn't underestand your solution ...

                  finally, & from the posts above, it seems that there's not direct way to do that ! and all we can do is just to simulate that someway ...
                  as a mysql expert, & oracle beginner, it seems so difficult to me , inorder to work with sqlplus when there's no equivalent for \G (& ofcourse arrowkeys when you want to go back & edit a sqlcommand before execution) ;)

                  again, thanx all for your help

                  Edited by: user10391666 on Jul 3, 2009 8:24 AM

                  Edited by: user10391666 on Jul 3, 2009 8:25 AM
                  • 6. Re: \G equivalent : display the sql result vertically
                    MichaelS
                    well, to simplify a bit you could wrap the code in a procedure
                    SQL> create or replace procedure print_vertical (sel          varchar2,
                                                                cur   in out sys_refcursor)
                    as
                    begin
                       open cur for
                          select   *
                            from   xmltable ('//ROW/*' passing dbms_xmlgen.getxmltype (sel)
                                             columns name varchar2 (30) path 'node-name(.)',
                                             value varchar2 (30) path '.');
                    end print_vertical;
                    /
                    Procedure created.
                    Then you would just do
                    SQL> var cur refcursor
                    
                    SQL> exec print_vertical('select * from dept where deptno<=20', :cur)
                    PL/SQL procedure successfully completed.
                    
                    SQL> print cur
                    
                    NAME                           VALUE                         
                    ------------------------------ ------------------------------
                    DEPTNO                         10                            
                    DNAME                          ACCOUNTING                    
                    LOC                            NEW YORK                      
                    DEPTNO                         20                            
                    DNAME                          RESEARCH                      
                    LOC                            DALLAS                        
                    
                    6 rows selected.
                    • 7. Re: \G equivalent : display the sql result vertically
                      710143
                      that was great !!!. nice idea
                      I really appreciate your help
                      thank you

                      Edited by: user10391666 on Jul 3, 2009 11:35 AM

                      Edited by: user10391666 on Jul 3, 2009 11:36 AM