Re: A transactionally consistent view on real-time data with MicroStrategy

<erik.ykema_at_gmail.com> wrote in message news:1149534024.767457.122780_at_f6g2000cwb.googlegroups.com...
>> <erik.ykema_at_gmail.com> wrote in message
>> news:1149284215.556809.269580_at_i40g2000cwc.googlegroups.com...
> Jonathan's suggestion using a context indeed solves my challenge! Many
> thanks.
>
> I was not aware of the field of usefullness of contexts untill know.
> I tried using a global temporary table for storing a session specific
> SCN and passing that to the AS OF construct, however I got an
> "ORA-22818 - subquery expression not allowed here".
>
> I also managed, now being pointed into the right direction using a
> package
> and referencing a session specific package global variable in the "AS
> OF" views:
>
> CREATE OR REPLACE package p_scn as
> g_scn number;
> function get_scn return number;
> procedure set_scn;
> end p_scn;
>
> CREATE OR REPLACE package body p_scn as
> function get_scn return number is
> begin
> if g_scn is null then
> set_scn;
> end if;
> return g_scn;
> end get_scn;
> procedure set_scn is
> begin
> g_scn := sys.dbms_flashback.get_system_change_number;
> end set_scn;
> end p_scn;
>
> create view v_a as select * from a as of scn (p_scn.get_scn);
>
> Thanks for reading my extensive example, I hope other's can reuse this
> in cases of near-real time periodically refreshed environments, I will
> also post it to the MSTR user forums.
> Best regards,
> Erik Ykema
>

I'm not sure that you want to use the function/variable like this. It means that you have to end each Microstrategy session after each report, otherwise every report for a session will run from the same SCN - i.e. the first one you set.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html