• olaf (unregistered)

    It is ITIL, that is how it is supposed to be! Look in the docs ;)

  • Weedo (unregistered)

    It's clearly a performance optimisation. Using a RDBMS just adds layers of bloat. hard coding the values is better.

  • Roland (unregistered)

    And do everytime a distinct... and distinct.... and distinct...

  • L.P.O. (unregistered)

    "You can, for example, do SELECT 2 + 2, you must do SELECT 2 + 2 FROM dual."

    I don't understand this sentence. To me it's not CAPTCHA validus.

  • David Aldridge (unregistered)

    My guess at the reason for this would be that the production system is so locked down that the bureaucracy to allow a new table to be created was more trouble than hacking this together -- a view, though, that's just a query!

    I've created similar constructs in standalone queries in situations where flexible lookups were required for which the rate of change of the data exceeded the rate at which change management processes could respond.

  • Stephen (unregistered)

    “dual” is a fictional table

    No; "dual" is a real table and, prior to Oracle 10g, caused real logical IO to happen. "select * from dual" if you're really bored.

  • (cs) in reply to olaf
    olaf:
    It is ITIL, that is how it is supposed to be! Look in the docs ;)

    Yeah. Agreed. I'm ITIL V3 certified; throwing ITIL at incompetence just leads to more complex but well-documented - in theory, at least - incompetence.

  • (cs)

    So, this is technically something that can only be accomplished in Oracle?

    TRWTF is calling your company as your main product. OracleDB would have been much better.

  • (cs) in reply to ubersoldat
    ubersoldat:
    TRWTF is calling your company as your main product. OracleDB would have been much better.
    My sarcasm meter just overloaded. Is that a clever mocking of hungarian notation or just a joke?
  • sean (unregistered)

    i came across an oracle db once that had an additional record inserted into dual. that was a treat to debug.

  • Mike (unregistered)

    The real wtf here is that nobody posted SELECT "frist" FROM dual!

  • dr memals (unregistered)

    some genius gave him access to create views but not tables, only thing that makes sense.

  • Rodnas (unregistered)

    I think this is done for performance reasons. I mean less tables, means less data and speeds up queries and such

  • orakloide (unregistered)

    Security-wise, so you can't DELETE FROM it. Also just GRANT CREATE VIEW is enough, not RESOURCES.

  • Andrew (unregistered)

    I actually did this all the time when I had to create insert scripts.

    insert into TABLE ( COLUMN1, ... ) select COLUMN1, ... from ( select null COLUMN1, ... from DUAL where 1=0 union select value1, ... from dual union select value2, ... from dual union etc. )

    Never seen it in prod code though.

  • Xerods (unregistered) in reply to ubersoldat

    According to Wikipedia: 1982: RSI (Relational Software Inc.) renames itself Oracle Systems Corporation in order to align itself more closely with its primary product.

  • (cs) in reply to Stephen
    Stephen:
    “dual” is a fictional table

    No; "dual" is a real table and, prior to Oracle 10g, caused real logical IO to happen. "select * from dual" if you're really bored.

    Looks like it was even modifiable in earlier versions. Since a "SELECT ... FROM DUAL" query returns one row for each value in DUAL, adding or removing values could create pretty interesting side effects...

  • Captain Oblivious (unregistered) in reply to Stephen
    Stephen:
    “dual” is a fictional table

    No; "dual" is a real table and, prior to Oracle 10g, caused real logical IO to happen. "select * from dual" if you're really bored.

    Indeed, the table dual is dual to the empty table. In other words, it is the top of the Dedekind-Maclane completion of the lattice of relations defined by the schema.

  • (cs) in reply to Unisol
    Unisol:
    ubersoldat:
    TRWTF is calling your company as your main product. OracleDB would have been much better.
    My sarcasm meter just overloaded. Is that a clever mocking of hungarian notation or just a joke?

    hungarian postfix?

    int SomeCountStoredOffN;

  • (cs) in reply to sean
    sean:
    i came across an oracle db once that had an additional record inserted into dual. that was a treat to debug.
    int GetFour()
{
    int returnValue = Sum(SELECT 2+2 FROM Dual);
    ASSERT(returnValue == 4);
    return returnValue;
}

    error: ASSERT failed. Value 69834345 not = to 4.

    int GetFourTryTwo()
{
    int returnValue;
    if ((returnValue = Sum(SELECT 2+2 FROM Dual)) != (2+2))
    {
      return 4;
    }
    return returnValue;
}

