Re: tool: convert classic oracle sql to ansi?

From: DA Morgan <>
Date: Tue, 30 Dec 2008 10:03:47 -0800
Message-ID: <> wrote:

> On Dec 20, 6:33 am, DA Morgan <> wrote:

>> wrote:
>>> On Dec 18, 6:27 pm, DA Morgan <> wrote:
>>>> steph wrote:
>>>>> On 16 Dez., 21:56, ddf <> wrote:
>>>>>> On Dec 16, 11:18 am, steph <> wrote:
>>>>>>> Hi Group,
>>>>>>> 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
>>>>>> "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
>>>>> Yes, correct: a tool to convert oracle join syntax to ansi join syntax
>>>>> - that's what I'm looking for!
>>>> 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
>>>> (replace x with u to respond)
>>>> Puget Sound Oracle Users quoted text -
>>>> - Show quoted text -
>>> I think it is clearer as it better separate joins from selection
>>> criteria

>> 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
>> SELECT unique inst_id FROM qb1)
>> FROM qb1, qb2
>> WHERE qb1.inst_id = qb2.inst_id;

>> or this:

>> SELECT srvr_id
>> FROM (
>> SELECT srvr_id, SUM(cnt) SUMCNT
>> FROM (
>> FROM servers
>> 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
>> (replace x with u to respond)
>> Puget Sound Oracle Users Hide quoted text -

>> - Show quoted text -
> 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

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



as they are with:

if BooleanExpression then


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 (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Tue Dec 30 2008 - 12:03:47 CST

Original text of this message