SQLPLUS Question : Urgent ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQLPLUS Question : Urgent ...

  1. #1
    Join Date
    Sep 2000
    Posts
    64
    I tried to extract data form Oracle database in order to build a flat file
    (no separator : fixed position used).
    I have a commentaire column (varchar2(160)) in which users entred text over 2 lines...
    so, using the following command under sqlplus :

    SQL> select commentaire from mytab1;

    the result is the following :

    SQL>
    COMMENTAIRE
    ------------------------------------------------------------
    commenaitre1(line1).
    commenaitre2.

    but I would like to have :
    SQL>
    COMMENAITRE
    -------------------------------------------------------------
    commenaitre1(line1).commenaitre2(line2).


    Thanks in advance...

    Sofiane
    Sofiane

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    I guess you'll have to use PL/SQL to go through your table and build a line using ||

    PS :
    SQL> select 'a' || '.' || 'b' from dual;
    > a.b

  3. #3
    Join Date
    Sep 2000
    Posts
    64
    I was not clear in my explanation.

    Suppose that there is only 1 record in my table.

    mytab definition
    ******
    C1 varchar2(1) notnull
    Commentaire varchar2(200)
    C3 number not null

    SQL> select count(*) from mytab;
    SQL>
    count(*)
    ----------
    1

    SQL> spool /tmp/mytab.lst
    SQL> select * from mytab;
    SQL> spool off

    vi /tmp/mytab.lst (with :set nu)
    1: Ycommenaitre1
    2: commentaire2 N



    I would like to have

    Ycommentaire1commenaitre2N

    Y : for C1 column
    commenaitre1commenaitre2 : for Commenaitre column
    N : for C3 column

    Any solution

    Sofiane
    Sofiane

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    ahhhh, ok, it's just a linesize problem :

    set linesize 1500
    spool ...
    select ...
    spool off


    and you're done

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I though there is a need to use substr and pipeline.
    I even try it myself. geeeh its just setting linesize allright.

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