Uncategorized

powermultiset and listagg () withing group ()

Posted on február 9, 2011. Filed under: Uncategorized | Značky:, , |

I need to create simple “dimension table” for reporting application I currently work on. Problem is that dimension is a “bit ugly”. Application is storing m:n relation Animal:BreedingType in Animal’s column as delimited string.

So I need to dynamically populate this dimension from enum definition with all possible delimited strings of codes and descriptions in my languages.

Sample table – BreedingType enum.


create table DE_BreedingType
(
  Code varchar2 (30 byte)  not null ,
  Description_TR varchar2 (255 byte) ,
  Description_EN varchar2 (255 byte)
);

alter table DE_BreedingType
  add constraint DE_BreedingType_PK
  primary key ( Code ) ;

insert into de_breedingtype values ('I', 'Süt', 'Milk');
insert into de_breedingtype values ('M', 'Et', 'Meat');
insert into de_breedingtype values ('W', 'Yün', 'Wool');
insert into de_breedingtype values ('B', 'Yetiştirme', 'Breeding');

commit;

I could do it with some PL/SQL but SQL is much elegant and simple. I will use PowerMultiSet function, which returns all submultisets for given multiset – in my case all collections of sub-collections of enums.

I have to create SQL Object Types first.


--enum object
create or replace type taro_enum as object
(
  Code           varchar2(30),
  Description_TR varchar2(255),
  Description_EN varchar2(255)
);
--table of objects
create type tart_enum as table of taro_enum;
--table of tables of objects
create type tartt_enum as table of tart_enum;

Now I can select all sub-multisets.


--1. collect objects and cast colection as table of objects
--2. compute powermultiset
--3. cast resulting collection as table of tables of objects
select cast(powermultiset(cast(collect(taro_enum(Code           => Code,
                                                 Description_TR => Description_TR,
                                                 Description_EN => Description_EN)) as tart_enum)) as
            tartt_Enum)
  from de_breedingtype;

Now I’m almost there. I “just” need to concatenate rows in in each submultiset. It’s simple and elegant in 11g using listagg () within group ().

In Oracle 11g, we have the within group SQL clause to pivot multiple rows onto a single row. We also a have direct SQL mechanism for non first-normal form SQL display. This allows multiple table column values to be displayed in a single column, using the listagg built-in function

Thanks to Don


select (select listagg(Code, ',') within group( order by Code) 
          from table(column_value)) as Code
      ,(select listagg(Description_EN, ',') within group( order by Code)
          from table(column_value)) as Description_EN
      ,(select listagg(Description_TR, ',') within group( order by Code) Code
          from table(column_value)) as Description_TR
  from table (select cast(powermultiset(
                       cast(collect(taro_enum(Code           => Code,
                                              Description_TR => Description_TR,
                                              Description_EN => Description_EN))
                            as tart_enum))
                          as tartt_Enum)
                from de_breedingtype);

CODE       DESCRIPTION_EN             DESCRIPTION_TR
---------- -------------------------- --------------------------
I          Milk                       Süt
M          Meat                       Et
I,M        Milk,Meat                  Süt,Et
W          Wool                       Yün
I,W        Milk,Wool                  Süt,Yün
M,W        Meat,Wool                  Et,Yün
I,M,W      Milk,Meat,Wool             Süt,Et,Yün
B          Breeding                   Yetistirme
B,I        Breeding,Milk              Yetistirme,Süt
B,M        Breeding,Meat              Yetistirme,Et
B,I,M      Breeding,Milk,Meat         Yetistirme,Süt,Et
B,W        Breeding,Wool              Yetistirme,Yün
B,I,W      Breeding,Milk,Wool         Yetistirme,Süt,Yün
B,M,W      Breeding,Meat,Wool         Yetistirme,Et,Yün
B,I,M,W    Breeding,Milk,Meat,Wool    Yetistirme,Süt,Et,Yün

And that’s about it.

