12 C Feature - WITH Clause

Note: Index article on some 12c features can be seen here

The with clause is more efficient to use than sub queries.
Instead of using a stored PL/SQL function, we can include the body of the function in the SQL query.
Benefit is that it will run faster than the call of a function by the same statement.

This can be better explained with an example.
Lets create a table with some test data.

Look at the usage of the function using with - in the body of the query.

The function in the with clause over-rides the function created at schema level. In below screenshot, you can see the schema level function returning SYSDATE+1 and the with clause function returning SYSDATE+2. Check the output - its SYSDATE+2.

Note: You need to put in a slash (/) at the end else SQLPLUS waits for more input (refer lines 9-12 in the snapshot waiting when ENTER key was pressed).

12 C Feature - Invisible Columns

Note: Index article on some 12c features can be seen here

The name is self explanatory.
Invisible columns means just that -- columns that are invisible.
It gives one the ability to introduce a change while minimizing any negative side effects of that change.

So, in a table, if we add a column, it will show up in a "select *" query.
All the "insert into <table> values ...." queries will break.

When you add an invisible column, you won't be able to see it even when you execute a "DESC" or when you run a "select *" query.
The "insert into <table> values ...." queries will not break.
However, you will able to see it if you specifically query for it by name.

Lets have a look at an example.
We have a table "TEST_IDENTITY" which we had used to test the identity column. Refer article on identity column

Lets add an invisible column to it.
The command will be:

ALTER TABLE <TABLE_NAME> ADD (<COLUMN_NAME> <TYPE> INVISIBLE);

As you can see below, I added a column name as INVISIBLE and it does not show up in DESC command.
It does not show up in "select *" and has no impact on the "insert into <TABLE_NAME>" as well.

But you can insert data into it and also do a select by calling it explicitly.

You can make the column visible by running the following alter command:
ALTER TABLE <TABLE_NAME> MODIFY <INVISIBLE_COLUMN_NAME> VISIBLE;

Some questions that may help:

How to know a table exists with an invisible column?

Run a query on USER_TAB_COLS
SELECT COLUMN_NAME, HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME = '<TABLE_NAME>';

What if I create an invisible column as NOT NULL?
INSERT will fail. You need to create with DEFAULT option or use a trigger to populate on INSERT.

Is there any massive advantage of this feature?
Its a feature which could be used but not any with massive advantage. it could create problems for some applications when made visible, but if you have coded with proper INSERTs/SELECTs (mentioning columns), it won't harm your application.

12 C Feature - Identity Columns

Note: Index article on some 12c features can be seen here 
 
Identity Columns

What is an Identity column?
This is also known as auto-number column. This implies that the data in the column should increase when data is inserted.

In previous version of Oracle 12c, this was accomplished in the one of the following two ways:

• Create a sequence
• Insert the sequence.nextval in the INSERT STATEMENT

OR

• Create a sequence
• Create a Trigger "BEFORE INSERT ON TABLE"

Trigger created:
CREATE OR REPLACE TRIGGER test_trg
BEFORE INSERT ON testseq
FOR EACH ROW
BEGIN
 SELECT test_seq.nextval INTO :new.id FROM dual;
END;


In Oracle 12c, the same thing can be achieved without the use of sequence and triggers in two ways:

Column default clause (with sequences)

Create a sequence and use it as part of the table creation.
One can specify sequences (NEXTVAL or CURRVAL) as default column values. When a new row is inserted into the table, the sequence value is automatically inserted.

Look at the screen shot below where I have created a sequence and a table.
You can see that we can also put in out own data (data with value like 100 in the example or null) and it overrides the value from the sequence.

I have made the column not null, and we can see on inserting null, it throws an error.

Using GENERATED as IDENTITY in Column definition:

Here I will show example of identity columns which can be specified in the CREATE TABLE and ALTER TABLE statements.

Create a table using the IDENTITY clause.

Create table test_identity
(
  id NUMBER GENERATED as IDENTITY,
  name VARCHAR2(100)
);

When you do a describe, you shall see that the column is by default NOT NULL;

Running a query on DBA_SEQUENCES shows a new SEQUENCE created by the name ISEQ$$_92446 (system-generated name in the format ISEQ$$_<objectID> where objectID is the object id of the table)

Also note that you cannot alter an existing non-identity column to become an identity column.

Difference between "Column default clause" and "Using GENERATED as IDENTITY"

