Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Horizontal output from sql query

    Hi All,

    I am pretty new to Oracle and SQL and am running a query on a oracle database. What I need though, is to get a output from the query in horizontal columns instead of vertical columns. I need to get the output one line after another instead of in columns. I want to see every record with a break in between and the data in the following format.

    First Name
    Last Name
    Initial
    Address 1
    Address 2
    City
    State
    Country

    Currently, I am getting something like this:

    First Name | Last Name | Initial | Address 1 | Address 2 | City | State | Country

    Please help. Thanks in advance .....

  2. #2
    Join Date
    Oct 2003
    Location
    Republic of Srpska, Bosnia and Herzegovina
    Posts
    35

    Horizontal Output

    you can do something like this:

    set serveroutput on;
    begin
    for i in (select empno,ename from scott.emp)
    loop
    dbms_output.put_line('EmpNo='||to_char(i.empno));
    dbms_output.put_line('EName='||to_char(i.ename));
    end loop;
    end;
    /

    Hope this help.

  3. #3
    Join Date
    Oct 2003
    Posts
    1

    Thumbs up Horizontal output

    Hi,
    You can try something like this, if you insist on getting the output using a single SQL statement:

    SQL> select empno||chr(10)||ename||chr(10)||job||chr(10)||sal| |chr(10)||chr(10)||chr(10) as newline from emp;

    This will give you the output in the exact format that you have asked for.

    Hope this helps!

    Cheers!
    H.S.Anand

  4. #4
    Join Date
    Oct 2003
    Posts
    2

    Horizontal Output

    Thanks a lot to both of you for your help. These solutions do exactly what I need to do. Now, I have run into another problem though. There is a field in the database that I am querying which is listed as "CLOB" in the oracle database. It contains a copy of the e-mail or phone conversation that was recorded in the database. How do I get that displayed in the same query ? When I query that field in the queries mentioned above, I get error that says "Inconsistent Data type"

    Thanks in advance.

  5. #5
    Join Date
    Oct 2003
    Location
    Republic of Srpska, Bosnia and Herzegovina
    Posts
    35

    CLOB field

    Im tried something like this

    create table proba (
    a integer, b clob);

    insert into proba values (1,'first clob');
    insert into proba values (2,'second clob');
    insert into proba values (1,'third clob');

    set serveroutput on;
    begin
    for i in (select a,b from proba)
    loop
    dbms_output.put_line('a='||to_char(i.a));
    dbms_output.put_line('b='||to_char(i.b));
    end loop;
    end;
    /

    You can try it.
    This work if you have characters in the CLOB field, but if you have phone conversation it doesnt work. You should be use BLOB or BFILE for phone conversation and dbms_lob package for reading and writing.

    Regards, Bato.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •