Example of using XSLT transformation

select xmltransform(xmltype('

  100
  1
  
    
  
'),xmltype(q'{




  
  
    
  





}'))
from dual
  

Work test of DETERMINISTIC function

SQL> set timing off;
SQL> def cnt = 10000000;
SQL> 
SQL> create or replace package detest as
  2    function test1(p in varchar2) return varchar2 DETERMINISTIC;
  3    function test2(p in varchar2) return varchar2 DETERMINISTIC;
  4  end;
  5  /

Package created.

SQL> 
SQL> create or replace package body detest as
  2    vCache varchar2( 2000 );
  3  
  4    function test1(p in varchar2) return varchar2
  5    as
  6    begin
  7      if p = 's' then
  8        return 't';
  9      else
 10        return null;
 11      end if;
 12    end;
 13  
 14    function test2(p in varchar2) return varchar2
 15    as
 16    begin
 17      if p = 's' then
 18        if vCache is null then
 19          select 't' into vCache from ALL_OBJECTS where rownum < 2;
 20        end if;
 21        return vCache;
 22      else
 23        return null;
 24      end if;
 25    end;
 26  
 27  end;
 28  /

Package body created.

SQL> 
SQL> set timing on;
SQL> 
SQL> declare
  2    vDummy varchar2(2000);
  3  begin
  4    for i in 1..&cnt
  5    loop
  6      vDummy := detest.test1('z');
  7      vDummy := detest.test1('s');
  8    end loop;
  9  end;
 10  /
old   4:   for i in 1..&cnt
new   4:   for i in 1..10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
SQL> 
SQL> declare
  2    vDummy varchar2(2000);
  3  begin
  4    for i in 1..&cnt
  5    loop
  6      vDummy := detest.test2('z');
  7      vDummy := detest.test2('s');
  8    end loop;
  9  end;
 10  /
old   4:   for i in 1..&cnt
new   4:   for i in 1..10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
SQL> 
SQL> set timing off;
SQL> 
SQL> create or replace package detest as
  2    function test1(p in varchar2) return varchar2;
  3    function test2(p in varchar2) return varchar2;
  4  end;
  5  /

Package created.

SQL> 
SQL> set timing on;
SQL> 
SQL> declare
  2    vDummy varchar2(2000);
  3  begin
  4    for i in 1..&cnt
  5    loop
  6      vDummy := detest.test1('z');
  7      vDummy := detest.test1('s');
  8    end loop;
  9  end;
 10  /
old   4:   for i in 1..&cnt
new   4:   for i in 1..10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.77
SQL> 
SQL> declare
  2    vDummy varchar2(2000);
  3  begin
  4    for i in 1..&cnt
  5    loop
  6      vDummy := detest.test2('z');
  7      vDummy := detest.test2('s');
  8    end loop;
  9  end;
 10  /
old   4:   for i in 1..&cnt
new   4:   for i in 1..10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.46
SQL> spool off

Out SYS_REFCURSOR into console

create or replace PROCEDURE grachevcur(
                            p_refcursor        IN OUT SYS_REFCURSOR
                         )  
    AS
        l_desc          DBMS_SQL.DESC_TAB ;
        l_cols          BINARY_INTEGER ;
        l_cursor        BINARY_INTEGER ;
        v_varchar2      VARCHAR2( 4000 ) ;
        v_number        NUMBER ;
        v_date          DATE ;
        l_data          varchar2( 32767 ) ;
        l_columnValue   VARCHAR2( 32767 ) ;
        l_processedRows Number := 0;
    BEGIN
 
        /* Convert refcursor "parameter" to DBMS_SQL cursor... */
        l_cursor := DBMS_SQL.TO_CURSOR_NUMBER( p_refcursor );
        /* Describe the cursor... */
        DBMS_SQL.DESCRIBE_COLUMNS( l_cursor, l_cols, l_desc );
 
        /* Define columns to be fetched. We're only using V2, NUM, DATE for example...
        for a complete list of the col_types this link is accessible.
        http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45504
        http://forums.oracle.com/forums/thread.jspa?threadID=912475
        if not a usable type, will throw new exception
        */
         FOR i IN 1 .. l_cols LOOP
             IF l_desc(i).col_type = 2 THEN
               DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_number);
            ELSIF l_desc(i).col_type = 12 THEN
               DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_date);
            ELSif l_desc(i).col_type = 01 or l_desc(i).col_type = 96 then
               DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_varchar2, 4000);
            else
                --raise an exception if the user's query contains a datatype not (yet) supported by this procedure
                RAISE_APPLICATION_ERROR(-20000, 'Invalid Data Type for conversion to delimited file. {' || l_desc(i).col_name || '}');
            END IF;
          END LOOP;
 
 
        /* -- print out the column names if desired
             FOR i IN 1 .. l_cols LOOP
                     dbms_output.put_line('** ' || l_desc(i).col_name) ;
             END LOOP;
        */
 
         /* Fetch all data... */
         WHILE DBMS_SQL.FETCH_ROWS(l_cursor) > 0 LOOP
             dbms_output.put_line('LINE: '  || l_processedRows || '');
             FOR i IN 1 .. l_cols LOOP
                 if l_desc(i).col_type = 12 THEN --we are in a date
                    DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_date);
                    v_varchar2 := to_char(v_date , 'dd-MON-yyyy' ) ;
                 elsif  l_desc(i).col_type = 2 THEN --we are in a number
                    DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_number);
                    v_varchar2 := to_char(v_number) ;
                 else --treat it as a string (should be varchar2,char,etc)
                    DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_varchar2);
                    IF v_varchar2 IS NOT NULL THEN
                       v_varchar2 := '"' || v_varchar2 || '"' ;
                       ELSE
                       v_varchar2 := '';
                    END IF ;
                 end if ;
                 dbms_output.put_line('  ' ||l_desc(i).col_name || '=>' || v_varchar2) ;
             END LOOP;
             l_processedRows := l_processedRows + 1 ;
          END LOOP;
 
          dbms_sql.close_cursor(l_cursor);
          dbms_output.put_line('I found and processed  '  || l_processedRows || ' rows .');
 
    END;
/

Comments: columns & tables

Get column comments:

select * from user_col_comments

How to disable and enable depended constraints

select
--'truncate table '|| TABLE_NAME || ';'
'alter table ' || TABLE_NAME || ' enable constraint ' || CONSTRAINT_NAME || ';'
from ALL_CONSTRAINTS
where CONSTRAINT_TYPE = 'R'
  and STATUS <> 'ENABLED'
  --and R_CONSTRAINT_NAME = 'C_MEDHISTCONS_PK'
order by TABLE_NAME

select 'alter table ' || TABLE_NAME || ' disable constraint ' || CONSTRAINT_NAME || ';'
from ALL_CONSTRAINTS
where CONSTRAINT_TYPE = 'R'
  and R_CONSTRAINT_NAME = 'C_DCSPECS_PK'  

How to create indexes by foreign key

select 'create index I_' || substr(C.CONSTRAINT_NAME, 3) || ' on ' || C.TABLE_NAME || '(' || 
  (select rtrim(xmlagg(xmlelement(e, CC.COLUMN_NAME,',').extract('//text()') order by CC.COLUMN_NAME).GetClobVal(),',')
   from ALL_CONS_COLUMNS CC
   where CC.OWNER = C.OWNER
     and CC.TABLE_NAME = C.TABLE_NAME
     and CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME) || ');'
from (
  select distinct C.OWNER, C.TABLE_NAME, C.CONSTRAINT_NAME
  from
    ALL_CONSTRAINTS C,
    ALL_CONS_COLUMNS CC
  where C.CONSTRAINT_TYPE = 'R'
    and C.OWNER = 'PARUS'
    and CC.OWNER = C.OWNER
    and CC.TABLE_NAME = C.TABLE_NAME
    and CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
    and not CC.COLUMN_NAME in ('COMPANY', 'VERSION')
    and CC.CONSTRAINT_NAME like 'C\_%' escape '\'
    and not exists (
      select *
      from ALL_IND_COLUMNS IC
      where IC.TABLE_OWNER = C.OWNER
        and IC.TABLE_NAME  = CC.TABLE_NAME
        and IC.COLUMN_NAME = CC.COLUMN_NAME)) C

How to reverse string

select listagg(C) within group(order by RN desc) rvs
from (
  select substr(T.COL, rownum, 1) C, rownum RN
  from (select 'Привет' COL from DUAL) T
  connect by level <= length(T.COL))

How to find out list of session which are blocking a temporary table

select S.username, S.osuser, S.machine,  L.*, O.object_name
from SYS.V_$LOCK L, SYS.V_$SESSION S, SYS.ALL_OBJECTS O
where L.TYPE = 'TO'
  and L.SID = S.SID
  and L.ID1 = O.object_id
  and O.object_name = 'MICACTIVISBF'

Parse string to a query

with test as (select 'ABC,DEF,GHI,JKL,MNO' str from dual)
select regexp_substr (str, '[^,]+', 1, rownum) split
from test
connect by level <= length (regexp_replace (str, '[^,]+'))  + 1

Thereapy for Zelenograd

-- Clearing UPDATELIST table
--1 930 637
begin
  for rec in (
    select T.ROWID TR, row_number() over (order by T.RN) RRN
    from UPDATELIST T
    where to_number(to_char(T.MODIFDATE, 'yyyy')) < 2015
    order by RN)
  loop
    delete UPDATELIST where ROWID = rec.TR;
    if mod(rec.RRN, 100) = 0 then
      commit;
    end if;
  end loop;
  commit;
end;
/

create tablespace PARUS_IND2 datafile 
  'D:\BASES\PZAO1\PARUS_IND2.DAT'
  size 10M autoextend on next 1M 
  maxsize unlimited logging online;


select 'alter index ' || SEGMENT_NAME || ' rebuild tablespace PARUS_IND2 nologging;'
from DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'PARUS_IND'
  and SEGMENT_TYPE    = 'INDEX'
  and OWNER           = 'PARUS'

drop tablespace PARUS_IND including contents and datafiles;

How to calculate size of tablespaces

select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, BLOCKS
from 
(
  select FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS
  from DBA_DATA_FILES
  union all
  select FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS
  from DBA_TEMP_FILES
  order by TABLESPACE_NAME, FILE_NAME
);

Recreation indexes' tablespace

create tablespace PARUS_IND2 datafile 
  'D:\BASES\PZAO1\PARUS_IND2.DAT'
  size 10M autoextend on next 1M 
  maxsize unlimited logging online;


select 'alter index ' || SEGMENT_NAME || ' rebuild tablespace PARUS_IND2 nologging;'
from DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'PARUS_IND'
  and SEGMENT_TYPE    = 'INDEX'
  and OWNER           = 'PARUS'

drop tablespace PARUS_IND including contents and datafiles;