ORA-65096: invalid common user or role name in Oracle 12c

Note: Index article on some 12c features can be seen here 

ORA-65096: invalid common user or role name in Oracle 12c

This error usually occurs due to we are trying to create user ( common user ) under root container.

In oracle 12c there are two type of users: common and local.

Common users belong to CBD’s as well as current and future PDB’s.

The user can perform operation in Container or Pluggable according to Privileges assigned


Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB.

Refer screen shots below on creation of user's in both containers.

Note that  users in CBD$ROOT container must start with C##

For the local user:

Please do note that you will need to make an entry in the tnsnames.ora for the container (with the service name). There will already be an entry for the CBD$ROOT container by default. You can copy the same and change the name and service name.

Do "grant create session to test;"
Since this is my test schema, I have also run "Grant UNLIMITED TABLESPACE to TEST;" (else you may encounter ORA-01950: no privileges on tablespace 'USERS')

You can then connect to test using "conn test/test@pdborcl;"

Installing Oracle 12c Database on Windows 7 (64 bit)

Note: Index article on some 12c features can be seen here 


Kindly peruse through the below steps (detailed) for installing Oracle 12 c DB on Windows 7 (64 bit):

Step 1.
Download the required binaries from Oracle website

Step 2.
Once downloaded, unzip the files into one folder. I created a folder "Oracle 12c" in D: and another folder in it called "database"

So my folder structure was D:\Oracle12c\database

Step 3.
Click on setup.exe

Step 4.
You could put your email address and request for support (if needed). I have not selected this option for this install.

Step 5.
I chose to skip software updates

Step 6.
Select "create and configure a database"

Step 7.
I have chosen "server class"

Step 8.
Type of DB installation - I chose Single

Step 9.
Choose Typical install and in the Next screen create a new Windows user (you can use an existing one if you want)

Step 10.
I changed the global database name to "orcl" and created a Pluggable DB "pdborcl"

Step 11.
After verification, click on install and await for the installation to complete.

Step 12.

Open a command prompt and test your install

Note: If you try to create a schema under root container, you may encounter ORA-65096 error.
For resolution of the same, have a look at the article here.

How Gamification helps businesses

According to Wikipedia, “gamification is the process of using game thinking and game mechanics to solve problems and engage users.”

Gamification, like the name suggests, selectively uses the mechanics that bring out people’s natural desires for competition and achievement in the form of a game.

In the business world, gamification is about driving business objectives and motivating people through data. Gamification may be among the latest tech industry buzzwords, but the concept of using games and gaming tactics to engage users is not new at all.

Credit card companies and airlines have been using the concept of gamification for years to entice customers to earn rewards through their brands.

Lets apply gamification and game mechanics to a community site.
A game has many mechnics which work closely on human desires:

• Point system for activities like creating, rating and sharing content (on their social networks) feeds the human desires of competition, achievement and satisfaction of getting rewards.
• Leader boards works on the principles of recognition and achievement.
• Badges - earned and shared on social sites feed on the human needs of self expression along with the above mentioned needs like status, reward,recognition, competition etc

Many popular websites are already gamified. Twitter displays the # of tweets, followers and following for each user. One may disagree on  how it helps, but many folks may follow folks with huge # of followers unconsciously. It works on the human mind.

But in the business world, for any gamification project to succeed, businesses need to align the goals of players with those of the organization itself. If your business wants to decrease support costs, for example, encourage members to post content and answer questions and reward them for doing so.

Businesses need to understand who their players are, what motivates them and what goals do they have - then design an experience that helps them to get more involved  and take them along a path to achieve their (and in turn the companies) goals. Some gamification vendors offer analytics as part of their products (which is a huge business opportunity as well).

Another benefit to having customers log in and participate on your website is that it allows you to use them as a sounding board.

Lets look at a community site example:

First time user logs into a community

This promotes participation and helps members feel involved in the community.

User helps businesses while feeding his/her human desire(s) (rewards,recognition, self achievement, self expression etc)

Do remember that over time people want increasing rewards for the same activity in order to feel motivated. Businesses need to be constantly on their toes to keep their customers involved.

Reset sa users password in SQL Server Express edition

• Click on Start > Programs > SQL Server Management Studio Express
• If SQL Server Management Studio Express is not installed, install from the following URL: http://www.microsoft.com/en-ca/download/details.aspx?id=8961
• Login as Windows admin
• Click on security
• Click on logins
• Right click on sa
• Change the password and click ok