Thursday, 22 June 2017

June 22, 2017

Hard-coding vs. Soft-coding in PL/SQL and SQL

 , , , , , , , , ,  2 comments


Thanksto Steven Feuerstein for his awesome work on pl/sql.
This post is mostly summary and compilation with useful links at the end.
All the files relating to hard-coding can be downloaded from here.🔗

Hard-coding

Hard- coding, usually involves using Literal values, would be fine if nothing ever changed in our applications, only and only if:
  • – Requirements, Tables' Definitions, Rules & Formulas and Configuration Constants  stayed the same...
Whenever anything changes, you have to find all the places you explicitly coded it, and fix them.

Soft Coding

– Rather than explicitly code values, rules and algorithms, make them "soft" or dynamic –changeable and settable at runtime.
and here is my definition:

Soft coding involves a way to code the definition , values, rules and algorithms which incorporate such a level of flexibility and manageability that any foreseeable technical and business requirements could be met by it ,without any significant change in the original code.

The concept is
  • Repeat nothing: become allergic to redundant repetition.
  • Aim for a "single point of definition"(SPOD) in everything you write and thus a great tool for refactoring.
  • Information hiding(Data Encapsulation) – Hide, hide, hide: values, implementations, workarounds
    • – Avoid exposing the implementation details of formulas,rules, algorithms, data access.
    • – The more you hide, the more flexibility you have.

  • "Never" and "Always" in software
    • – It's never going to stay the same.
    • – It's always going to change.
 
So Lets take quick look on the various hard-coding and its solutions.
Literal values
  • – Especially language-specific literals, Limits, Statuses,Flags etc. should only be in SPOD
  • Code files:
    • thisuser*.*
    • soft_code_literals.sql
Solution
  • :Hide literals behind:
      • Create a Packaged Constant, assigning the value to that constant.
        • Fast on Retrieval
        • Some time the additional typing need by placing package name as the prefix, however this can be avoided if the constants are used within the package and thus can be defined in the package body and referenced by the constant name directly
      • Create a Function that hides and returns the value.
        • Pros are
          • Encapsulation Hides the value in the Package body
          • and dynamic, easy to refactor
        • Cons are Performance Overhead
          • The function can be deterministic as the value is predetermined(never changing) and there are no external dependencies in the function , and which can add to optimized performance
        • Still more expensive to call a function than to reference a constant
      • Soft code in tables , Store and manage the value in a database table , soft code it.
          • – The Most Dynamic
          • – More complex code
          • – More overhead, but caching can avoid this problem.
Constrained declarations
  • – Every declaration of the form VARCHAR2(N) to be a bug. – Unless it is the SPOD for that type.
  • Code Files:
    • fullname.pks
    • plsql_limits.pks
    • string_tracker3.*
