Toad World® Forums

Convert Oracle SQL Joins To ANSI SQL Syntax

Hi.

I am using Toad for Oracle 12.6.0.53 Base Edition against Oracle server 11g (11.2.0.3).

I have two questions.

I am looking for a way to convert SQL statements written using Oracle’s pre-ANSI92 join syntax to ANSI92 syntax and vice-versa. Does Toad provide a way to do this? Also, may this be done against many such statements automatically without my having to convert each one individually/manually one-by-one?

Is there a way to access APEX from within Toad. I do not mean simply SELECTing from APEX related tables/views in the APEX schema that is created by default when Oracle was installed. Instead, I ma talking about actually creating and running an APEX application from within Toad.

Thank you.

Elie Grunhaus

egrunhaus@gmail.com

  1. Rt-click on your statement in Editor and look at the Refactoring submenu. There’s also an ANSI Join Converter action in the Automation Designer that can handle files containing multiple statements. That will only convert to ANSI join syntax and not back to Oracle join syntax though.

Michael

  1. You can backups of your APEX applications with Database > Export > APEX Export. If you wanted to automate your backups, then there is an APEX export action in Automation Designer. As far as creating or running an APEX application, there isn’t anything like that. The code behind an APEX application is pl/sql, so you could use the editor to change the code I suppose, but I’m not sure why you would want to, when you can just use a browser and use the Oracle APEX Development GUI. You can just use any browser, since it’s all HTML based, and you can run and test your changes in there very easily.

Morning egrunhaus_203,

Re this question:

I am looking for a way to convert SQL statements written using Oracle’s pre-ANSI92 join syntax to ANSI92 syntax and vice-versa. Does Toad provide a way to do this? Also, may this be done against many such statements automatically without my having to convert each one individually/manually one-by-one?

As pointed out, you can use the refactoring option in the editor to convert to ANSII, but not, it appears, back to Oracle. So, this might help:

In the editor, select and copy your query WITHOUT THE TRAILING SEMICOLON.

Go to Database->Report->Query Builder.

Click on the Generated Query tab.

Ctrl-C to paste your query in.

Press CTRL+SHIFT+D and wait while the diagram is updated. You need to do this, it doesn’t work without it.

Above the generated query tab, there’s a toolbar. The 6th button from the left as “asnii” and a green arrow. If depressed, the query will be converted to ANSII format. If you click it again, to raise it, the ANSII query gets converted to Oracle again.

And yes, you will have to do this one by one I’m afraid.

Cheers,

Norm. [TeamT]