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

Thread: How do I print space in the beginning of a line

  1. #1
    Join Date
    Mar 2001
    Posts
    287

    How do I print space in the beginning of a line

    Here I tried to output some space key in the beginning of a line. However, SQL Plus seems to ignore those space keys. If I add something like '--' before the space keys, it works. Is there a way to print space key before other characters?

    Test:

    SQL> exec dbms_output.put_line(' there are space in the beginning of the line.') ;
    there are space in the beginning of the line.

    PL/SQL procedure successfully completed.

    SQL> exec dbms_output.put_line('-- there are space in the beginning of the line.') ;
    -- there are space in the beginning of the line.

    PL/SQL procedure successfully completed.[FONT=courier new][FONT=courier new]

  2. #2
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    Try the following :
    SQL> exec dbms_output.put_line(chr(10) || ' There are space in the beginning of the line');

  3. #3
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    The above will give u extra one line coz of chr(10) (NEXT LINE)

    But u can use the following if you don't want any extra line.

    exec dbms_output.put_line(char(1) || ' There are space');

    HTH
    Ravinder

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    You can use this to get characters from dual. Change the start and end points of the loop so you don't get overflow.

    set serveroutput on
    declare
    str varchar2(20);
    begin
    for i in 1..60 loop
    select 'aaaa'||chr(i)||'aaaa' into str from dual;
    dbms_output.put_line(i||', '||str);
    end loop;
    end;

    When you hit chr(32), you will see a space. Plus, it shows the other commonly used chr(x) values and what they do for you.
    Last edited by stecal; 10-07-2003 at 06:23 PM.

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    Code:
    SQL> select 'No space at beginning'||chr(10)||
      2         chr(32)||'One space at beginning'||chr(10)||
      3         chr(32)||chr(32)||'Two spaces at beginning'
      4         Spaces
      5         from dual;
    
    SPACES
    ---------------------------------------------------------
    No space at beginning
     One space at beginning
      Two spaces at beginning

  6. #6
    Join Date
    Mar 2001
    Posts
    287
    Why cannot I simply use ' ' to output a space key? I thought this is just the issue of SQL Plus setting. I know you can get rid of the trailing space by "set trim on". Any idea about preserving the leading space? Thanks!

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Because you simply cannot use '' to output a space. You have two options: using chr(x) or string single quotation marks together in the proper manner to produce the desired effect. If you have access to MetaLink, read this article: Note:1005607.6
    Subject: HOW TO EMBED SINGLE QUOTE IN STRING


    Then decide if you'd rather use chr(x) or ''''''''' to get "" "".

  8. #8
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    I think it's the way dbms_output.put_line work it's just trim all trailing space so even if u embeded CHR(32) in the begining it will not work.

    exec dbms_output.put_line(char(32) ||chr(32) || 'There are space');
    There are space

    Though same works if you do
    select chr(32)||chr(32) ||'There are space' from dual;
    There are space

    So in order to make it worky just add either chr(10) or any other character just before ' There' or chr(32).

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by dba_admin
    Why cannot I simply use ' ' to output a space key? I thought this is just the issue of SQL Plus setting.
    You thought correctly. No need to use warious CHR() and simmilar tricks to suppres SQL*Plus from trimming the leading spaces with DBMS_OUTPUT. Just read te SQL*Plus manual about the options about SET SERVEROUTPUT FORMAT setting. Example:
    Code:
    SQL> set serveroutput on
    SQL> exec dbms_output.put_line('      There should be leading spaces.')
    There should be leading spaces.
    
    PL/SQL procedure successfully completed.
    
    SQL> set serveroutput on format
    Usage: SET SERVEROUTPUT { ON | OFF } [SIZE n]
                 [ FOR[MAT] { WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED] } ]
    SQL> set serveroutput on format wrapped
    SQL> exec dbms_output.put_line('      There should be leading spaces.')
          There should be leading spaces.
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Mar 2001
    Posts
    287
    Thank you.

    I have been read the manual several times but just could not find the right command to do it! Now I got it. Thank you very much!

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