ASSERT(GetFourTryTwo() == 4);

    no errors:

  • Thomas (unregistered) in reply to ubersoldat
    ubersoldat:
    So, this is technically something that can only be accomplished in Oracle?

    It's even more simple in MySQL because there you can do a SELECT without a FROM.

  • Anonymous Paranoiac (unregistered) in reply to Thomas
    Thomas:
    ubersoldat:
    So, this is technically something that can only be accomplished in Oracle?

    It's even more simple in MySQL because there you can do a SELECT without a FROM.

    MSSQL, too

  • Dim (unregistered)

    I'm pretty sure the primary goal was to circumvent user database access right. User can't create table he will create temporary. User can't create temporary he will try view... and so on

    Never underestimate creativity of users who have time.

  • Bob (unregistered)

    It could be a way of seeing changes in service lookups in the commits and diffs. It means the magic numbers / strings your app needs to reference are at least somewhere in the codebase, and refreshing some views is (only slightly) more stable than resetting all the lookup tables to their creation & initial population scripts. I see two WTFs here: 1/ No good way (in any DB platform) to keep lookup / config tables continuously synched with version controlled code 2/ dual

  • (cs) in reply to Mike
    Mike:
    The real wtf here is that nobody posted SELECT "frist" FROM dual!

    No, that is simply a rare island of sanity in an ocean of cunts.

  • OneDayWhen (unregistered)

    "The Oracle database doesn't allow..."

    Which Oracle database?

    Try, "The Oracle DBMS doesn't allow..."

  • ortigao (unregistered)

    The real WTF is that this query is using union.

    Enterprisey code requires this to be achieved by ref cursor and pipelined functions.

    Captcha: haero - I came here to save this code from unenterprisiness

  • (cs)

    The most sensible reason for this code to exist is purely for the Remy joke in the comments. The tough part was getting Back to the Future written, financed, produced, and distributed to set up the cultural reference.

  • anon (unregistered)

    Am I the only one who noticed the Event Horizon reference?

  • (cs) in reply to Stephen
    Stephen:
    “dual” is a fictional table

    No; "dual" is a real table and, prior to Oracle 10g, caused real logical IO to happen. "select * from dual" if you're really bored.

    Agreed. Dual is real table owned by system.

  • Buddy (unregistered)

    One thing you learn after many years in the business: don't ask why.

  • ih8u (unregistered) in reply to eViLegion
    eViLegion:
    Mike:
    The real wtf here is that nobody posted SELECT "frist" FROM dual!

    No, that is simply a rare island of sanity in an ocean of cunts.

    Now we just need Mike ... or really anyone to complain when there aren't 40 trolls for every legitimate post. (as if that would EVER happen).

    eViL ... you have become my haero (captcha) with that post

  • Allen (unregistered) in reply to L.P.O.

    The text is wrong. They meant to say "You canNOT, for example, do SELECT 2 + 2, you must do SELECT 2 + 2 FROM dual."

  • bobby (unregistered)

    Fixed?

    create or replace view itil_service_v1 as
select *
from (
    values
        ('SS1', 'Low', 'Low', 48, 96, 4),
        ('SS1', 'Low', 'Medium', 48, 96, 4),
        ('SS1', 'Medium', 'Low', 48, 96, 4),
        ('SS1', 'High', 'Low', 1.5, 24, 1),
        ('SS1', 'Medium', 'Medium', 3, 36, 2),
        ('SS1', 'Low', 'High', 48, 96, 4),
        /* SNIP a few dozen lines for brevity */
        ('HS2', 'Low', 'High', 48, 96, 4),
        ('HS2', 'Medium', 'High', 1.5, 24, 1),
        ('HS2', 'High', 'Medium', 0.30, 16, 0.30),
        ('HS2', 'High', 'High', 0.15, 8, 0.15),
        --Asset Managemetn; (AM3)
        ('AM3', 'Low', 'Low', 48, 96, 4),
        ('AM3', 'Low', 'Medium', 48, 96, 4),
        /* SNIP 800 more lines of the same */
        ('ACC45', 'Medium', 'High', 1.5, 24, 1),
        ('ACC45', 'High', 'Medium',  0.30, 16, 0.30),
        ('ACC45', 'High', 'High', 0.15, 8, 0.15)
    ) as itil_service (servicio, impacto, urgencia, response, resolution, escalates)
  • Strunk (unregistered) in reply to Nagesh

    Nope. DUAL is owned by SYS.

  • Strunk (unregistered) in reply to Roland
    Roland:
    And do everytime a distinct... and distinct.... and distinct...
    Can't see DISTINCT here. UNION is distinct. UNION ALL is not.
  • Cornad (unregistered) in reply to bobby

    ORA-00903: invalid table name

  • (cs) in reply to ih8u
    ih8u:
    eViLegion:
    Mike:
    The real wtf here is that nobody posted SELECT "frist" FROM dual!

    No, that is simply a rare island of sanity in an ocean of cunts.

    Now we just need Mike ... or really anyone to complain when there aren't 40 trolls for every legitimate post. (as if that would EVER happen).

    eViL ... you have become my haero (captcha) with that post

    You are the wiiiiiind beneath my wiiiiiings!

  • (cs) in reply to bobby
    bobby:
    Fixed? 
    create or replace view itil_service_v1 as
