tool: convert classic oracle sql to ansi?
steph
I do this manually from time to time - but maybe there's a better
way ...
Does anybody know of a way, tool or whatever to convert classic oracle
sql-statements to ansi-sql and vice versa? Maybe the database is able
to do so?
Thanks,
Stephan
ddf
"Classic" Oracle SQL? Hmmm ...
Selecte *
From Ye Olde Dual;
Or are you asking for a 'tool' to 'translate' Oracle join syntax (such
as outer joins) into the ANSI equivalent?
I know of no such tool, but, I've been wrong before.
David Fitzjarrell
sybr...@hccnet.nl
ANSI joins are unreadable anyway.
Sybrand Bakker
Senior Oracle DBA
camh...@gmail.com
I agree, give me the classic Oracle join syntax any day
steph
Sybrand, thanks for your opinion - but actually when being confronted
with a 100s-of-lines sql i find ansi-joins more readable. Why? Because
tables and associated joins tend to be on the same line. But what is
good for me needn't be good for you and I don't want to start a
discussion about this.
steph
Yes, correct: a tool to convert oracle join syntax to ansi join syntax
- that's what I'm looking for!
Mladen Gogala
> Yes, correct: a tool to convert oracle join syntax to ansi join syntax -
> that's what I'm looking for!
The tool is called "vi". It's an excellent GUI tool which allows
you to read an Oracle query and modiy its syntax in a way that you
like.
Palooka
> On Wed, 17 Dec 2008 03:21:19 -0800, steph wrote:
>
>
>> Yes, correct: a tool to convert oracle join syntax to ansi join syntax -
>> that's what I'm looking for!
>
> The tool is called "vi". It's an excellent GUI tool which allows
> you to read an Oracle query and modiy its syntax in a way that you
> like.
>
steph
thanks for the excellent hint. can i use a mouse?
i give up on this topic ...
DA Morgan
It would take less time to write your own with perl than it has taken
to amuse everyone here by asking the question. The vast majority of
us, knowing Oracle, consider the ANSI joins a waste of perfectly good
keystrokes.
Lets see:
SELECT *
FROM t1,t2
WHERE t1.col1 = t2.col2;
Replace the comma with ' INNER JOIN ' (add ten keystrokes)
Replace WHERE with ON (subtract three keystrokes)
and it looks like this:
SELECT *
FROM t1 INNER JOIN t2
ON t1.col1 = t2.col2;
And that is worth your time and your employer's money?
And one is clearer than the other?
I'd love to hear the explanation.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Shakespeare
Actually you can! Let it run over your keyboard and just pray it types
the right commands. Wouldn't be any worse than when I used VI..... I
guess the mouse would do better.
Shakespeare
(What's mickey proof?)
Palooka
a doddle - especially when your typing is as poor as mine :-)
Palooka
kooro...@gmail.com
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
I think it is clearer as it better separate joins from selection
criteria and find it easier to spot missing joins which a few
developers [and myself] tend to do using the old syntax with complex
queries - killing dev with a cross join in the process. So for me we
save time/$$ by less errors and rework. For simple queries it is a bit
of a trade off.
rm
Michael D O'Shea
> sql-statements to ansi-sql and vice versa?
> Stephan
Hi Stephan, I do not know of a tool to perform this task but expect it
would be neither difficult nor time consuming to write one.
To convert between the varying syntax of Oracle and ANSI SQL, your
approach should be cautious of just replacing (+) with "INNER JOIN"
etc. AFAIK the ANSI join syntax offers more rich functionality than
the familiar Oracle specific syntax. For example (below), this compact
ANSI full outer join cannot be represented so neatly and clearly using
the Oracle specific syntax.
Regards
Mike
TESSELLA Michael.OS...@tessella.com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429
SQL>
SQL>
SQL>
SQL> SELECT t1.a, t2.a
2 FROM tblTest1 t1 FULL OUTER JOIN tblTest2 t2
3 ON t1.a = t2.a;
A A
---------- ----------
6 6
7 7
8 8
9 9
10 10
5
3
1
2
4
13
15
12
11
14
15 rows selected.
SQL>
SQL> SELECT *
2 FROM tbltest1;
A
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> SELECT *
2 FROM tblTest2;
A
----------
6
7
8
9
10
11
12
13
14
15
10 rows selected.
SQL>
SQL>
SQL> SELECT *
2 FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production
SQL>
DA Morgan
And this is important to a trained professional for what reason?
If you can't read:
WHERE t1.x = t2.x
you are in the wrong profession.
and find it easier to spot missing joins which a few
> developers [and myself] tend to do using the old syntax with complex
> queries
Which indicates to me that your training needs to be improved not that
every query needs to be rewritten which is far more expensive especially
when you add in a full testing cycle.
- killing dev with a cross join in the process. So for me we
> save time/$$ by less errors and rework. For simple queries it is a bit
> of a trade off.
This isn't about you this is about your employer. And one week of
training, at most a few thousand dollars, is a lot cheaper than
rewriting and retesting working code.
If a little thing like classic Oracle vs ANSI joins has you in a twist
what are you going to do when you see something like this?
SELECT num_rows, index_name
FROM (SELECT table_name, num_rows FROM user_tables) a,
(SELECT index_name, table_name FROM user_indexes) b
WHERE a.table_name = b.table_name(+);
or this:
WITH qb1 AS
(SELECT inst_id FROM gv$session),
qb2 AS
(SELECT unique inst_id FROM qb1
UNION ALL
SELECT unique inst_id FROM qb1)
SELECT /*+ MATERIALIZE */ *
FROM qb1, qb2
WHERE qb1.inst_id = qb2.inst_id;
or this:
SELECT srvr_id
FROM (
SELECT srvr_id, SUM(cnt) SUMCNT
FROM (
SELECT DISTINCT srvr_id, 1 AS CNT
FROM servers
UNION ALL
SELECT DISTINCT srvr_id, 1
FROM serv_inst)
GROUP BY srvr_id)
WHERE sumcnt = 2;
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
hpuxrac
> kooroori...@gmail.com wrote:
> >> And that is worth your time and your employer's money?
Typical mis direction from Captain Morgan here ...
> >> And one is clearer than the other?
> >> I'd love to hear the explanation.
A better question ...
Poster replies
> > I think it is clearer as it better separate joins from selection
> > criteria
Possibly a valid point to some people ... I will give you that.
Poster is asserting their opinion.
Then Captain Morgan jumps in with the pontificating and the
posing ...
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
Looks like the typical spamming of the newsgroup here
DA Morgan
> Then Captain Morgan jumps in with the pontificating and the
> posing ...
Which, of course, is not what you are doing yet again eh?
> Looks like the typical spamming of the newsgroup here
I agree. You should stop doing it. Every time you post you are
just inviting me to post a response thus allowing me the solid
platinum privilege to again recommend that people quit their
jobs and relocate to Seattle Washington so they can take my
class at the U: Like that is going to happen. Thanks John.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
steph
> hpuxrac wrote:
> > Then Captain Morgan jumps in with the pontificating and the
> > posing ...
>
> Which, of course, is not what you are doing yet again eh?
>
> > Looks like the typical spamming of the newsgroup here
>
> I agree. You should stop doing it. Every time you post you are
> just inviting me to post a response thus allowing me the solid
> platinum privilege to again recommend that people quit their
> jobs and relocate to Seattle Washington so they can take my
> class at the U: Like that is going to happen. Thanks John.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> Puget Sound Oracle Users Groupwww.psoug.org
i came to the conclusion that i never will attend your classes as you
seem to sell sentiments not knowledge ...
DA Morgan
Amazing.You came to a conclusion without a single data point. I wish I
had your keep insight.
But no matter ... there aren't any empty seats in the current academic
year. Perhaps you can repost this in September 2009 during open
registration when I promise to give it all the consideration it demands.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
hpuxrac
snip
> I agree. You should stop doing it. Every time you post you are
> just inviting me to post a response thus allowing me the solid
> platinum privilege to again recommend that people quit their
> jobs and relocate to Seattle Washington so they can take my
> class at the U: Like that is going to happen. Thanks John.
I don't recall ever giving you permission to use my first name.
joel garry
I'm looking at this in google groups. I click on "View profile" and
it says your name is John Hurley. If you don't want people to call
you by your name, perhaps you should change it to Bītme Dümas.
jg
--
@home.com is bogus.
http://en.wikipedia.org/wiki/Horus
kooro...@gmail.com
Have a few days leave and come back to this?
Thanks for the lambasting of my SQL skills Daniel. I don't really see
the inference you draw. Then you post some rather simple SQL using in-
line views, UNIONS outerjoins and WITH clauses - inferring i would not
be able to understand them. In fact to me your first query seems so
much shorter, simpler and easy to understand as:
SELECT a.num_rows, index_name
FROM user_tables a
LEFT OUTER JOIN user_indexes b USING (table_name);
Perhaps you need some SQL training in simplifing your SQL and saving
your employees money - but since consultants seem to change by the
hour :)
I also don't advocate re-work (converting existing queries) unless
there is some need - such as a performance problem.
rm
DA Morgan
Not lambasting your skills at all.
Anyone that has been in this industry for more than a few years should
be as comfortable with:
if BooleanExpression then
StatementIfTrue
else
StatementIfFalse;
as they are with:
if BooleanExpression then
StatementIfTrue;
else
StatementIfFalse;
end if;
and numerous other variations. Surely you can easily read both of these
You may be the world's greatest coder. I don't know because I don't know
you. What I do know is that the amount of flexibility required to read
both traditional Oracle and ANSI Standard joins is minimal. And after
teaching both for nine years to students I have a very good idea of how
easy it is for people, who want to, to learn both.
I didn't rate you ... you rated yourself.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
kooro...@gmail.com
Perhaps "lambasting" is a little strong but we appear to disagree on
the meaning of your words "Which indicates to me that your training
needs to be improved ..." and "If a little thing like classic Oracle
vs ANSI joins has you in a twist ..." which, to me, is you attempting
to berate my skills. Somewhat similar sematic arguments as to SQL
which, as with English sentences, can (often) be written in many ways
to achieve the same end result perhaps with subtle differences
(meaning and clarity in English; perhaps performance or clarity in
SQL).
You are correct, after a short time you can easily enough read either
"Oracle" or "ANSI" SQL and it becomes a personal or company
(standards) preference.
One of the main reasons I started to prefer ANSI (aside from
developers having learnt it and not "Oracle" SQL) is for clarity of
joins, especially outer joins. I agree with Kevin Meade ( orafaq.com )
that outer joins are one of the most error prone areas and I have had
to spend time sorting out such queries that "don't return the right
results". Particularly doing a full outer join in ANSI syntax is much
clearer than with Oracle.
Enough of this thread, it really comes down to personal preference, I
feel there really little extra/less effort either way.
Have a Happy New year Daniel
rm
Lukas Eder
I'm obviously late to this discussion, but my company offers such a tool for free on this website:
https://www.jooq.org/translate
Or as a commercial library to download
Hope this helps
Lukas