Categories
What application do you use to write your SQL
Content
I'm very curious as to what other people use to write their SQL. Personally I am using NotePad++ (with the SQL language setting to help with syntax).
I do feel that there could be more software assistance and there might be some more automated applications out there which allow searching the repository, automatic joins, etc. Having a tool like this would speed up my work tremendously.
Would like to discuss in this topic what you use and what you like/dislike about it.
Regards,
Maarten
Comments
-
Hi Maarten,
I have been using notepad++ from a very long time and generally find it is good particularly keyboard short-cuts like Ctrl+q to comment a line (which is often required), Ctrl+L to delete a line, etc. I use http://www.dpriver.com/pp/sqlformat.htm to format my SQL at times.
I am curious to learn more on this.
I do know that there is a limitation to use tools like toad or sql developer around Fusion cloud environments because of lack of db connections.
thanks,
Santosh
0 -
Been using SQL Developer, tried Toad but just never was able to switch over.
0 -
Great question. Interested to see what others are using. Personally, I use SSMS because it's familiar and on my desktop anyway. My other option is Notepad++. Wendy
0 -
Hi Santosh,
Great tips on the keyboard short-cuts, those will come in handy.
Those SQL developer tools are definitely something I'm looking for, but I am aware of the limitations. Was really hoping there's a way around and I am just doing it wrong all this time.
Maarten
0 -
Hi, Since this is a cloud forum, I would love to know how people are using desktop tools such as sqldeveloper, jdeveloper, toad etc? I am not aware that it is possible to use our favourite desktop tools to set up a database connection to issue sql on the saas cloud database. The closest thing i have found is to do pysical sql on the issue sql page in analytics. So its black and white which is a bit 1970's, but you can drag the input box using the bottom right corner to make it bigger, you only have to press one button to see the results in traditional rows and columns (not as xml), you can use a hash # symbol to comment in/out lines or /* */ for multiple lines so great for debugging. So in browser go to <cloud base url>/analytics/saw.dll?IssueRawSQL but then use execute physical then reference one of the out of the box connection pools in the metadata repository database (rpd) (so this is not logical sql on the subject areas). You can select anything that the user configured in the connection pool can see (whether it is in the physical layer in the rpd or not). BTW Your administrator must grant to your role using analytics/saw.dll?PrivilegeAdmin to grant privilege "Issue SQL Directly" and "Execute Direct Database Analysis". But beware because in the readiness documents this functionality will I think be disabled in a future release after 19D next year. I assume that leaves with us with using a physical sql query in a publisher data model which makes life a lot harder for ad hoc sql.
0 -
Hi Nathan,
For quick and dirty SQL to troubleshoot issues I use the New analysis -- > Create Direct Database Query method. This sends you to <cloud base url>/analytics/saw.dll?Answers&criteriatype=physical Here I connect to Fusion OLTP Connection Pool.
However, I did read in patch notes somewhere they're phasing this out in 20A (or some other letter, but definitely getting phased out).For actual reports, I use a Data Model + BI Publisher template (which usually ends up just being a Excel output).
For documentation, I rely on some of the tables I've memorized (such as AP_INVOICES_ALL) and https://docs.oracle.com/cloud/farel11/financialscs_gs/OEDMF/toc.htm. But mostly I use Google, because the search functionality in the Oracle knowledge base is ... not satisfactory.
My 'editor' is Notepad++, where I just hardcore code the SQL with no help whatsoever. I use this over the tiny box in Oracle so I can more easily see what I'm doing and I have some extra formatting options to make my life easier. Sometimes it feels I might as well be using pen and paper, basically the reason I asked the question here!
Regards, Maarten
0 -
Maybe I'm missing something, but it's almost as if Oracle doesn't want Cloud Application customers to use SQL to retrieve data directly from the database, but they haven't provided a meaningful replacement. Along those lines, the impending phase-out of Direct Database Queries as a "delivery mechanism" is a real bummer. Limits my ability to provide work products the users I support know how to operate (i.e. Analysis not Data Models/Reports).
I use Notepad++ as well with the previously-mentioned plugin. But I really, really, really miss proper database-specific syntax highlighting, proper auto-complete, and suggestions--even if I have to copy-paste the resulting query in order to run it.
Anyone got something better for authoring or editing (but not necessarily executing) queries to be run against the Fusion cloud database? Is there a SQL IDE out there that can use of an XML (or similar) extract of the schema or structure in lieu of an "online" connection?#confused #ironic #bummer #frustrated
0 -
I totally agree with you on this. I don't need anything to execute actual queries, but just something which helps with editing based on the known Oracle table schema. Also to explore the schema, as the Oracle repository is not always that helpful.
Having something like this would already be such a big help.
0 -
Hi all,
I found this online: https://oraclecloudtools.com/
Does anyone have experience using this tool? Is it legit? Site looks a bit crappy.I'm a bit hesitant to try this out, also because the price is quite high for the functionality it's actually providing.
Are there more third party software suites that connect to Oracle via webservices to provide a better SQL experience?
Kind regards,
Maarten0 -
Interesting. Had not heard of. As you say, "price is quite high for the functionality it's actually providing" but I'd be very interested to hear from anyone who's used the tool. Thanks, Wendy
0
