Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.3 February 13, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) This Issue's URLs: Oracle Training 3) 10-Second Tutorial: SQL Programming Standards 4) Answer to the Pop Quiz 5) Function-based Indexes --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- Q1. What is the minimum block size for an Oracle database? Q2. What is the maximum block size for an Oracle database? Q3. What is the desired PCTINCREASE value for rollback segments? Q4. Which of the following database columns requires more storage: (a) N1 NUMBER(38) (b) N2 NUMBER Q5. How much data is transferred in one Oracle I/O? --------------------------------------------------------- 2) THIS ISSUE'S URLs: Oracle Training --------------------------------------------------------- Oracle remains a major player in providing training in Oracle products. Their education home page is at http://education.oracle.com/. Another big vendor of Oracle education is Learning Tree (http://www.learningtree.com). Others are geared toward CBT (computer-based training) and/or OCP (Oracle Certification Program), such as http://www.oraworld.com/ and http://www.guruischool.com/index_main.htm. --------------------------------------------------------- 3) 10-SECOND TUTORIAL: SQL Programming Standards --------------------------------------------------------- I know I'm getting into dangerous territory by bringing up the "S" word - standards -- but I feel that this is a subject that's important enough to warrant discussion, even though I run the risk of being flamed by the experienced PL/SQL developers out there, who might disagree with my "proposed" standards. In my view, the purpose of PL/SQL standards is to: (a) Define a framework for coding PL/SQL programs; (b) Simplify program maintenance and debugging if all PL/SQL programs on a project or within an organization conform to certain established conventions; (c) Conform reasonably well to industry rules such that new hires can easily adapt to the coding standards; and (d) Show commitment to the quality of the application. In order for the standards to be successful, we should provide a framework that covers every aspect of PL/SQL coding: (a) Object naming conventions for all database objects: * Tables, views, and partitions * Constraints: primary keys, unique keys, foreign keys, check constraints * Index naming conventions * Stored programs, triggers -- When to use them? Which one to use? * Sequences, etc. (b) Database table and column definitions * Is there a company-wide abbreviation list for naming tables and columns (e.g., DESC for "description" and NUM for "number")? * Is there a standard list of column definitions (e.g., the description field is always VARCHAR2(2000) for all applications)? * When to use nullable columns vs. mandatory columns? * When to provide column-level default values? * Should there be one global table for all reference codes or multiple reference code tables? * Which datatype to use: VARCHAR2 vs. CHAR? (c) Operating system filenames * Do these scripts contain the commands for creating database objects? What is the filename, and what is the file extension for each type of script? (d) PL/SQL coding standards * When to use stored procedures vs. functions vs. database packages * In-line documentation format * Code indentation -- Three characters? Should tabs be allowed? * Variable naming conventions: local vs. global vs. input/output parameters -- Use of underscore vs. mixed case (e.g., TotalEmployees vs. total_employees) * Procedure parameters: when to provide defaults? * PL/SQL type naming conventions: record type, PL/SQL tables, etc. * Cursors: when to declare cursors and how to use them * Coding style -- Conditional logic: how to code IF-THEN-ELSE -- Looping logic: use of WHILE vs. FOR vs. LOOP controls * Uppercase vs. lowercase vs. mixed case -- Oracle's recommendation is that Oracle keywords be entered in uppercase, and everything else in lowercase (e.g., SELECT emp_id FROM employees;) * Exception handling: how to raise exceptions, how to propagate exceptions to the calling routines, how to specify error message code and text * Debugging: how to trace program exception * Transaction handling and logging: do standard tables exist to log row changes? * Utility programs: are there utility procedures for logging, execution tracing, error reporting, etc.? (e) SQL coding * Define standards for writing SELECT, INSERT, UPDATE, and DELETE operations * SELECT statement -- Define rules for query hints -- Define table listing rules in the FROM clause -- Specify ordering of columns in the WHERE clause -- Specify the use of explicit data conversion functions in the WHERE clause * Indentation: how to line up the SQL clauses within each statement Here are some examples of our standards: (a) Object naming conventions * Table name _table_name where refers to the application name (e.g., HR_EMPLOYEES) -- Note that we use plurals for tables and don't use abbreviations. * Primary key: _table_name_PK (e.g., HR_EMPLOYEES_PK) * Unique keys: _table_name_UKnn (e.g., HR_EMPLOYEES_UK01) * Foreign keys: _table_name_FKnn (e.g., HR_EMPLOYEES_FK01) * Check constraints: _table_name_CHKnn (e.g., HR_EMPLOYEES_CHK01) * Indexes: _table_name_IDXnn (e.g., HR_EMPLOYEES_IDX01) * Stored program names -- First of all, we always implement stored programs using database packages; we don't have standalone procedures and functions. -- Package name: pkg_program_name (e.g., pkg_employees) -- Package public procedure/function name (e.g., p_insert_employee, f_count_employees) -- Package private procedure/function name (e.g., lp_check_employee, lf_count_departments) * Triggers -- _table_name_ (e.g., trg_employees_rbiud to denote row level, before insert/update/delete trigger) (b) Database table and column definitions * Use VARCHAR2 instead of CHAR datatype * For numeric columns, try to define them as NOT NULL with a DEFAULT value of 0. This greatly simplifies mathematical operations on these columns. * As a general rule, use one global reference table for all reference codes that remain static and share common columns -- i.e., code name, code value, and code description (e.g., gender). Create separate reference tables for codes that might change, and require native database support for foreign keys. (c) Operating system file names * We use .sps for the database package specification file and .spb for the package body script. * At the end of the SQL command for creating procedural logic, include the SHOW ERRORS command. In the next issue, I'll discuss our standards for SQL and PL/SQL coding. In the meantime, I welcome your comments regarding PL/SQL standards. Let me know if there are any particularly useful standards and techniques that you'd like to share with fellow subscribers. --------------------------------------------------------- 4) ANSWER TO THE POP QUIZ --------------------------------------------------------- Q1. What is the minimum block size for an Oracle database? Ans. 2K Q2. What is the maximum block size for an Oracle database? Ans. 32K Q3. What is the desired PCTINCREASE value for rollback segments? Ans. Always set to 0. In fact, it's not even part of the CREATE/ALTER ROLLBACK SEGMENT statement. Q4. Which of the following database columns requires more storage: (a) N1 NUMBER(38) (b) N2 NUMBER Ans. Same. The maximum precision for NUMBER datatype is 38. Q5. How much data is transferred in one Oracle I/O? Ans. This is determined by two initialization parameters: db_block_size and db_file_multiblock_read_count. If db_block_size is 2K and db_file_multiblock_read_count is 8, then one Oracle I/O corresponds to 16K. --------------------------------------------------------- 5) FUNCTION-BASED INDEXES --------------------------------------------------------- A reader mentioned that while Oracle's function-based indexes are useful, they're only available in the Enterprise edition! Well, next time you go to an Oracle conference and/or user group, perhaps you can apply some pressure to your Oracle representative! --------------------------------------------------------- Well, that's it for this week. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2001 http://www.pinnaclepublishing.com All rights reserved.