Read Full Post | Make a Comment ( None so far )

Oracle SQL Developer Data Modeler 3.0 production – migrating Domains from EA (Mac OS X)

Posted on február 1, 2011. Filed under: Uncategorized | Značky:, |

Migration to 3.0 production was was not as easy as I thought it would be. My Domains carefully set for all columns got screwed and all VARCHAR2 column sizes were lost.

After some investigation I found out, that EA saves all Domains to default domains file which is buried in

OracleDataModeler.app/Contents/Resources/datamodeler/datamodeler/types/defaultdomains.xml

and not in your home, so it doesn’t get copied to new version settings when running 3.0 production for the 1st time.

So before you open your model in 3.0 production you have to import this defaultdomains.xml by File > Import > Domains. Don’t forget to check Import in Default Domains check-box.

Happy modeling 😉

Read Full Post | Make a Comment ( None so far )

Back on Track

Posted on január 28, 2011. Filed under: Uncategorized |

Too much work – too little motivation. But now I’m back on track. I hope … 😉

Read Full Post | Make a Comment ( None so far )

No sum for interval day to second?

Posted on október 30, 2009. Filed under: Uncategorized | Značky:, , , , |

I just wonder why ORACLE didn’t implement sum of interval day to second datatype.

CREATE OR REPLACE TYPE sum_interval AS OBJECT
(

/* type implements oracles data cartridge interface to compute sum of day to second interval values */

   duration INTERVAL DAY TO SECOND, --duration accumulator

   CONSTRUCTOR FUNCTION sum_interval RETURN SELF AS RESULT
      PARALLEL_ENABLE,

   STATIC FUNCTION odciaggregateinitialize(ctx IN OUT sum_interval) RETURN NUMBER,

   MEMBER FUNCTION odciaggregateiterate
   (
      SELF  IN OUT sum_interval,
      VALUE IN INTERVAL DAY TO SECOND
   ) RETURN NUMBER,

   MEMBER FUNCTION odciaggregatedelete
   (
      SELF  IN OUT sum_interval,
      VALUE IN INTERVAL DAY TO SECOND
   ) RETURN NUMBER,

   MEMBER FUNCTION odciaggregateterminate
   (
      SELF        IN sum_interval,
      returnvalue OUT INTERVAL DAY TO SECOND,
      flags       IN NUMBER
   ) RETURN NUMBER,

   MEMBER FUNCTION odciaggregatemerge
   (
      SELF IN OUT sum_interval,
      ctx  IN sum_interval
   ) RETURN NUMBER
)
;
/

CREATE OR REPLACE TYPE BODY sum_interval IS

   ------------------------------------------------------------------------------------------------- 
   CONSTRUCTOR FUNCTION sum_interval RETURN SELF AS RESULT
      PARALLEL_ENABLE IS
   BEGIN
      RETURN;
   END sum_interval;
   
   ------------------------------------------------------------------------------------------------- 
   STATIC FUNCTION odciaggregateinitialize(ctx IN OUT sum_interval) RETURN NUMBER IS
   BEGIN
      ctx := sum_interval();
      RETURN odciconst.success;
   END odciaggregateinitialize;
   
   ------------------------------------------------------------------------------------------------- 
   MEMBER FUNCTION odciaggregateiterate
   (
      SELF  IN OUT sum_interval,
      VALUE IN INTERVAL DAY TO SECOND
   ) RETURN NUMBER IS
   BEGIN
      IF SELF.duration IS NULL THEN
         SELF.duration := VALUE;
      ELSE
         SELF.duration := SELF.duration + VALUE;
      END IF;
      RETURN odciconst.success;
   END odciaggregateiterate;
   
   ------------------------------------------------------------------------------------------------- 
   MEMBER FUNCTION odciaggregateterminate
   (
      SELF        IN sum_interval,
      returnvalue OUT INTERVAL DAY TO SECOND,
      flags       IN NUMBER
   ) RETURN NUMBER IS
   BEGIN
      returnvalue := SELF.duration;
      RETURN odciconst.success;
   END odciaggregateterminate;
   
   ------------------------------------------------------------------------------------------------- 
   MEMBER FUNCTION odciaggregatedelete
   (
      SELF  IN OUT sum_interval,
      VALUE IN INTERVAL DAY TO SECOND
   ) RETURN NUMBER IS
   BEGIN
      SELF.duration := SELF.duration - VALUE;
      RETURN odciconst.success;
   END odciaggregatedelete;
   
   ------------------------------------------------------------------------------------------------- 
   MEMBER FUNCTION odciaggregatemerge
   (
      SELF IN OUT sum_interval,
      ctx  IN sum_interval
   ) RETURN NUMBER IS
   BEGIN
      IF ctx.duration IS NULL THEN
         NULL;
      ELSE
         SELF.duration := SELF.duration + ctx.duration;
      END IF;
      RETURN odciconst.success;
   END odciaggregatemerge;
   
