DUAL table in Oracle
A developer's cross-engine 4GL code (FourJs/Genero) detected Oracle by trying "SELECT USER FROM DUAL" and falling back if it failed; it broke when someone created a DUAL table in an Informix database. Replies explained DUAL is Oracle's one-row dummy table (originally two rows, used for cartesian transposes) and that Informix's equivalent trick is selecting from systables where tabid=1, or creating a DUAL view on it. No fix was imposed: the consensus was that engine detection via DUAL is fragile and the real issue was the customer altering a schema they didn't own; the thread drifts into anecdotes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Yes, I know this is an Informix ng... Folks, I've just been informed that some (unspecified, and I don't want to know either, or I might start sending e-mails :-) customer of ours has added a table called DUAL into the Informix database, and it is blowing up our cross-engine code which has been designed to run against Oracle using FourJs 4GL. I've been asked for solutions - it would help if I knew what DUAL contains! Can I get an explanation? Mark T, I'm looking in your direction... Ideally I would like to suggest a solution that the violating customer can use to change their code, so I'm looking for a suggested work-around which lets the offender rename their table and still get the functionality they desire.
Andrew Hamm wrote: > Yes, I know this is an Informix ng... > > Folks, > > I've just been informed that some (unspecified, and I don't want to know > either, or I might start sending e-mails :-) customer of ours has added a > table called DUAL into the Informix database, and it is blowing up our > cross-engine code which has been designed to run against Oracle using > FourJs 4GL. > > I've been asked for solutions - it would help if I knew what DUAL > contains! Can I get an explanation? Mark T, I'm looking in your > direction... > > Ideally I would like to suggest a solution that the violating customer can > use to change their code, so I'm looking for a suggested work-around which > lets the offender rename their table and still get the functionality they > desire. > > I'm not sure I understand your question, or how I can help, but DUAL is just a table with a single varchar2(1) column in it called dummy, with a single row containing the value 'X'. It is of course available in every schema in Oracle. Unqualified. The actual column name, type and content isn't important, but having just a single row is. It was initially provided in the days before PL/SQL so that there was a known result to query system variables against. For instance, select sysdate into :myvar from dual; would return the current date and time. I'm not sure how this helps however ? Can you describe the actual problem
"Mark Townsend" <markbtownsend@comcast.net> wrote
> I'm not sure I understand your question, or how I can help, but DUAL is
> just a table with a single varchar2(1) column in it called dummy, with a
> single row containing the value 'X'.
>
> It is of course available in every schema in Oracle. Unqualified.
>
> The actual column name, type and content isn't important, but having
> just a single row is. It was initially provided in the days before
> PL/SQL so that there was a known result to query system variables
> against. For instance,
> select sysdate into :myvar from dual;
> would return the current date and time.
>
> I'm not sure how this helps however ? Can you describe the actual problem
SOme of our coders who have extensive Oracle expiernece wanted to
have dual capability in informix. I created a view as follows
create view dual(const) as select 1 from systables where tabid = 1
With this view, the usage of dual is just the same as in oracle.
Mark Townsend wrote:
>
> The actual column name, type and content isn't important, but having
> just a single row is. It was initially provided in the days before
> PL/SQL so that there was a known result to query system variables
> against. For instance,
> select sysdate into :myvar from dual;
> would return the current date and time.
ahhhh - ok, interesting stuff. The informix trick which you probably know
about is something like
select sysdate from systables where tabid = 1
or some such thing.
> I'm not sure how this helps however ? Can you describe the actual
> problem
wellll, the actual problem is that someone created an Informix table
called DUAL which I presume has exactly the column and one row you
mention. Now, since we use 4JS Open Database Interface to run our 4GL code
against Informix, Oracle and MS, as you can probably guess there are
various places that use DUAL only if it exists to do a technique in the
Oracle style for the Oracle engines.
Since DUAL is a convenience table from your description, it looks like
someone thinks it's a cute trick to put it into the Informix database. Or
maybe they're porting some Oracle code to run on Informix. Or maybe
they're Oracle programmers and they want this cute table to do things the
way they always have.
Now our 4GL code is being screwed because the table exists. So ...... my
solution is probably to suggest that the enterprising Unknown Programmer
should use the systables technique above, or just to use some other name.
More to the point, I wonder why anyone is authorised to add tables to our
schema too. The customers own the data, but they don't own the schema or
have authority to change the schema.
I need more info but it's my opinion that the creator of this table needs
to find another technique or toy.
rkusenet wrote:
>
> SOme of our coders who have extensive Oracle expiernece wanted to
> have dual capability in informix. I created a view as follows
>
> create view dual(const) as select 1 from systables where tabid = 1>
> With this view, the usage of dual is just the same as in oracle.
Yup - that looks like a reasonable solution, but the problem here is the
use of the name DUAL. I can't imagine what would happen if someone made a
table called "systables" in one of our Oracle customer sites. Some things
are Just Not Proper.
Some of our 4GL code does something like
whenever error ......
select user into engine.user from dual
whenever error ....
if status < 0 then ......
which is an entirely suitable technique when you have to deal with
multiple databases. Variations on the theme of course exist depending on
the need. For example, a cursor might be prepared firstly with that
SELECT; if it fails it's not Oracle, so then a MS or Informix attempt is
made as appropriate to the task.
When you put in a table that should not exist and is such a sensitive
table, all hell breaks loose. None of our programmers would ever create a
table called dual. It would never get past the "what if" stage in any
conversation at a desk or planning meeting.
Andrew Hamm wrote: > > Some of our 4GL code does something like > > whenever error ...... > select user into engine.user from dual > whenever error .... > if status < 0 then ...... > > which is an entirely suitable technique when you have to deal with > multiple databases. Variations on the theme of course exist depending on > the need. For example, a cursor might be prepared firstly with that > SELECT; if it fails it's not Oracle, so then a MS or Informix attempt is > made as appropriate to the task. > Interesting approach.
Andrew Hamm wrote:
> rkusenet wrote:
>
>>SOme of our coders who have extensive Oracle expiernece wanted to
>>have dual capability in informix. I created a view as follows
>>
>>create view dual(const) as select 1 from systables where tabid = 1>>
>>With this view, the usage of dual is just the same as in oracle.
>
>
> Yup - that looks like a reasonable solution, but the problem here is the
> use of the name DUAL. I can't imagine what would happen if someone made a
> table called "systables" in one of our Oracle customer sites. Some things
> are Just Not Proper.
>
> Some of our 4GL code does something like
>
> whenever error ......
> select user into engine.user from dual
> whenever error ....
> if status < 0 then ......
Well, I hope that's in a subroutine library rather than written out
longhand in every I4Gl program you've got.
I routinely create a table dual in my databases - I'd break your code.
I don't go around creating other DBMS system catalog tables, though --
I'd be much more inclined to go after those if there's no better way
of identifying the database server you're connected too. How does
I4GL connect to Oracle anyway -- EGM?
> which is an entirely suitable technique when you have to deal with
> multiple databases. Variations on the theme of course exist depending on
> the need. For example, a cursor might be prepared firstly with that
> SELECT; if it fails it's not Oracle, so then a MS or Informix attempt is
> made as appropriate to the task.
It wasn't a horribly awful choice - it just wasn't the best choice.
You might think about qualifying it with the correct schema name -
though Mark T implied that no schema name was necessary.
> When you put in a table that should not exist and is such a sensitive
> table, all hell breaks loose. None of our programmers would ever create a
> table called dual. It would never get past the "what if" stage in any
> conversation at a desk or planning meeting.
My dual table (normally oracle.dual in full) currently has a single
integer column, containing the value 0 and a check constraint to
ensure that only that value is permitted. And I have a script that I
run to create it when I create a database. (I also usually load up
some tables of elements, isotopes and chemical compounds, too.) I may
even fix my variant of dual up to match Mark's specification. It's a
useful concept; it probably won't ever make it into IDS officially,
but anybody can add it unofficially.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Jonathan Leffler wrote: > > Well, I hope that's in a subroutine library rather than written out > longhand in every I4Gl program you've got. Mostly :- unless it's a one-off unlikely to be > > I routinely create a table dual in my databases - I'd break your code. ahhhh - but that's YOUR database. What if I came along and added rows because I've had a different DUAL for years before I even heard of Oracle? Answer: I would have to compromise, not expect you to. Futher, you should probably tell me to butt out and leave your database alone and then cross me off your Christmas card list. > I don't go around creating other DBMS system catalog tables, though -- > I'd be much more inclined to go after those if there's no better way > of identifying the database server you're connected too. How does > I4GL connect to Oracle anyway -- EGM? It's BDL aka 4JS 4gl. And now the new Genero. 4JS makes a great effort at mapping most of the stuff that's different, but some things simply must be handled in a variant manner. > It wasn't a horribly awful choice - it just wasn't the best choice. ok - semantics. I'll withdraw the "horrible" word ;-) > You might think about qualifying it with the correct schema name - > though Mark T implied that no schema name was necessary. That would imply code change on our part, which is simply not fair. Futher, this team is about to go into code freeze for roughly 100 customers next delivery. If my recent work is correct ..... > My dual table (normally oracle.dual in full) currently has a single > integer column, containing the value 0 and a check constraint to > ensure that only that value is permitted. And I have a script that I > run to create it when I create a database. (I also usually load up > some tables of elements, isotopes and chemical compounds, too.) I may > even fix my variant of dual up to match Mark's specification. It's a > useful concept; it probably won't ever make it into IDS officially, > but anybody can add it unofficially. Yet it and several other core tables are often critical when doing multi-database code. I agree, dual sounds sorta sexy if you know you are on one engine platform. But like anything else in software, change can lead to unpleasant surprises that cost to get fixed again.
Mark Townsend wrote: > Andrew Hamm wrote: >> >> Some of our 4GL code does something like >> whenever error ...... >> select user into engine.user from dual >> whenever error .... >> if status < 0 then ...... >> > Interesting approach. Since exception handling is a formal part of the 4GL language, it is useful not just for error trapping. I expect there are many software projects out there which respond to locking using the same approach. It's one of a few multi-engine techniques that can be used. Wouldn't want to do this specific example inside a tight loop however. But a cursor could still be prepared the same way if it suits the task. Horses for Courses. Simple clean code is always best, yet it's not 100% realisable for multi-engine coding.
Andrew Hamm wrote: > wellll, the actual problem is that someone created an Informix table > called DUAL which I presume has exactly the column and one row you > mention. Now, since we use 4JS Open Database Interface to run our 4GL code > against Informix, Oracle and MS, as you can probably guess there are > various places that use DUAL only if it exists to do a technique in the > Oracle style for the Oracle engines. Just a note here to watch for if you are using the db_get_database_type function (src in src directory - dbutils.4gl) to find out what database you are using. The order of the checking is ADABAS; SQL Server: DB2: Oracle: Sybase: Postgresql: Informix The check for Oracle is as follows: IF dbtype IS NULL THEN # Try ORACLE specific statement SELECT USER INTO uname FROM DUAL IF STATUS=0 THEN LET dbtype = "ORA" END IF END IF Probably not an issue in this case but could bite you in other places. Scott
Not sure what I can say that hasn't already been said. Oracle has a
table called 'dual' which is always used for dummy select statements
(ie, SELECT 1 FROM DUAL, SELECT USER FROM DUAL, etc).
The key question which no-one seems to be asking is what exactly are
4gays selecting from DUAL - I'm reasonably certain that this is the
problem the guys are facing, not the table DUAL itself.
"rkusenet" <rkusenet@sympatico.ca> wrote in message news:<2udmujF29mc37U1@uni-berlin.de>...
> "Mark Townsend" <markbtownsend@comcast.net> wrote
> > I'm not sure I understand your question, or how I can help, but DUAL is
> > just a table with a single varchar2(1) column in it called dummy, with a
> > single row containing the value 'X'.
> >
> > It is of course available in every schema in Oracle. Unqualified.
> >
> > The actual column name, type and content isn't important, but having
> > just a single row is. It was initially provided in the days before
> > PL/SQL so that there was a known result to query system variables
> > against. For instance,
> > select sysdate into :myvar from dual;
> > would return the current date and time.
> >
> > I'm not sure how this helps however ? Can you describe the actual problem
>
>
> SOme of our coders who have extensive Oracle expiernece wanted to
> have dual capability in informix. I created a view as follows
>
> create view dual(const) as select 1 from systables where tabid = 1>
> With this view, the usage of dual is just the same as in oracle.
Hubert Hoelzl wrote: > > The key question which no-one seems to be asking is what exactly are > 4jays selecting from DUAL - I'm reasonably certain that this is the > problem the guys are facing, not the table DUAL itself. Actually, the Primary key question (ok, sorry, really bad pun) is why someone was altering the schema of a database they didn't design and are not responsible for the warranty on the design. It doesn't matter if it's the customer or a 3rd party software supplier who's the guilty party either. I'm not talking about whether the customer "owns" their data or not. I don't think anyone would dispute that customers own their data. But they don't necessarily own their schema unless there has been prior arrangement. Adding a table or other schema changes is like adding a 5th wheel to my car and then demanding that the car company fixes it when I crash into a tree. There's no doubt that I own my car, but I can't complain to the manufacturer if I make the car unfit for it's purpose. I know for a fact that our database schema is so large that there can be no room for users adding tables or making any alterations without permission. It's unlikely to be denied if it's conservative and reasonable. In this case, alarm bells would have gone off immediately. Anyway, I've had plenty of explanation as to it's purpose, and why someone might be doing it (trying to ease code compatibility sounds persuasive as a reason). It's interesting; our guys which included some Oracle experts decided to stay well clear of literal mimicry of Informix or Oracle (and now MS) features in the other databases. Instead they went for abstraction where there were differences; hide the differences behind suitable views etc. Sort of like structured programming - freaky! On a not quite completely related subject, I have to ask - why the odd choice of name? I can't think of a reason why "dual" implies the use of this table. The table has one row and one field; where's the duality in that? Is there another table with 2 columns and 2 rows which is called "triplicate"? Mark, I'm looking in your direction again...
Mark Townsend wrote: > Andrew Hamm wrote: > >> Yes, I know this is an Informix ng... >> >> Folks, >> >> I've just been informed that some (unspecified, and I don't want to know >> either, or I might start sending e-mails :-) customer of ours has added a >> table called DUAL into the Informix database, and it is blowing up our >> cross-engine code which has been designed to run against Oracle using >> FourJs 4GL. >> >> I've been asked for solutions - it would help if I knew what DUAL >> contains! Can I get an explanation? Mark T, I'm looking in your >> direction... >> >> Ideally I would like to suggest a solution that the violating customer >> can >> use to change their code, so I'm looking for a suggested work-around >> which >> lets the offender rename their table and still get the functionality they >> desire. >> >> > I'm not sure I understand your question, or how I can help, but DUAL is > just a table with a single varchar2(1) column in it called dummy, with a > single row containing the value 'X'. > > It is of course available in every schema in Oracle. Unqualified. > > The actual column name, type and content isn't important, but having > just a single row is. It was initially provided in the days before > PL/SQL so that there was a known result to query system variables > against. For instance, > select sysdate into :myvar from dual; > would return the current date and time. > > I'm not sure how this helps however ? Can you describe the actual problem In addition to being a table, with 10g, isn't it also a C structure to keep developers and DBAs from messing it up? -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)
DA Morgan wrote: > Mark Townsend wrote: > > In addition to being a table, with 10g, isn't it also a C structure to > keep developers and DBAs from messing it up? > Yep - but I omitted this detail as I didn't think it would be relevant to the discussion
Andrew Hamm wrote: > > On a not quite completely related subject, I have to ask - why the odd > choice of name? I can't think of a reason why "dual" implies the use of > this table. The table has one row and one field; where's the duality in > that? Is there another table with 2 columns and 2 rows which is called > "triplicate"? Mark, I'm looking in your direction again... > I went back to some of our elders on this one. Evidently the table did initially contain two rows. Supposedly a very early data dictionary table consisted of a list of tables in the database, with a column for disk space used, and another column for disk space used by the corresponding indexes. I'm not sure I understand fully why this was done, but supposedly the story goes that by having a table with two dummy rows - one called "Table" and one call "Indexes", you could do a cartesian join on the data dictionary and transpose the column values to row values, one for the table values and one for the index values, which you could then filter and sum etc and build reports out of. Of course, this was way before UNION etc. The 'transposing' table was called DUAL, because if you joined in this way, you got twice the number of rows that you had in your initial target. Obviously a change in data dictionary design, and stronger SQL got around this trick in later releases, but the idea of using DUAL as a table to produce a result set in the format you wanted persisted. Strangely enough, however, nobody can remember when DUAL went to just one row, or why.
Mark Townsend wrote: >> > I went back to some of our elders on this one. > > Evidently the table did initially contain two rows. > >[SNIP] hehheh - I love stories like this. It's like the (true) story about how the size of the space shuttle is based on the width of two Roman chariot horse arses. The computer industry is only a few decades old and it's already locked down with tradition, dogma, and unfixable mistakes. I don't think we'll ever see the day when our job can truly be called Computer SCIENCE.
Andrew Hamm wrote: > > The computer industry is only a few decades old and it's already > locked down with tradition, dogma, and unfixable mistakes. I don't > think we'll ever see the day when our job can truly be called > Computer SCIENCE. And for a sense of deja-vu... I've just been told about a customer site (of a different team) where a Perl script has stopped working. Piece of paper was put under my nose showing the error message: Perl v5.8.5 required--this is only v5.8.1 I supplied them with a build 5.8.5 which was built including the multi-threading; an essential part of the scripts functioning. Without asking anyone, they've picked up 5.8.1 from the Sun website and over-installed. It will also not contain multi-threading. Luckily I put a Perl version requirement check into the script. This is what my life has been reduced to. (am I making you cry? I am)
Andrew Hamm wrote:
>
>This is what my life has been reduced to. (am I making you cry? I am)
>
>
>
>
Oh boy! Horror story time :)
At my last job a well meaning user decided to do their own report. They
used somethins simmilar to the following query:
select jan.total, feb.total, mar.total, apr.total, may.total,
jun.total, jul.total, aug.total, sep.total, oct.total,
nov.total, dec.total
from month_data jan, month_data feb, month_data mar, month_data apr,
month_data may, month_data jun, month_data jul, month_data aug,
month_data sep, month_data oct, month_data nov, month_data dec
where jan.month = 1 and feb.month = 2 and mar.month = 3 and apr.month = 4
and may.month = 5 and jun.month = 6 and jul.month = 7 and aug.month = 8
and sep.month = 9 and oct.month = 10 and nov.month = 11 and dec.month = 12
and jan.custid = feb.custid and feb.custid = mar.custid and mar.custid= apr.custid
and apr.custid = may.custid and may.custid = jun.custid and jun.custid
= jul.custid
and jul.custid = aug.custid and aug.custid = sep.custid and sep.custid
= oct.custid
and oct.custid = nov.custid and nov.custid = dec.custid
month_data had 600,000 records, 12 per customer. It was running on a
Unixware "non-stop cluster". Non-stop my a......
When the directors of the company reviewed the wreckage they took prompt
action and decided that after their employee took down the system the
soloution was to take away _our_ superuser privileges . We couldn't
figure it out either...
So, if you want to bring tears to my eyes you'll need to fill me in on
the sanctions taken against you for the customer doing something as
reasonable as updating Perl for you. What a nice customer!
--
Scott Burns
Mirrabooka Systems
Tel +61 7 3857 7899
Fax +61 7 3857 1368
Scott Burns wrote: > > So, if you want to bring tears to my eyes you'll need to fill me in on > the sanctions taken against you for the customer doing something as > reasonable as updating Perl for you. What a nice customer! the sanctions against me includes being held prisoner dealing with this situation. PS - it was a downgrade of Perl. An upgrade might be acceptable, if they managed to find a binary that contained the threading and the 3 specialist modules I built into my binary.
Andrew Hamm wrote: >Scott Burns wrote: > > >>So, if you want to bring tears to my eyes you'll need to fill me in on >>the sanctions taken against you for the customer doing something as >>reasonable as updating Perl for you. What a nice customer! >> >> > >the sanctions against me includes being held prisoner dealing with this >situation. > > > Sorry, not quite there. Without that final insult to injury knife twist you only make it as far as a wince and a softly muttered "you poor b..." >PS - it was a downgrade of Perl. An upgrade might be acceptable, if they >managed to find a binary that contained the threading and the 3 specialist >modules I built into my binary. > > > > Probably not applicable here, but the nice thing about RPM is that if you had built an RPM with a specific dependancy it would have firstly required special switches for them to downgrade, and secondly screamed bloody muder because of your dependancy, requiring more switches to force it. Of course, it can be a real pain to use RPMs when you have a bunch of little programs, and even worse on systems that were not installed using it (most of them ) and doesn't protect against people installing tarballs. The thought of setting root's default shell to script has apealed to me more than once. -- Scott Burns Mirrabooka Systems Tel +61 7 3857 7899 Fax +61 7 3857 1368