Solution:
  • Instead, anchor the declaration back to its "source".
    • – %TYPE for variables based on columns
    • – %ROWTYPE for records based on table/cursor
    • –  OR if you can't anchor back to a DB element? use  SUBTYPES for all applications-specific types
      • Critical when working with complex structures like collections of records, and nested collections.
      • Example of the hardcoded values
        l_full_name VARCHAR2(100);
        l_big_string VARCHAR2(32767);
      • And using subtype
        CREATE OR REPLACE PACKAGE employee_rp As
        SUBTYPE fullname_t IS VARCHAR2 (1000);

        Then in your code use
        l_full_name employees_rp.full_name_t;
        l_big_string plsql_limits.maxvarchar2;
    Rules and formulas
    • – Especially the "trivial" ones, more critical than repeated literals
    • – They will change & get more complex.
    • Code Files:
      • get_time.sql
      • sf_timer.*
    Solution
      • – Learn to recognize rules and formulas. Often they are missed, especially when simple.
      • – Hide the logic behind functions.
    • Example: Never trust a rule!
      • DBMS_UTILITY.GET_TIME andGET_CPU_TIME (10g) use to compute elapsed time down to the hundredth of a second
      SQL statements
      • - Every SQL statement that you write is a hard-coding! difficult to contemplate
      • – PL/SQL is, in fact, the best place for SQL.
      • – Follow the principles; they are your guide.
      • – Don't repeat anything!
      Solution
      • :hide SQL inside a data access layer.
        • Think of SQL as a service that is provided to you, not something you write.
        • – Or if you write it, you put it somewhere so that it can be easily found, reused, and maintained.
        • This service consists of programs defined in the data access layer.Known as
          • –  table APIs,
          • –  transaction APIs,
          • –  or data encapsulation
        • these programs contain all the intelligence about business transactions and underlying tables.
        • Pros:
          • Change/improve my implementation with minimal impact on my application code.
            • – The underlying data model is constantly changing.
            • – We can depend on Oracle to add new features.
            • – We learn new ways to take advantage of PL/SQL.
          • Vastly improve my SQL-related error handling.
            • – Do you handle dup_val_on_index for INSERTs,too_many_rows for SELECT INTOs, etc?
          • Greatly increase my productivity
            • – I want to spend as much time as possible implementing business requirements.
        • Cons: checkout the stackoverflow Tapi Vs Xapi discussion in the links at the end 
        • For each table, we have these generated packages:
          • – <table>_CP for DML
          • – <table>_QP for queries
          • – <table>_TP for types
          • And the Coded – <table>_XP "extra stuff" package with custom SQL logic and related code
        • It must be very consistent, well-designed and efficient or it will not be used.
        • Best solution: generate as much of the code as possible.
          • – And any custom SQL statements should be written once and placed in a standard container (usually a package).
      Hide Algorithmic details
      • Assume that whatever you are working on will change – and hide it behind an API.
      • – Logging errors
      • – Function result cache
      • – Manipulating collection contents
      • Code Files:
        • 11g_emplu.pkg
        • string_tracker3.*
        • cc_smartargs.pkb
      • – Example: error logging mechanism
        • We usually, but not always, want to write error information out to a log table. 
      WHEN NO_DATA_FOUND THEN
l_code := SQLCODE;
INSERT INTO errlog
VALUES ( l_code, 'No company for id ' || TO_CHAR ( v_id ), 'fixdebt', SYSDATE, USER );
WHEN OTHERS THEN
l_code := SQLCODE; l_errm := SQLERRM;
INSERT INTO errlog
VALUES (l_code, l_errm, 'fixdebt', SYSDATE, USER );
RAISE;
END;
        • Now using a shared, generic logging utility , we re-write based on the Quest Error Manager, so You do less work and get more information
        • Download form Here: Quest Error Manager
      WHEN NO_DATA_FOUND
THEN
q$error_manager.register_error (text_in => 'No company for id ' || TO_CHAR ( v_id ));
WHEN OTHERS
THEN
q$error_manager.raise_unanticipated (name1_in => 'COMPANY_ID', value1_in => v_id);
END;
        • The Oracle11g function result cache
          • The optimal way to query and cache data changes over time.
            • – Explicit and implicit cursors
            • – FORALL and BULK COLLECT
            • – And now the function result cache
          • With the Result Cache,provided the same(matching) inputs as parameters the  function return the data previously cached with   
      FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
RETURN employees%ROWTYPE RESULT_CACHE;
        • Manipulating collection contents
          • Collections are Oracle's version of arrays in PL/SQL.
          • – A relatively complicated but critically important data type.
          • Best to hide collection references behind procedures and functions.
          • – Especially when you work with string-indexed and multi-level collections.

      Further Reading

      PDFs
      Say-Goodbye-to-Hard_2D00_Coding.pdf
      Hardcoding 1
      Hard Coding 2

      Hardcoding

      YouTube -Hard Coding Playlist
      https://www.toadworld.com/platforms/oracle/b/weblog/archive/2011/09/15/avoiding-hard-coded-values-in-views
      https://jeffkemponoracle.com/2010/03/02/please-hard-code-your-literals/
      http://pretius.com/how-to-avoid-hard-coding-in-your-plsql-code/

      SQL hardcoding , TAPIs, XAPIs & Data Access Layer

      https://jeffkemponoracle.com/2010/06/23/tapi-vs-xapi/
      https://stackoverflow.com/questions/3092041/understanding-the-differences-between-table-and-transaction-apis?answertab=active#tab-top

      Read More