END;
/

create or replace function fce_sum_interval(duration interval day to second) return interval day to second
  parallel_enable
  aggregate using sum_interval;
/

select fce_sum_interval(numtodsinterval(level, 'minute')) from dual connect by level <= 10;

Read Full Post | Make a Comment ( None so far )

Is your physical model “XML ready”?

Posted on október 26, 2009. Filed under: Uncategorized | Značky:, |

drop table t;
drop view v;
create table t (t timestamp);
insert into t values (current_timestamp);
commit;
create view v as select xmlforest(t) x from t;

alter session set nls_language='AMERICAN';
alter session set nls_territory='AMERICA';
select * from v;
X
-------------------------------------------
2009-10-26T14:51:58.916436 


alter session set nls_language='CZECH';
alter session set nls_territory='CZECH REPUBLIC';
select * from v;

Error report:
SQL Error: ORA-01877: pro vnitřní vyrovnávací paměť je řetězec příliš dlouhý
01877. 00000 -  "string is too long for internal buffer"
*Cause:    This is an internal error.
*Action:   Please contact Oracle Worldwide Support.


truncate table t;
alter table t modify(t timestamp with local time zone);
insert into t values (current_timestamp);
commit;

alter session set nls_language='CZECH';
alter session set nls_territory='CZECH REPUBLIC';
select * from v;
X
-------------------------------------------
2009-10-26T14:51:59.086511+01:00


alter session set nls_language='AMERICAN';
alter session set nls_territory='AMERICA';
select * from v;

X
-------------------------------------------
2009-10-26T14:51:59.086511+01:00

Read Full Post | Make a Comment ( None so far )

PL/SQL try-catch-finally concept

Posted on október 9, 2009. Filed under: Uncategorized | Značky:, |

Taky maly pokus o koncept implementacie try-catch-finally v PL/SQL.

  • 1. priblizenie
  • + zachovava “strukturu”
  • – rozbija error_stack a chybovu hlasku

<<try_catch_finally_block>>
declare
  --some handled exception
  e exception;
  pragma exception_init(e, -20666);
  
  --some unhandled user defined exception
  e_some_user_defined exception; 
  pragma exception_init(e_some_user_defined, -20000);
  
  --some ora exception
  e_some_ora exception; 
  pragma exception_init(e_some_ora, -1);
  
  l_sqlcode pls_integer;
  l_sqlerrm varchar2(500);
