This discussion is archived
7 Replies Latest reply: Jul 3, 2009 11:42 AM by 710143 RSS

\G equivalent : display the sql result vertically

710143 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Here one might get an idea on how it should look like ...
  • 4. Re: \G equivalent : display the sql result vertically
    MichaelS Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points