xtender

create or replace type emp_t as object(
    employee_id      number(6)   ,
    first_name       varchar2(20),
    last_name        varchar2(25),
    email            varchar2(25),
    phone_number     varchar2(20),
    hire_date        date        ,
    job_id           varchar2(10),
    salary           number(8,2) ,
    commission_pct   number(2,2) ,

xtender

SQL> exec for r in (select * from table(xf_to_drop)) loop null; end loop;
Elapsed: 00:00:29.71

SQL> ed
Wrote file afiedt.sql

  1  begin
  2  for r in (
  3     select dt,
  4           dim1,

xtender

SQL> @tests/3
SQL> /*
SQL> drop table test purge;
SQL> create table test as
SQL> select rownum clientid,ceil(rownum/3)  accountid from xmltable('1 to 3000000');
SQL>
SQL> begin
SQL>    for r in (
SQL>       select min(group_member_id) as group_max_id, accountid, clientid

xtender

with t1 as (select level id from dual connect by level<=10)
    ,t2 as (select level id, 'a'||level v from dual connect by level<=10)
select
  id,
  (select rtrim(new_v,',')
   from t2
   where t2.id<=t1.id
   model
      return updated rows
      dimension by (rownum rn)

xtender

xt_scripts

Вспомогательные скрипты для SQL*Plus

Установка

Для удобной и корректной работы скриптов нужно:

xtender

SQL> -- 2 max:
SQL> select/*+ findme 1 */ * from
  2  (
  3      select c.*
  4          , max(c.oper_id) over (partition by c.client_id) as m_o/*max_operation*/
  5      from
  6          (
  7          select t.*
  8          , max(t.amount) over (partition by t.client_id) as m_a/*max_amount*/
  9          from habr_test_table t

xtender

select 
  chairperson
 ,grp          as period
 ,min(date_from) keep (dense_rank first order by date_from,date_to) as date_from
 ,max(date_to  ) keep (dense_rank last  order by date_from,date_to) as date_to
from (
      select
           chairperson 
         , date_from 
         , date_to 

xtender

begin
   
   insert into chairmanships ( chairperson, date_from, date_to)
   values
   ( 'Jim' , to_date('01-01-2000', 'DD-MM-YYYY') ,  to_date('01-05-2000', 'DD-MM-YYYY')
   );
   
   insert into chairmanships ( chairperson, date_from, date_to)
   values
   ( 'Jim' , to_date('02-05-2000', 'DD-MM-YYYY') ,  null

xtender

-- 1. xmltable-string-tokenize:
with v as (
           select 1 id, 'Hello Brendan How are you today?' str from dual
           union all 
           select 2 id, 'It is a hot and sunny day in Ireland.' from dual
           union all
           select 3 id, '$$$It is a hot and sunny day in #Ireland.' from dual
           union all
           select 4 id, '#####' from dual
 

xtender

SQL> create function xt_func(v varchar2) return varchar2 as
  2  begin
  3     dbms_output.put_line('Function fired! ['||v||']');
  4     return v;
  5  end;
  6  /

Function created.

SQL> create view xt_view as