1 2 3 Previous Next 39 Replies Latest reply on Jun 26, 2015 9:09 AM by Billy~Verreynne

    coding standards

    Suri

      Hello,

       

      As far as I know Oracle has never published any article on coding standards. I've seen many people using capitals and few use small case letters while developing the code.

      today when I'm going through few built in packages like dbms_output, dbms_utility, utl_file etc. I was surprised by seeing different coding standards followed for each package.

      copied below piece of code from dbms_utility package specification.

      type maxname_array IS table of VARCHAR2(32767) index by BINARY_INTEGER;

      --  Lists of large VARCHAR2s should be stored here

      type dblink_array IS table of VARCHAR2(128) index by BINARY_INTEGER;

      --  Lists of database links should be stored here

      TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;

      --  order in which objects should be generated is returned here

      TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

      --  order in which objects should be generated is returned here for users.

      TYPE instance_record IS RECORD (NUMBER,VARCHAR2(60));

       

      -- Suri

        • 1. Re: coding standards
          Karthick2003

          I don't know about oracle's standards, but i like to keep it all in lower case. Upper case just irritates me.

          • 2. Re: coding standards
            Raj Nath

            You can refer this link     Tech PL/SQL: Disciplined PL/SQL

            • 3. Re: coding standards
              TPD-Opitz

              In my view it's not so important if you write your key words upper or lower case but keeping in mind that PL/SQL is not case sensitive (meaning that my_var refers to the same variable as MY_VAR or My_Var I would avoid camelcase only naming (MyVar) because there is no rule to get it back if someone accidentally formatted it with equalized case...

               

              bye

              TPD

              • 4. Re: coding standards
                theoa

                In the 90s Oracle developed an extensive set of standards, as part of CDM (Custom Development Method).

                But I don't know if anyone (including Oracle) still uses those.

                • 5. Re: coding standards
                  Karthick2003

                  And one thing I am very picky about is prefixing the variable to identify if its a parameter or a local variable. like l_ for local variable and p_ for parameter.

                   

                  Following is a real situation that we faced in production because of not naming the variable properly. The variable name was same as the column name.

                   

                  SQL> declare
                    2    empno emp.empno%type := 7369;
                    3  begin
                    4    open :rc for select * from emp where empno = empno;
                    5  end;
                    6  /
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> print rc
                  
                       EMPNO ENAME  JOB              MGR HIREDATE         SAL        COM     DEPTNO
                  ---------- ------ --------- ---------- --------- ---------- ---------- ----------
                        7369 SMITH  CLERK           7902 02-APR-13      12975          0         20
                        7499 ALLEN  SALESMAN        7698 20-FEB-13      11600        300         30
                        7521 WARD   SALESMAN        7698 22-FEB-13      11250        500         30
                        7566 JONES  MANAGER         7839 02-APR-13      12975          0         20
                        7654 MARTIN SALESMAN        7698 28-SEP-13      11250       1400         30
                        7698 BLAKE  MANAGER         7839 01-MAY-13      12850          0         30
                        7782 CLARK  MANAGER         7839 09-JUN-13      12450          0         10
                        7788 SCOTT  ANALYST         7566 19-APR-87      13000          0         20
                        7839 KING   PRESIDENT            17-NOV-13       5000          0         10
                        7844 TURNER SALESMAN        7698 08-SEP-13      11500          0         30
                        7876 ADAMS  CLERK           7788 23-MAY-87      11101          0         20
                  
                  11 rows selected.
                  
                  SQL>
                  

                   

                  The tough one is how you write your SQL. with every release new feature gets added and how to accommodate is always a challenge. Two common things that i have seen is right and left alignment of keywords.

                   

                  Something like this

                   

                  select d.dname
                       , e.ename
                    from emp e
                    join dept d
                      on e.deptno = d.deptno
                  
                  

                   

                  or

                   

                  select d.dname
                  ,      e.ename
                  from   emp e
                  join   dept d
                  on     e.depno = d.deptno
                  
                  

                   

                  Thinks get kind of complex when using analytical function's and MODEL clause. The introduction of WITH clause brought some ease in coding readability.

                  • 6. Re: coding standards
                    TPD-Opitz

                    I found in my application that WITH clause almost always harms performance of the statement so that I have to duplicate the subqueries anyway.

                     

                    But that's another discussion I guess...

                     

                    bye

                    TPD

                    • 7. Re: coding standards
                      Karthick2003

                      TPD-Opitz wrote:

                       

                      I found in my application that WITH clause almost always harms performance of the statement so that I have to duplicate the subqueries anyway.

                       

                      But that's another discussion I guess...

                       

                      bye

                      TPD

                       

                      Interesting. Have been using WITH clause for sometime now. Din't encounter something like that. Would appreciate if you could share some test results.

                      • 8. Re: coding standards
                        TPD-Opitz

                        next time I face it I'll open a discussion, but I it might be highly bound to my data model. Just wanted to share this experience...

                         

                        bye

                        TPD

                        • 9. Re: coding standards
                          Suri

                          Karthick_Arp wrote:

                           

                          And one thing I am very picky about is prefixing the variable to identify if its a parameter or a local variable. like l_ for local variable and p_ for parameter.

                           

                          Following is a real situation that we faced in production because of not naming the variable properly. The variable name was same as the column name.

                           

                          1. SQL> declare 
                          2.   2    empno emp.empno%type := 7369; 
                          3.   3  begin 
                          4.   4    open :rc for select * from emp where empno = empno; 
                          5.   5  end
                          6.   6  / 
                          7.  
                          8. PL/SQL procedure successfully completed. 
                          9.  
                          10.  
                          11.      EMPNO ENAME  JOB              MGR HIREDATE         SAL        COM     DEPTNO 
                          12. ---------- ------ --------- ---------- --------- ---------- ---------- ---------- 
                          13.       7369 SMITH  CLERK           7902 02-APR-13      12975          0         20 
                          14.       7499 ALLEN  SALESMAN        7698 20-FEB-13      11600        300         30 
                          15.       7521 WARD   SALESMAN        7698 22-FEB-13      11250        500         30 
                          16.       7566 JONES  MANAGER   
                          17. SQL> print rc
                          18.      7839 02-APR-13      12975          0         20 
                          19.       7654 MARTIN SALESMAN        7698 28-SEP-13      11250       1400         30 
                          20.       7698 BLAKE  MANAGER         7839 01-MAY-13      12850          0         30 
                          21.       7782 CLARK  MANAGER         7839 09-JUN-13      12450          0         10 
                          22.       7788 SCOTT  ANALYST         7566 19-APR-87      13000          0         20 
                          23.       7839 KING   PRESIDENT            17-NOV-13       5000          0         10 
                          24.       7844 TURNER SALESMAN        7698 08-SEP-13      11500          0         30 
                          25.       7876 ADAMS  CLERK           7788 23-MAY-87      11101          0         20 
                          26.  
                          27. 11 rows selected. 
                          28.  
                          29. SQL> 

                           

                           

                           

                          True Karthick. One of our team member has declared a variable with column name and passed it in DELETE statement. (luckily he did that in Test environment )

                          declare

                            identity varchar2(30);

                          begin

                            delete from fnd_users where identity = identity;

                          end;

                           

                          -- Suri

                          • 10. Re: coding standards
                            Raj Nath

                            Check out this link also  PL/SQL best practice Standards

                            • 11. Re: coding standards
                              BluShadow

                              As Billy~Verreynne will tell you, the PL language was developed from an earlier language called Ada.

                              Ada does have coding standards:

                               

                              http://www.adaic.org/resources/add_content/docs/95style/html/sec_3/3-1-3.html

                               

                               

                              3.1.3 Capitalization

                              guideline

                              • Make reserved words and other elements of the program visually distinct from each other.

                              instantiation

                              Use lowercase for all reserved words (when used as reserved words).
                              - Use mixed case for all other identifiers, a capital letter beginning every word separated by underscores.
                              - Use uppercase for abbreviations and acronyms (see automation notes).

                               

                              Lots of other standards detailed on that link...

                               

                              Also see: http://www.adaic.org/resources/add_content/docs/95style/95style.pdf

                              1 person found this helpful
                              • 12. Re: coding standards
                                BluShadow

                                Raj Nath wrote:

                                 

                                Check out this link also  <link removed>

                                 

                                Please do not post links to websites that just give snippets of information in order to try and get you to buy their books for the full details.  That website is notorious for that practice and is not seen as a good resource by many.

                                • 13. Re: coding standards
                                  Billy~Verreynne

                                  Oracle PL/SQL code has been written by very diverse development teams. And not only that, some of the code is really, really old, and uses really, really old mainframe based standards.

                                   

                                  The year is 2015 and most languages in use today, from C/C++, Perl, Python, to .Net/C# and Java, share the same basic principles in program specifications and standards. Old mainframe based standards are part of history.

                                   

                                  Besides upper and lowercase, there are also camelcase and pascalcase. The latter two are typically used for identifiers and unit (procedure, function, class, method) names. Lowercase is used for most everything else. With uppercase very seldom used (if at all).

                                   

                                  See Microsoft .Net Framework Capitalization Standards and Java Style Guide: Naming Conventions and Google C++ Style Guide many other similar guides for Perl, Python, C, Pascal and so on.

                                   

                                  As Blu mentioned above, PL/SQL is an implementation of the Ada language (with Pascal part of the ALGOL family of languages). And Ada also has a very similar set of standards, as you will find in Java, .Net and others.

                                  1 person found this helpful
                                  • 14. Re: coding standards
                                    BluShadow

                                    p.s. as for why Oracle's own code seems to have mixed standards, that's likely because they have multiple developers from different backgrounds and no doubt some contractors who don't know their ar*e from their elbow, and nobody there to kick them in to line and code review what they write.

                                    1 2 3 Previous Next