Jan 312014
 

An SQL magic trick – the table disappeared, just by running a select statement! Can you figure out how it’s done?

select * 
from   the_disappearing_table_trick
/

NOW_YOU_SEE_ME
--------------
Now you don't

--....

select * 
from   the_disappearing_table_trick
/

ORA-00942: table or view does not exist

If you think you know, put your answers in the comments!

  11 Responses to “SQL Quiz: How does this table disappear?”

  1. sokrates@11.2 > create package p is
    2 type t is record ( now_you_see_me varchar2 ( 20 ));
    3 type tt is table of t;
    4 counter int := 0;
    5 function f return tt pipelined;
    6 end p;
    7 /

    Package created.

    sokrates@11.2 > create package body p is
    2 function f return tt pipelined is
    3 e exception;
    4 pragma exception_init(e, -00942);
    5 l t;
    6 begin
    7 if p.counter = 0 then
    8 p.counter := p.counter + 1;
    9 l.now_you_see_me := q'|Now you don't|';
    10 pipe row ( l );
    11 return;
    12 else
    13 raise e;
    14 end if;
    15 end f;
    16 end p;
    17 /

    Package body created.

    sokrates@11.2 > create view the_disappearing_table_trick as
    2 select * from table(p.f);

    View created.

    sokrates@11.2 > select * from the_disappearing_table_trick;

    NOW_YOU_SEE_ME
    --------------------
    Now you don't

    sokrates@11.2 > /
    select * from the_disappearing_table_trick
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "SOKRATES.P", line 13

    • You’re too good Matthias! ;) My solution was slightly different (I submitted a job in the pipelined function), but I think yours is better :)

    • Interesting that this gives the ORA-942, even though the view is still actually there…

      • sometimes ( not in this case here ) raising an exception associated with an error code is self-fulfilling.
        see:

        sokrates@11.2 > declare
        2 "your session has been killed" exception;
        3 pragma exception_init( "your session has been killed", -00028);
        4 begin
        5 raise "your session has been killed";
        6 end;
        7 /
        declare
        *
        ERROR at line 1:
        ORA-00028: your session has been killed
        ORA-06512: at line 5

        sokrates@11.2 > select * from dual as "Am I still alive?";
        select * from dual as "Am I still alive?"
        *
        ERROR at line 1:
        ORA-01012: not logged on
        Process ID: 15202
        Session ID: 500 Serial number: 2107

        ERROR:
        ORA-01012: not logged on
        Process ID: 15202
        Session ID: 500 Serial number: 2107

        If raising ORA-00942 above would have the same effect, it would have been dropped the view the_disappearing_table_trick indeed.

  2. that would do the trick:

    alter session set current_schema=ANOTHER_SCHEMA;

    Regards,
    Peter

    • That would hide it Peter – I didn’t run any other code in this case though.

      • Dear Chris,

        you are right!

        Here is another solution using DBMS_RLS (you need EE for that one):


        create table the_disappearing_table_trick (NOW_YOU_SEE_ME varchar2(100));

        insert into the_disappearing_table_trick values (q'(Now you don't)');

        CREATE OR FUNCTION NOW_YOU_SEE_ME (p_schema VARCHAR2, p_table VARCHAR2)
        RETURN VARCHAR2
        IS
        v_job PLS_INTEGER;
        BEGIN
        IF p_table = 'THE_DISAPPEARING_TABLE_TRICK'
        THEN
        DBMS_JOB.submit (
        v_job,
        q'(begin execute immediate('drop table the_disappearing_table_trick'); end;)');
        commit;
        END IF;

        RETURN NULL;
        END;

        BEGIN
        SYS.DBMS_RLS.ADD_POLICY (
        object_schema => 'MYSCHEMA'
        ,object_name => 'THE_DISAPPEARING_TABLE_TRICK'
        ,policy_name => 'NOW_YOU_SEE_ME'
        ,function_schema => 'MYSCHEMA'
        ,policy_function => 'NOW_YOU_SEE_ME'
        ,statement_types => 'SELECT'
        ,policy_type => dbms_rls.dynamic
        ,long_predicate => TRUE
        ,update_check => FALSE
        ,static_policy => FALSE
        ,enable => TRUE );
        END;

        select * from the_disappearing_table_trick;

        NOW_YOU_SEE_ME
        --------------------------------------------------------------------------------
        Now you don't

        -- wait until job starts

        select * from the_disappearing_table_trick
        *
        ERROR at line 1:
        ORA-00942: table or view does not exist

        Regards,
        Peter

        • Great work Peter – I hadn’t thought of that! Interesting that a “security” feature can be used to do some damage in your database…

  3. SQL>create or replace function now_you_see_me
    2 return varchar2
    3 is
    4 pragma autonomous_transaction;
    5 begin
    6 execute immediate ‘drop view the_disappearing_table_trick’;
    7 return ‘Now you don”t’;
    8 end;
    9 /

    Function created.

    SQL>
    SQL>create or replace view the_disappearing_table_trick as
    2 select now_you_see_me from dual
    3 /

    View created.

    SQL>
    SQL>select *
    2 from the_disappearing_table_trick
    3 /

    NOW_YOU_SEE_ME
    ——————————————————————————–
    Now you don’t

    SQL>
    SQL>select *
    2 from the_disappearing_table_trick
    3 /
    from the_disappearing_table_trick
    *
    ERROR at line 2:
    ORA-00942: table or view does not exist

    SQL>

    • Nicely done Anton. The solution I came up with is actually a hybrid of yours and Matthias’ – I used a pipelined function that submitted a “drop view” job ;)

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)