Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Formatting Pl/Sql code with carriage returns

HUAug 20 2010 — edited Aug 23 2010
According to the manual (PL/SQL User's Guide and Reference page 2-2)
it should be valid to use carriage returns to format :
"To show structure, you can split lines using carriage returns, and indent lines using
spaces or tabs. This formatting makes the first IF statement more readable."

But I get an error when I try that.

It looks as if there is no problem using linefeed, but I have only access to Oracle running on Windows.
So I am looking for confirmation (by experience or inside knowledge),
that this is the case for all platforms (or at least Windows, Solaris and Linux)


Test case
select banner from v$version;
BANNER
------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.


declare
  nl   varchar2(2) := chr(10);
  stmt varchar2(1000);
begin
  stmt := 'begin' || nl
       || '  dbms_output.put_line(''Hello'');' || nl
       || 'end;';
  execute immediate stmt;
end;
/
Hello

PL/SQL procedure successfully completed.


declare
  nl   varchar2(2) := chr(13) || chr(10);
  stmt varchar2(1000);
begin
  stmt := 'begin' || nl
       || '  dbms_output.put_line(''Hello'');' || nl
       || 'end;';
  execute immediate stmt;
end;
/
declare
*
ERROR at line 1:
ORA-06550: line 1, column 6:
PLS-00103: Encountered the symbol "" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <en identifikator>
<en identifikator, der er afgrµnset vha. dobbelte anf°rselstegn>
<en tilknytningsvariabel> << close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe
The symbol "" was ignored.
ORA-06550: line 2, column 33:
PLS-00103: Encountered the symbol "" when expecting one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<en identifikator>
<en identifikator, der
ORA-06512: at line 8


Same error when using CR only

Same error on 9i
BANNER
------------------------------------------------------
Oracle9i Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

5 rows selected.
This post has been answered by MichaelS on Aug 20 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 20 2010
Added on Aug 20 2010
10 comments
6,338 views