begin

  <<try_block>>
  begin
    --try
    dbms_output.put_line('try');
    
    --dbms_output.put_line('handled exception raised');
    --raise e;    
    
    --dbms_output.put_line('some unhandled user defined exception raised');
    --raise e_some_user_defined;

    --dbms_output.put_line('some unhandled ora exception raised');    
    --raise e_some_ora;
    
  exception   
    when e then  
       --catch
      dbms_output.put_line('catch');
  
    when others then  
      --store unhandled exception
      l_sqlcode := sqlcode;
      l_sqlerrm := sqlerrm;      
      dbms_output.put_line('store unhandled exception');
      
  end try_block;  
  
  --finally
  dbms_output.put_line('finally');  
  
  --reraise unhandled  exception
  if (l_sqlcode is not null) then
    dbms_output.put_line('reraise unhandled exception');
    
    --if user defined exception
    if l_sqlcode between -20999 and -20000 then          
      --reraise user defined error
      raise_application_error(l_sqlcode, l_sqlerrm);
    else
      --reraise ora error
      <<reraise_ora_error>>
      declare        
      begin
        execute immediate '
          declare
            e exception;
            pragma exception_init(e, '||l_sqlcode||');
          begin
            raise e;
          end;
        ';
      end reraise_ora_error;      
    end if;
    
  end if;
  
end try_catch_finally_block;
/

  • 2. priblizenie
  • – nezachovava “strukturu”
  • + zachovava error_stack a chybovu hlasku
  • + cistejsi & prehladnejsi kod

<<try_catch_finally_block>>
declare
  --some handled exception
  e exception;
  pragma exception_init(e, -20666);
  
  --some unhandled user defined exception
  e_some_user_defined exception; 
  pragma exception_init(e_some_user_defined, -20000);
  
  --some ora exception
  e_some_ora exception; 
  pragma exception_init(e_some_ora, -1);
  
  --local procedure where finalization is implemented
  procedure finally is
  begin
    --finally
    dbms_output.put_line('finally');    
  end finally;
  
begin

  <<try_block>>
  begin
    --try
    dbms_output.put_line('try');
    
    --dbms_output.put_line('handled exception raised');
    --raise e;    
    
    --dbms_output.put_line('some unhandled user defined exception raised');
    --raise e_some_user_defined;

    --dbms_output.put_line('some unhandled ora exception raised');    
    --raise e_some_ora;
    
  exception   
    when e then  
       --catch
      dbms_output.put_line('catch');
  
    when others then  
      --finally
      finally;
      
      --raise unhandled
      raise;
      
  end try_block;  
  
  --finally
  finally;
  
end try_catch_finally_block;
/

Read Full Post | Make a Comment ( 1 so far )

Oracle SQL Developer Release 2.1 – Early Adopter 1

Posted on október 5, 2009. Filed under: Uncategorized | Značky: |

Tak som stiahol a nainstaloval a bol velmi priijemne prekvapeny.

  • rychlost
  • drag’n’drop objektov do sql editoru
  • spustanie viacerych prikazov
  • usporiadanie membrov

A co ma zaujalo a chystam sa otestovat

  • Unit testing!!!!
  • integracia so SVN – snad konecne funguje 😉

+ dalsie veci, ktore objavim neskor.

Read Full Post | Make a Comment ( None so far )

zrada materialized view log-ov

Posted on jún 16, 2009. Filed under: Uncategorized | Značky:, , |

do mview logov sa nepropaguju direct path dml operacie na tabulkach. a fast refresh je potom na 2 veci … to bol teda daarecek. diiky 🙂

Read Full Post | Make a Comment ( None so far )

user_views_definition

Posted on jún 4, 2009. Filed under: Uncategorized | Značky:, , |

Bud mi nieco uniklo, alebo ma Oracle este aj v 11g ulozene definicie view iba v long stlpcoch, ktore su uprimne na …, neda sa v nich hladat, rozumne pristupovat jedine z pl/sql, achjo. Tak si pre potesenie z moci sql vyrabam tabulku do ktorej si mergujem definicie pre take to domace dotazovanie … 😉

drop table user_views_definition;

create table user_views_definition (
  view_name       varchar2(30) not null,
  view_definition clob,
  constraint  user_views_definition_pk
  primary key (view_name)
  using index
)
;

DECLARE
   c CLOB;
