How do I print space in the beginning of a line

[dba_admin]

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]

[bhallar]

Try the following :
SQL> exec dbms_output.put_line(chr(10) || ' There are space in the beginning of the line');

[bhallar]

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

[stecal]

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.

[stecal]

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

[dba_admin]

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!

[stecal]

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 "" "".

[bhallar]

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).

[jmodic]

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:

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>

[dba_admin]

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!