PL/SQL

select substr(sys_connect_by_path(col1, ','), 2)
from
(
       select rownum r, rownum col1 from dual
       connect by rownum < 5
)
where connect_by_isleaf=1
connect by prior r=r-1
start with r=1

select a3.d1, a3.d2, sum(t.val)
  from test2 t,
       (select a1.dt d1, a2.dt d2
          from (select dt, rownum rn
            from (select dt
                from test2
               where mod(to_number(to_char(dt, 'MI')) +
                         to_number(to_char(dt, 'HH24')) * 60,
                         20) = 0 --20 minutes
               order by dt)) a1,
               (select dt, rn
            from (select dt, rownum rn
                    from (select dt
                      from test2
                     where mod(to_number(to_char(dt, 'MI')) +
                               to_number(to_char(dt, 'HH24')) * 60,
                               20) = 0 --20 minutes
                     order by dt))) a2
        --join records with offset
         where a1.rn + 1 = a2.rn
         order by a2.dt) a3
 where t.dt between a3.d1 and a3.d2
 group by a3.d1, a3.d2
--output interval
having a3.d1 between 
       to_date('02.01.2012 00:00', 'DD.MM.YYYY HH24:MI') and 
       to_date('02.01.2012 23:59', 'DD.MM.YYYY HH24:MI')
 order by a3.d1

We create two identical sets of dates, ordered in ascending order, each subsequent element = previous + 20 minutes. We join them with an offset, getting consecutive pairs of intervals. We sum the val field, specify the date range in the condition. We filter the resulting set by a 1-day interval.

delete from emp
 where rowid in 
 (select rwd
      from (select rowid rwd,
           row_number() 
           over(partition by empno, 
                             last_name, 
                             first_name, 
                             job_id, 
                             dept_id, 
                             salary 
                    order by empno, 
                             last_name, 
                             first_name, 
                             job_id, 
                             dept_id, 
                             salary) rn
              from emp)
 where rn > 1)

We group using the analytic function PARTITION OVER by all fields (full duplicate), number the records in groups. Select all those with a sequence number greater than 1 and delete by ROWID

select empno,
       sum(decode(rn, 1, a_value, null)),
       sum(decode(rn, 2, a_value, null)) from(
select ev1.empno,
       ev1.a_value,
       row_number() over
        (partition by ev1.empno order by ev1.empno) rn
      from Alex.Emp_Values ev1
      order by rn, ev1.a_value)
      group by empno
      order by empno

Example: If a citizen has a passport and also an international passport, it is displayed in the second column

select rownum from dual connect by rownum < 10;

CREATE TYPE SCOTT.PERSON_T
AS
OBJECT (
name VARCHAR2(100),
ssn NUMBER
);
 CREATE GLOBAL TEMPORARY
TABLE SCOTT.TMP_OBJ    
(
PERSON PERSON_T
)    
ON COMMIT DELETE ROWS;

begin 
  dbms_session.set_context('CLIENTCONTEXT', 'n_param', 1); 
  dbms_session.set_context('CLIENTCONTEXT', 'v_param', 'Y'); 
end;

select sys_context('CLIENTCONTEXT', 'n_param') from dual;
select sys_context('CLIENTCONTEXT', 'v_param') from dual;

We create a user-defined context. MY_CONTEXT_API - a package whose functions are granted write permissions on the context during context initialization.

create or replace context MY_CONTEXT using MY_CONTEXT_API;

Now we can initialize a variable in the user-defined context MY_CONTEXT using the procedure

procedure set_my_context(p_var  varchar2,
                         p_val  varchar2)
is
begin
   dbms_session.set_context('MY_CONTEXT', p_var, p_val);
end;

Creation and access would look like this:

begin
   MY_CONTEXT_API.set_my_context(p_var => 'p_var', p_val => 'Y');
end;

select sys_context('MY_CONTEXT', 'p_var') from dual;

with a as (
      select 1 pk, 'Answer1' answr, 'Y' flag from dual
      union
      select 1, 'Answer2', 'Y' from dual
      union
      select 1, 'Answer3', 'N' from dual
      union
      select 1, 'Answer4', 'N' from dual
    ) 
    select pk, 
           "'Answer1'_ANS" Answer1W,
           "'Answer2'_ANS" Answer2W,
           "'Answer3'_ANS" Answer3W,
           "'Answer4'_ANS" Answer4W
    from (
    select * from a
     pivot (max(flag) ans FOR answr IN ('Answer1', 
                                        'Answer2', 
                                        'Answer3', 
                                        'Answer4'
                                       ))
     );

And vice versa:

select *
  from XXIP310_pivotview_tmp
  unpivot(flag for answr in(answer1w as
                           'Answer1',
                           answer2w as
                           'Answer2',
                           answer3w as
                           'Answer3',
                           answer4w as
                           'Answer4'))

select c.owner,
       c.object_name,
       c.object_type,
       b.sid,
       b.serial#,
       b.status,
       b.osuser,
       b.machine,
       decode(a.locked_mode,
              0, 'None',
              1, 'Null (NULL)',
              2, 'Row-S (SS)',
              3, 'Row-X (SX)',
              4, 'Share (S)',
              5, 'S/Row-X (SSX)',
              6, 'Exclusive (X)',
              a.locked_mode) locked_mode
  from v$locked_object a, v$session b, dba_objects c
 where b.sid = a.session_id
   and a.object_id = c.object_id
   and object_name like '%XXXX%';

lockmode Clause
Specify one of the following modes:
ROW SHARE ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.
ROW EXCLUSIVE ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.
SHARE UPDATE See ROW SHARE.
SHARE SHAREpermits concurrent queries but prohibits updates to the locked table.
SHARE ROW EXCLUSIVE SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.
EXCLUSIVE EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.

select rownum rn,
         DBMS_RANDOM.STRING('a', 8) str_rnd,
         round(DBMS_RANDOM.value(1, 5)) num_rnd,
         trunc(sysdate - DBMS_RANDOM.value(1, 1000)) date_rnd
    from dual

select rownum ara
 ,to_char(rownum,'fmrn') rom
from all_objects where rownum < 4000 

declare
    CURSOR A(c_tab IN varchar2) IS SELECT TABLE_NAME FROM USER_TABLES
      WHERE TABLE_NAME LIKE c_tab;
    l_table varchar2(30);
  begin
    open A(c_tab => 'A%');
    loop
      fetch A into l_table;
      exit when A%NOTFOUND;
      dbms_output.put_line(l_table);
    end loop;
    close A;
  end;
  /

select to_char(systimestamp, 'HH24:MI:SSXFF') from dual;

select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
'NLS_LANGUAGE', 'LANGUAGE',
'NLS_TERRITORY', 'TERRITORY') name,
value from v$nls_parameters
WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')