Sunday, August 31, 2014

Agile - Principles and values


One of the most important things to remember is that AGILE is NOT
- A set of tools
- Process
- Methodology

AGILE is a set of values.

The Agile Manifesto as per wikipedia reads, in its entirety, as follows:

We are uncovering better ways of developing software by doing it and helping others do it. Through this work we have come to value:
  • Individuals and interactions over Processes and tools
  • Working software over Comprehensive documentation
  • Customer collaboration over Contract negotiation
  • Responding to change over Following a plan
That is, while there is value in the items on the right, we value the items on the left more.
As we had mentioned in our previous agile article, continuous feedback is very important.
Point 1 above is on similar lines where continuous feedback is more important when we mentioned "individuals and interactions".

Working software will definitely get more value over documentation, but it does not imply that documentation is not at all needed.

With time a customers needs can change. Even due to marketing dynamics, needs can change. Hence, customer collaboration and continuous checks on delivery will help to deliver a model which the customer can appreciate.

As mentioned, market dynamics and conditions may make the customer change his requirements. Incremental reviews and phase wise delivery and continuous feedback and collaboration will help us deliver a product close to customer's needs (at that point in time).

The Agile Manifesto, as per wikipedia, is based on twelve principles:
  • Customer satisfaction by rapid delivery of useful software
  • Work closely with the customer and provide timely incremental delivery of the product
  • Incremental reviews for customer feedback
  • Capture acceptance criteria on each discussion
  • Prioritize the pending requirements
  • Conduct surveys to find the satisfaction level of the customers

  • Welcome changing requirements, even late in development
  • It is widely accepted that customer needs can change with time due to market dynamics
  • One needs to embrace this change to provide value to the customer
  • Evolve a process that accepts these changes
  • Changes need to be prioritized with existing requirements
  • Working software is delivered frequently (weeks rather than months)
  • Frequent incremental delivery of software.
  • These are sprint reviews (may be in a couple of weeks and not months).
  • Automated, performance and load testing plays an important part in testing such small delivery cycles continuously.
  • Close, daily cooperation between business people and developers
  • In non-agile models, business and development teams rarely interact. This could lead to a delivery of a product that may not adhere to the customers need at that point in time.
  • There should be frequent interaction between business and development teams.
  • Development team benefits from knowing the business perspective (about the function of the software)
  • Business teams gets insight into development details that they may not have an idea about when they conceive the product needs.
  • Projects are built around motivated individuals, who should be trusted
  • Motivated employees need to be encouraged and involved in the product.
  • Employees feel empowered and result in higher output and better product
  • Face-to-face conversation is the best form of communication (co-location)
    • Lot has already been mentioned about communication, hence not delving much into this point.
  • Working software is the principal measure of progress
  • Self explanatory - a working software is a measure of progress.
  • In waterfall, there is no working software at any point in time (in the schedule) except at the end. Thus, from customers perspective, there has been no progress to see.
  • For every incremental release, there should be a working demo which should go through automated set of testing.
  • Customer feedback is gathered at the end of the demo and any new changes need to be prioritized and embedded for the next subsequent releases.
  • Sustainable development, able to maintain a constant pace
  • Pretty much self explanatory, constant pace need to be maintained.
  • However, need to ensure that members of teams do not work crazy hours/weekends (maintain 40 hours a week)
  • Continuous attention to technical excellence and good design
  • I guess this is a no brainer. Team members need to have the the technical expertise to deliver a quality product.
  • There should also be a focus to continuously learn and improve upon coding standards, design, integration and testing.
  • Simplicity—the art of maximizing the amount of work not done—is essential
  • Reduce non value added work of team members.
  • Simplify process of deployment or remove extra features etc
  • Self-organizing teams
  • As the product evolves, there is a need to change the architecture, requirements, design etc. Team members must be involved in decision making leading to greater ownership and responsibility.
  • This is not easy and requires a lot of team work and an open culture that promotes team based rewards etc
  • Regular adaptation to changing circumstances
  • Periodically, there should be an introspection on how to become more effective and be able to adapt to changing demands and requirements.
  • Self organizing teams are the key to real improvement.
  • There should be a commitment to adapt and improve with every iteration over a period of time.

Thursday, August 28, 2014

Some 12C Features

Will try to describe some of the 12C features and will keep editing this post.

Note:
  • If you want to install Oracle 12 on Windows 7, refer the steps here 
  • To troubleshoot invalid common user or role name after installation, go here

Feature List

 Identity Columns:
  • Also known as auto-number column. This implies that the data in the column should increase when data is inserted).
  • Read article on the same here.

 Invisible Columns:
  • Columns can be made invisible
  • It gives one the ability to introduce a change while minimizing any negative side effects of that change.
  • Read article on the same here 
With Clause:
  • Instead of using a stored PL/SQL function, we can include the body of the function in the SQL query
  • Read article on the same here 
 Truncate statements with the cascade option
  • We could not truncate table with foreign key constraints.
  • Now in 12c, one can execute the truncate table command with the cascade option (to cascade through the child tables).
  • Note: You would need to create the foreign key with the "on delete cascade" option
Columns can have a default value in place of NULL
  • You can define a column to have a default value when NULL is inserted
  • e.g. create table ......  product_id NUMBER ON NULL 0,.....
  • So if one explicity inserts NULL, it will be populated by value 0
Multiple Index - same columns
  • You can create multiple indexes (different types) on a same set of columns.
  • Assume you need to change from B*tree to Bitmap or vice versa.
  • You need not drop the index.
  • Just make the first index invisible and make a new index.
  • Conditions:
    • Different types of indexes
    • Different uniqueness
    • Different partitions are used
 



Tuesday, August 26, 2014

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).



Saturday, August 23, 2014

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.

Thursday, August 14, 2014

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"





Wednesday, August 13, 2014

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.