BEGIN
   FOR ii IN (SELECT * FROM user_views)
   LOOP
      c := ii.text;
      
      MERGE INTO user_views_definition t
      USING (SELECT ii.view_name AS view_name, c AS view_definition FROM dual) s
      ON (t.view_name = s.view_name)
      WHEN MATCHED THEN UPDATE SET t.view_definition = s.view_definition 
      WHEN NOT MATCHED THEN INSERT (t.view_name, t.view_definition) VALUES (s.view_name, s.view_definition);
      
   END LOOP;
   COMMIT;
END;
/

Read Full Post | Make a Comment ( None so far )

a je to tu!

Posted on máj 16, 2009. Filed under: Uncategorized |

konecne som sa dokopal k tomu, aby som na pracovnom notebooku vyhodil windows a dal tam konecne ubuntu. tento blog ale nebude o linuxe. budem sa snazit pisat o tom co robim alebo o tom co ma zaujima. a ziadne varenie! to je dufam predsavzatie ktore sa mi podari splnit 🙂

Read Full Post | Make a Comment ( None so far )

  • Co prave robim

    • 【鋼の錬金術師 FAコラボ開催!】エルリック兄弟、ロイ・マスタング、リン・ヤオ、リザ・ホークアイがモンストにやってきた!スカー、エンヴィー、グリードがクエストに出現!“錬金術師”達と共に闘おう! #ハガレンモンスト #モンスト bit.ly/2sNPPBR 1 week ago
    • 映画『パイレーツ・オブ・カリビアン/最後の海賊』とのコラボ開催中!金と銀の“ジャック・スパロウ“をゲットで「パイレーツ缶」が当たるキャンペーンも!ジャック・スパロウと一緒にモンストで冒険しよう! #モンスト #パイレーツモンスト xfl.ag/2re67pF 3 weeks ago
    • 【リアル版 超・獣神祭『#十二支再競争』】本物のネズミやトラが走る「十二支伝説」のやり直し競争!?1着的中で投票したオーブの10倍のオーブGET!更に1&2着を的中で総額3億円分を山分け!どの動物に投票する? #モンスト xfl.ag/2r8817u 4 weeks ago
    • 「映画ドラえもん」コラボキャラを“運極”したいな!ミッションでキャラが入手しやすいからできるかも!レアなプレゼントが貰える「運極カチコチ大作戦キャペーン」もあるから、みんなも挑戦してみてね! #モンスト #モンストドラえもん xfl.ag/2lG0kab 4 months ago
    • 年末年始もモンスト!大晦日に「年末BIGボーナスくじ」でオーブ5個届く&総額1億円が当たる!大晦日と元旦はログインでオーブ合計20個!元旦より超・獣神祭の限定新キャラ「パンドラ」登場! #モンストやるなよ #モンスト xfl.ag/2fKD1ot 6 months ago
    • 「幽☆遊☆白書」コラボ開催!モンストに浦飯幽助、蔵馬、飛影、桑原和真、幻海が登場!登場キャラボイスは全て録りおろし!”伊達にあの世は見てねぇぜ!”幽助達と一緒に「暗黒武術会」に挑め! #モンスト #幽白モンスト xfl.ag/2fcrlwu 8 months ago
    • 11/18のニコ生放送内で「モンスト武術会」で優勝するのは「B4」と予想!予想的中でオーブ1個が貰えるよ!モンスト内のミッションから11/18 19時迄、投票可能! #モンスト #幽白モンスト monster-strike.com/news/20161111_… 8 months ago
    • 【モンスト3周年感謝キャンペーン!】10月はモンストがとにかくヤバイ!ハズレなしの「モンストハッピーくじ」で旅行券やオーブ1年分が当たるチャンス!★6キャラ確定&属性が選べる無料ガチャ開催!他! #モンストやるなよ monster-strike.com/promotion/3rd_… 9 months ago
  • Meta

  • Kategórie

Liked it here?
Why not try sites on the blogroll...