DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: how do i ??????

  1. #1
    Join Date
    Apr 2001
    Posts
    5

    Exclamation

    hi guys,

    pl. find a solution for this :

    assuming a simple table, that an emp table has got 3 cols like empno,ename,esal. and contains 10 rows.

    if we give select empno,ename,esal from emp;
    it will list you all ten rows corresponding to empno.

    but i want to see only all empno's in horizontal manner at a time.

    ie., not like :

    empno
    =====
    1001
    1002
    1003
    ..
    ..
    1010

    but i want see them like

    empno
    =====
    1001, 1002, 1003, ....1010

    at sql prompt

    thanks

  2. #2
    Join Date
    Feb 2001
    Posts
    184
    Hi,

    Send me your email ID at oracledba8@orasearch.net

    I'll send you a script that can solve your problem.

    Thanks. Qaisar

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    create this function:

    create or replace function doit(fieldin varchar2, tablein varchar2) return varchar2 is
    wholestring varchar2(4000) := '';
    nextstring varchar2(4000) := '';
    TYPE t_cursor is REF CURSOR;
    v_cursor t_cursor;
    begin
    open v_cursor for 'select '||fieldin||' from '||tablein;
    loop
    fetch v_cursor into nextstring;
    exit when v_cursor%notfound or ((length(wholestring)+length(nextstring)) > 4000);
    if wholestring is not null then
    wholestring := wholestring||', '||nextstring;
    else
    wholestring := nextstring;
    end if;
    end loop;
    return wholestring;
    end;
    /


    pass in two strings: the first is a string representing an expression which returns a string, such as a char or varchar2 field name, or to_char(numberfield). The second parameter to be passed in is a string containing the table name (including schema name if appropriate).

    For example:

    select doit('to_char(id)', 'emp') summary from dual;
    select doit('name', 'emp') summary from dual;

    OR

    set serveroutput on
    set heading off
    set pagesize 10000
    set linesize 122
    exec dbms_output.put_line(doit('name', 'emp'));

  4. #4
    Join Date
    Apr 2001
    Posts
    5
    thank u so much.

    ram

Posting Permissions

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


Click Here to Expand Forum to Full Width