Triggers in Oracle 11g -> Execution Order in Oracle 11g

Before Oracle 11g, more than trigger can be created on one table but Oracle doesn't guarantee the order of execution of trigger.  Oracle has introduced the feature in Oracle 11g to execute the trigger in any order as per requirements.

Let’s look the example:

Virtual Column in Oracle

Oracle has introduced new feature in version 11 ie. Virtual column. Virtual column values are not stored on disk. They are generated at runtime using their associated expression.

Lets have a look how to create a table with Virtual Column 

CREATE TABLE testvirtual
(
   id1   NUMBER (10)
 , id2   NUMBER (10)
 , id3   NUMBER (10) GENERATED ALWAYS AS (id1 + id2) VIRTUAL
);
Virtual keyword is optional.

Table Compression in Oracle 11g

Table compression was first introduced in Oracle 9i as a space saver feature only for data warehousing projects. In oracle 11g , this is acceptable as mainstream feature which is acceptable for OLTP Databases. This will result in saving storage space as this will compress the table, increased I/O performance and reduced memory use in the buffer cache. Definitely, this incurs CPU overhead because of compression.

Compression can be done at the tablespace, table or partition level . Options/Features which are available with Compression :

Regular Expression In Oracle

REGULAR EXPRESSION:

            A regular expression is a string or set of characters that describes the structure of some text .This expression helps to search a particular pattern on the line. It is composed of Literals and Metacharacters.

Drop columns from table in fastest way

Columns in the oracle table can have more than 1000 rows of data. If the user asked to drop such columns from the table, dropping the same will take significant amount of time.

By using “Set Unused Column”, it is possible to reduce the significant amount of time while dropping the column from the table.

The syntax used to drop the column from the table is:

Alter table Table Name drop column Column Name;

ORA-32332: Resolution

ORA-32332: Resolution

ERROR at line 1:

ORA-32332: cannot refresh materialized view "Materialized View Name"

as type evolution has occured

Resolution:

1)      Check if the materialized view is valid .

SELECT * from dba_objects where object_name =<view_name>

2)      Check if underneath compoennts are valid

3)      If Underneath all tables are there, check if any field has been added to the same.

If yes, get a complete refresh for materialized view.

In my case, one of the underneath view was invalid.

How to Use SYS_CONNECT_BY_PATH in Oracle

SYS_CONNECT_BY_PATH :

Syntax:

SYS_CONNECT_BY_PATH is a function which is used in hierarchical queries to get the path for the current node starting from Parent node.

SELECT SYS_CONNECT_BY_PATH ( column , char)

FROM table_name

START WITH <root_node>

CONNECt BY NOCYCLE PRIOR < child_node_col> = <Parent_node>

How to replace Special Characters in Oracle

How to replace Special Characters in Oracle

Use regexp_replace (column_name,'[]~!@#$%^&*|?]', NULL ) to replace Special character .

New Blog for Selenium Automation

Hello

Please refer to below blog for any assistance required in Selenium Automation. Infact, you can Request the topic you want me to write about and i would be more than happy to add a blog on the same.

http://automationtesters007.blogspot.com.au/

Happy Learning
The Software Professionals.

How to convert single column to multiple rows in Oracle

Example 1 ->  Take the below Example :

CREATE TABLE config_value

(

   Config_id    VARCHAR2(40)

 , config_code    VARCHAR2(100)

);

How to use 'Case' statement in Oracle

Case Statement

Syntax:

CASE [ expression ]

   WHEN condition_1 THEN result_1

   WHEN condition_2 THEN result_2

   ...

   WHEN condition_n THEN result_n

   ELSE result

END

Let’s take few scenarios to Implement Case Statement: