Loop within Recursive Query

create table t

( id        number

, parent_id number

);

insert into t values (1, 2);

insert into t values (2, 1);

commit;

with tr (id,parent_id) as

( select id

       , parent_id

    from t

   where id = 1

   union all

  select t.id

       , t.parent_id

    from t

         join tr on t.parent_id = tr.id

) cycle id set is_cycle to '1' default '0'

select id

     , parent_id

     , is_cycle

  from tr;

Continue Reading

Managing Indexes

Managing Indexes

In general, during the ETL process, before a table is truncated, all the indexes, as defined in the repository, will be dropped before the data is loaded and recreated automatically after the data is loaded. The dropping of indexes before the data load and recreating them after the data load improves ETL performance.

Continue Reading

DBMS_LOB.SUBSTR

DBMS_LOB.SUBSTR

This function returns amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB.

For fixed-width n-byte CLOBs, if the input amount for SUBSTR is specified to be greater than (32767/n), then SUBSTR returns a character buffer of length (32767/n), or the length of the CLOB, whichever is lesser. For CLOBs in a varying-width character set, n is 2.

Syntax:

Example :

SELECT    DBMS_LOB.SUBSTR(ERROR_MESSAGE,4000,1)  COLUMN_A 

FROM MY_TABLE WHERE ERROR_MESSAGE IS NOT NULL

Continue Reading