select *
from (
    values
        ('SS1', 'Low', 'Low', 48, 96, 4),
        ('SS1', 'Low', 'Medium', 48, 96, 4),
        ('SS1', 'Medium', 'Low', 48, 96, 4),
        /* SNIP */
        ('ACC45', 'Medium', 'High', 1.5, 24, 1),
        ('ACC45', 'High', 'Medium',  0.30, 16, 0.30),
        ('ACC45', 'High', 'High', 0.15, 8, 0.15)
    ) as itil_service (servicio, impacto, urgencia, response, resolution, escalates)

    There are many times that I wish that Oracle would allow this syntax! Unfortunately it doesn't.

  • (cs)

    "it just needs to work"

  • (cs)

    I've used the SELECT / UNION ALL motif a number of times in DB2, because it's the quickest/easiest way to do a load into a small table. I've even used it in ad hoc queries where I needed a handful of static rows to join against something else. I think both can be justified by their ad hoc nature.

    It just never occurred to me to build a view based on such a SELECT, so there is no need for a real table at all.

    Possibly because, while I'm a little weird, I don't think I'm actually insane...

  • jay (unregistered) in reply to Andrew
    Andrew:
    I actually did this all the time when I had to create insert scripts.

    insert into TABLE ( COLUMN1, ... ) select COLUMN1, ... from ( select null COLUMN1, ... from DUAL where 1=0 union select value1, ... from dual union select value2, ... from dual union etc. )

    Never seen it in prod code though.

    Umm, why? Why not just

    insert into table1(column1,...)
values (value1,...),
(value2,...),
(value3,...);
  • Andrew (unregistered) in reply to jay

    If you're generating the data from, say, a spreadsheet, it's easy to script it into this form. Then you can run the select to see what will be inserted. When that checks out, you insert it all at once instead of executing N insert statements...

    Also, mine doesn't generate an ORA-00933: SQL command not properly ended.

  • sagaciter (unregistered) in reply to Andrew
    Andrew:
    When that checks out, you insert it all at once instead of executing N insert statements...
    He didn't say N insert statements, he said one insert statement specifying N rows.
    Also, mine doesn't generate an ORA-00933: SQL command not properly ended.
    Oracle sucks.
  • (cs) in reply to sagaciter
    sagaciter:
    Also, mine doesn't generate an ORA-00933: SQL command not properly ended.
    Oracle sucks.

    No, that's ORA-22275.

  • The Big Picture Thinker (unregistered) in reply to Unisol
    Unisol:
    ubersoldat:
    TRWTF is calling your company as your main product. OracleDB would have been much better.
    My sarcasm meter just overloaded. Is that a clever mocking of hungarian notation or just a joke?
    That's wouldn't be Hungarian notation, unless the prefix "Oracle" was both lowercase and a datatype. While Hungarian notation is not good practice in strongly-typed languages, it is often a necessary scheme to keep track of variables in duck-typed languages, where mistakes and forgetfulness can accidentally cause variable types to change.
  • (cs) in reply to Stephen
    Stephen:
    “dual” is a fictional table

    No; "dual" is a real table and, prior to Oracle 10g, caused real logical IO to happen. "select * from dual" if you're really bored.

    It just contains a single row and a single column, value 1, iirc.

    Tom Kyte's responses to people asking if they can change it to something else are quite amusing, mostly coming down to "Why would you want do that you fucking idiot?". Come to think of it that's his response to a lot of questions.

  • (cs) in reply to hikari
    Nagesh:
    Agreed. Dual is real table owned by system.

    The owner of dual is SYS, but dual can be accessed by every user.

    hikari:
    It just contains a single row and a single column, value 1, iirc.

    "dual" is a table that always exists in an Oracle database. It consists of exactly one column whose name is dummy and one record. The value of that record is X.

    hikari:
    Tom Kyte's responses to people asking if they can change it to something else are quite amusing, mostly coming down to "Why would you want do that you fucking idiot?". Come to think of it that's his response to a lot of questions.

    "Yes, it will (can) return an 'X', but as i'm trying to show in the examples, you can select just abount anything you want...

    I didn't say it was a dummy table; it contains a column called "dummy""

    http://en.allexperts.com/q/Oracle-1451/Dual.htm

  • C-Derb (unregistered) in reply to hikari
    hikari:
    Stephen:
    “dual” is a fictional table

    No; "dual" is a real table and, prior to Oracle 10g, caused real logical IO to happen. "select * from dual" if you're really bored.

    It just contains a single row and a single column, value 1, iirc.

    Tom Kyte's responses to people asking if they can change it to something else are quite amusing, mostly coming down to "Why would you want do that you fucking idiot?". Come to think of it that's his response to a lot of questions.

    I'm pretty sure that was his response, word for word, when someone suggested they develop a half decent administration interface.

  • bobby (unregistered) in reply to sagaciter
    sagaciter:
    Oracle is TRWTF.
    Fixed!

Leave a comment on “Where We're Going, We Don't Need Tables”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article