Sunday, January 3, 2016

DB: Logical Reordering of Columns

Applicable to 12c

Following post will help you to logically(not physical) reorder the columns in a table with the help of invisible columns feature in 12c.

Let's say, we have a table TAB1 having 2 columns COL1, COL2.

CREATE TABLE TAB1
  ( COL1 NUMBER,
    COL2 NUMBER);

Now suppose that, we need to add new column COL3 and it should appear in middle of the table.. We could achieve this column reordering without using invisible columns as follows.
  • Rename the table TAB1 to something, For Ex: TAB2
  • Add new column COL3 to table TAB2, which would appear at the end of the column list.
  • Create a view or an editioning view named TAB1 that explicitly selects the columns in the right order
  • Revoke the grants on TAB2 and issue those grants on the view TAB1
  • Update the scripts(indexes, triggers, constraints) to refer renamed table TAB2
This may help us to make the column seem to appear in the middle of the table, but require lot of efforts. But we can achieve the the logical reordering with minimal efforts using invisible columns feature in 12c as follows.
  1. First hide the column COL2 and Add column COL3. At this point, column COL3 will appear to be right after column COL1.

  2. ALTER TABLE TAB1 MODIFY COL2 INVISIBLE;
    ALTER TABLE TAB1 ADD COL3 NUMBER;

  3. Next unhide column COL2 and it will appear after column COL3 logically.

  4. ALTER TABLE TAB1 MODIFY COL2 VISIBLE;

  5. Describe the table TAB1

  6. DESC TAB1

     Name       Type
     —————   ——————
     COL1        NUMBER
     COL3        NUMBER
     COL2        NUMBER

But physically these columns will be stored as COL1, COL2, COL3 on disk.

DB: Virtual and Invisible Columns

Following post will help you to understand the usage of couple of new features introduced in Oracle database.

Virtual Column (Introduced in 11g R1):

Virtual columns are expressions that are based on one or more existing columns in the table. A virtual column is only stored as metadata. It does not consume physical space, but it can be indexed. The virtual column also contains optimizer statistics and histograms.

When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.

Syntax: column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

Benefits:
  • Automatic re-computation of derived columns for ad-hoc query tools
  • Reduction in redundant disk space for columns that must be derived from other columns
  • Using virtual columns in tables also eliminates the need to use views to display derived column values.
  • Using a virtual column also simplifies the use of derived columns. Transparently derived values do not require the application to calculate and insert an additional value. This also prevents the need to use a trigger on the table to provide an alternate implementation of this functionality.

Limitations:
  • Virtual columns may not reference other virtual column values:
  • Virtual columns only work within the specified table, and you cannot reference columns within other tables.
  • Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
Ex:
CREATE TABLE EMP (
  id            NUMBER,
  name      VARCHAR2(100),
  salary      NUMBER(9,2),
  commission  NUMBER GENERATED ALWAYS AS (ROUND(salary/100,2)) VIRTUAL,
  CONSTRAINT emp_pk PRIMARY KEY (id)
);

INSERT INTO EMP (id, name, salary)  VALUES (1, 'LKAKARLA', 100);

SELECT * FROM EMP;

Invisible Column (Introduced in 12c R1):

This new feature in 12c allows to make invisible columns for generic queries, operations. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns.

Syntax: 
Invisible Column: column_name [datatype] [INVISIBLE]
Invisable Virtual Column: column_name [datatype] [INVISIBLE] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Benefits:
  • Hide important data - columns from being displayed in the generic queries
  • Invisible columns can be useful during application migrations. Making new columns invisible means they won't be seen by any existing applications, but can still be referenced by any new applications, making the online migration of applications much simpler.
  • Virtual columns can be made invisible.
  • Ability to logically reorder the columns in a table (refer link)

Limitations:
  • Temporary tables, external tables and cluster tables won’t support invisible columns.
  • Following operations don’t see invisible columns
    • SELECT * FROM statements in SQL
    • The DESCRIBE statement when used in either SQL*PLUS or via Oracle Call Interface (OCI) (SET COLINVISIBLE ON in SQL*PLUS to see invisible columns)
    • %ROWTYPE attribute declarations in PL/SQL
Ex:
CREATE TABLE EMP (
  id            NUMBER,
  name      VARCHAR2(100),
  salary      NUMBER(9,2),
  dob         DATE INVISIBLE,
  commission  NUMBER GENERATED ALWAYS AS (ROUND(salary/100,2)) VIRTUAL,
  bonus       NUMBER INVISIBLE GENERATED ALWAYS AS (ROUND(salary/200,2)) VIRTUAL,
  CONSTRAINT emp_pk PRIMARY KEY (id)
);

INSERT INTO EMP (id, name, salary, dob)  VALUES (1, 'LKAKARLA', 100, SYSDATE);

DESC EMP;

SET COLINVISIBLE ON;

SELECT * FROM EMP;

SELECT ID, NAME, SALARY, DOB, COMMISSION, BONUS FROM EMP ;

ADF: JSTL with ADF Faces Rich Client Components

Following post will help you to understand the usage of JSTL along with ADF Faces.

The JavaServer Pages Standard Tag Library (JSTL) is a collection of useful JSP tags which encapsulates core functionality common to many web application functionalities.

Here I listed commonly used JSTL expressions:

Function
Description
fn:contains()
Tests if an input string contains the specified substring.
fn:containsIgnoreCase()
Tests if an input string contains the specified substring in a case insensitive way.
fn:endsWith()
Tests if an input string ends with the specified suffix.
fn:escapeXml()
Escapes characters that could be interpreted as XML markup.
fn:indexOf()
Returns the index withing a string of the first occurrence of a specified substring.
fn:length()
Returns the number of items in a collection, or the number of characters in a string.
fn:replace()
Returns a string resulting from replacing in an input string all occurrences with a given string.
fn:startsWith()
Tests if an input string starts with the specified prefix.
fn:substring()
Returns a subset of a string.
fn:substringAfter()
Returns a subset of a string following a specific substring.
fn:substringBefore()
Returns a subset of a string before a specific substring.
fn:toLowerCase()
Converts all of the characters of a string to lower case.
fn:toUpperCase()
Converts all of the characters of a string to upper case.
fn:trim()
Removes white spaces from both ends of a string.


















You need to import JSTL namespace (xmlns:fn="http://java.sun.com/jsp/jstl/functions") into your page/fragment to refer JSTL tags.

<jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1"
          xmlns:af="http://xmlns.oracle.com/adf/faces/rich"
          xmlns:f="http://java.sun.com/jsf/core"
          xmlns:fn="http://java.sun.com/jsp/jstl/functions">

JSTL Sample Examples: 
1. Get Records count in an Array List:
<af:outputText value="#{fn:length(pageFlowScope.empBean.employeeList)}" id="ot1"/>
2. Match content of String:          
<af:outputText value="#{fn:contains(bindings.deptName.inputValue,'SALES') ? 'SALES' :  'OTHER'}" id="ot1"/>

For more details, refer link