Pivot and Unpivot

These keyword was introduced in Oracle 11g. The UNPIVOT operator transforms the data arranged on a column into separate rows. With this utility it is possible to convert the display of the data to have them displayed on columns to have them on lines. In this example we talk about the prices associated with a ticket: UNPIVOT : SELECT * FROM tickets ; Applying the unpivot: pivoting on the ticket column it is possible to see the opening and closing price on separate lines. SELECT ticket,to_char(ticket_date,’YYYYMMDD’)ticket_date,price_type,price FROM tickets UNPIVOT ( price FOR price_type IN ( opening_price AS ‘OPEN’, closing_price AS Read More

What is cloud computing?

Aspects and definition of the SaaS, PaaS, DaaS, IaaS platforms. What is cloud computing? Possibility of relying on a specialized provider for the management of one or more IT resources that, from that moment on, are provided via the Web through an outsourcing contract. All this, without the company having to bear the costs of purchasing licenses or machines to take advantage of services indispensable to the business. the supplier to maintain all the infrastructure necessary to manage and distribute the services based on the request (on demand) and with a pay per use formula. All with a subscription to Read More

How to solve ORA-04021: timeout occurred while waiting to lock object

Compile an object can give the following error select s.sid, s.serial#, p.spid, s.username, s.osuser, s.program, s.terminal, s.module, s.action , p.tracefile , s.type , s.logon_time , s.state , s.wait_time, s.seconds_in_wait, s.service_name from v$session s, v$process p where s.paddr = p.addr ; Quire the process to find the session that still running select a.object, a.type, b.sid,b.serial#,s.inst_id, b.username, b.osuser, b.program, s.status, s.sql_exec_start from v$access a, v$session b, gv$session s where a.sid = b.sid and a.owner = DECODE(UPPER(‘&1’), ‘ALL’, a.object, upper(‘&1’)) and a.object = DECODE(UPPER(‘&2’), ‘ALL’, a.object, upper(‘&2’)) and b.sid = s.sid and b.serial# = s.serial# AND s.status = ‘ACTIVE’ order by a.object , Read More

Nested table

In PL SQL exists an efficient and adaptable collection of data: Nested table. Nested table is similar to one dimensional array but with some differences : An array has declare number of element – Nested table not. The size of nested table can increase using extend method. An array has always consecutive elements – Nested table has consecutive elements initially but it can become sparse when elements are deleted. To create a nested table you have to use this syntax : type type_name is table of element_type[size element]; table_name type_name; Here below an example to creation and use of a Read More