10 Replies Latest reply on Aug 23, 2010 12:33 PM by Billy~Verreynne

    Formatting Pl/Sql code with carriage returns

    HU
      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.
        • 1. Re: Formatting Pl/Sql code with carriage returns
          Billy~Verreynne
          HU wrote:

          But I get an error when I try that.
          Correct. Do not use (silly) Windows/MS-DOS dual linefeed carriage return combination when parsing PL/SQL code. Don't you think it is kind of silly to use 2 characters as end-of-line terminator, when a single character suffices? Which begs the question why Microsoft is using 2 characters for indicating a new line...

          If you believe this to be a bug, consider opening a SR with Oracle Support. But this has been around since version 9i (and likely earlier) as far as I can recall.
          • 2. Re: Formatting Pl/Sql code with carriage returns
            HU
            Sorry, I forgot to mention, that I also get the error if I only use a CR.

            Also, I am planning to not use CRLF.


            The manual says I can use CR, but my tests suggest I should be using LF.
            So is the Manual wrong, or is it a code bug?
            Are there platforms where I should use CR?

            As I need to support existing 9i installations, opening a SR will not help.
            • 3. Re: Formatting Pl/Sql code with carriage returns
              MichaelS
              Could reproduce your error in 9.2.0.8 (Unix) and 10.2.0.4.0 (Windows) but not in 11.2.0.1.0 (Unix)
              SQL> select * from v$version where rownum = 1
              /
              BANNER                                                                          
              --------------------------------------------------------------------------------
              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production          
              1 row selected.
              
              SQL> 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;
              /
              Hello
              In previous versions you maybe could do
              SQL> select * from v$version where rownum = 1
              /
              BANNER                                                          
              ----------------------------------------------------------------
              Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
              1 row selected.
              
              SQL> declare
                nl   varchar2(2) := chr(13) || chr(10);
                stmt varchar2(1000);
              begin
                stmt := 'begin' || nl
                     || '  dbms_output.put_line(''Hello'');' || nl
                     || 'end;';
                execute immediate replace(stmt, chr(13));
              end;
              /
              Hello
              • 4. Re: Formatting Pl/Sql code with carriage returns
                Billy~Verreynne
                HU wrote:
                Sorry, I forgot to mention, that I also get the error if I only use a CR.
                Won't work with 10g AFAIK..
                Also, I am planning to not use CRLF.
                Good idea. :-)
                The manual says I can use CR, but my tests suggest I should be using LF.
                On v11.2.0.1, all 3 combinations are working fine.
                So is the Manual wrong, or is it a code bug?
                Perhaps a bit of both?
                Are there platforms where I should use CR?
                That's a Apple Mac standard I believe.
                • 5. Re: Formatting Pl/Sql code with carriage returns
                  HU
                  MichaelS,

                  when you tested 9.2.0.8 (Unix), did it run without problems when you used only a LF?
                  • 6. Re: Formatting Pl/Sql code with carriage returns
                    MichaelS
                    when you tested 9.2.0.8 (Unix), did it run without problems when you used only a LF?
                    Yes, no problem:
                    SQL> select * from v$version where rownum = 1
                    
                    BANNER                                                          
                    ----------------------------------------------------------------
                    Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
                    1 row selected.
                    SQL> declare
                     nl varchar2(1) := chr(10);
                    begin
                      execute immediate  'begin ' || nl  || '  dbms_output.put_line(''Hello'');' || nl || 'end;';
                    end;
                    /
                    Hello
                    PL/SQL procedure successfully completed.
                    
                    but
                    
                    SQL> declare
                     nl varchar2(1) := chr(13);
                    begin
                      execute immediate 'begin ' || nl  || '  dbms_output.put_line(''Hello'');' || nl || 'end;';
                    end;
                    /
                    Error at line 13
                    ORA-06550: line 1, column 7:
                    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 <an identifier>
                       <a double-quoted delimited-identifier> <a bind variable> <<
                       close current delete fetch lock insert open rollback
                       savepoint set sql execute commit forall merge
                       <a single-quoted SQL string> pipe
                    The symbol "" was ignored.
                    ORA-06550: line 1, column 40:
                    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
                       <an identifier> <a double-quoted delimited-id
                    ORA-06512: at line 4
                    • 8. Re: Formatting Pl/Sql code with carriage returns
                      Rob van Wijk
                      And for readability of the code, I'd use this variant:
                      SQL> declare
                        2    stmt varchar2(1000);
                        3  begin
                        4    stmt :=
                        5      'begin
                        6         dbms_output.put_line(''Hello'');
                        7       end;'
                        8    ;
                        9    dbms_output.put_line(stmt);
                       10    execute immediate stmt;
                       11  end;
                       12  /
                      If you don't mind that the first line of variable stmt is not pretty indented, that is:
                      begin
                             dbms_output.put_line('Hello');
                           end;
                      Of course the result is what you expect:
                      Hello
                      
                      PL/SQL procedure successfully completed.
                      Regards,
                      Rob.
                      • 9. Re: Formatting Pl/Sql code with carriage returns
                        BluShadow
                        Billy  Verreynne  wrote:
                        Don't you think it is kind of silly to use 2 characters as end-of-line terminator, when a single character suffices? Which begs the question why Microsoft is using 2 characters for indicating a new line...
                        Yes, but surely you know why 2 characters were used Billy? It's a throwback to the days console output going to printing devices where feeding the paper by a line (Line Feed or LF) was a seperate operation to returning the print carriage to the start of the line (Carriage Return or CR). The principle was that the CR and LF characters were output to the device to start a new line and that device could be a printer or it could be the terminal screen, so it was a 'device independent' thing. Although I love Unix, it's the one that's wrong by making the assumption that a LF character also implies the carriage should return to the start of the line.

                        ;)
                        • 10. Re: Formatting Pl/Sql code with carriage returns
                          Billy~Verreynne
                          Remember that well.. but will prefer to forget ye olde typewriter type devices. Back then we manually opened the printer device (or port in MS-DOS) and wrote directly raw data to the printer. I got to know some printer control languages pretty well like that... :-)

                          BTW, I still use it in shell scripting when displaying interactive messages - having a new message overwrite the existing one displayed. Together with ANSI ASCII for writing pretty colours and bold. Who says a shell script these days have to be gray (or black) and boring? ;-)