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]

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

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

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.

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

---

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!

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

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

Quote:

---

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>

---

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!