4

I'm looking for an alternative to PL/SQL. PL/SQL is mediocre at best but I haven't found anything that's even close to matching its productivity when writing Oracle database-centric scripts.

So, are there any other programming languages that offer any of the following features?

  • Natively handle SQL queries (SQL treated as code, not as a string).
  • Autocomplete for table & column names.
  • SQL is validated against DB at compile time.

*Edit: I'm well aware of, and use, IDEs for SQL and PL/SQL that offer autocomplete. I'm merely wondering if there exists any other programming language out there that lets you intermingle SQL and code in a similar way to PL/SQL.

ivanatpr
  • 1,862
  • 14
  • 18
  • 5
    In what way is PL/SQL mediocre? Given that PL/SQL in conjunction with an IDE like SQL Developer does everything you're after. Plus it is the most performant language for working with data in the Oracle database. – APC Sep 22 '11 at 14:44
  • 1
    I think you are confusing PL/SQL language with editors that handle PL/SQL. Autocomplete is not a feature of PL/SQL it is a feature of an editor that understands PL/SQL. – David Mann Sep 22 '11 at 15:27
  • 2
    In my experince, programmers who think PL/SQL is mediocre don't know how to use it fully and its specialised features or don't understand how tightly integrated it is within the Oracle database. That's not a criticism of the programmer, far from it, but it is my observation of working within databases for the past 13 years. – Ollie Sep 22 '11 at 15:28
  • @DavidMann: that's true, but the subjects states _programming language/IDE combo_, so one might assume the author means that also in the question's body. – user272735 Sep 22 '11 at 15:32
  • @APC Awkward exception handling, a lack of many built-in datatypes and functions that you take for granted in most languages, lack of anything resembling proper support for multi-threaded programming, inability to access the filesystem of the person running the script (SSH access to prod DB servers is often out of the question), hugely awkward for OOP, dealing with packages can be a PITA, doesn't respect role-base grants, etc... PL/SQL is awesome when it comes to integration with the DB, but beyond that it's a fairly poor language once you go past a certain level of complexity. – ivanatpr Sep 22 '11 at 18:02
  • 1
    @user272735 I am not good at mind reading, all I'm asking for is a clarification if they want to dump PL/SQL because they don't have a grasp on separation between languages and IDEs. OK OP, decide of PL/SQL as a language is useful to you. Separate from that check out some IDEs that are useful for programming PL/SQL - Oracle SQL Developer IDE (Free), PL/SQL Developer by Allround Automations (Cost), Toad by Quest (Cost), for a start. There are many other IDEs available for helping with PL/SQL developmentdeveopment, most include the features you listed. – David Mann Sep 22 '11 at 18:14
  • @DavidMann I'm not. Whether or not an IDE is going to be able to offer autocomplete for SQL queries is intimately tied to the design of the underlying programming language. PL/SQL IDEs can do it easily because SQL is usually code in PL/SQL, which allows the IDE to use the language's own grammar rules to distinguish when a particular statement is a SQL query. Also, a particular PL/SQL script is inherently tied to a particular database session, allowing the IDE to easily fetch the relevant table and column names for that script. – ivanatpr Sep 22 '11 at 18:37

3 Answers3

7

"Awkward exception handling, a lack of many built-in datatypes and functions that you take for granted in most languages, lack of anything resembling proper support for multi-threaded programming, inability to access the filesystem of the person running the script (SSH access to prod DB servers is often out of the question), hugely awkward for OOP, dealing with packages can be a PITA, doesn't respect role-base grants, etc... PL/SQL is awesome when it comes to integration with the DB, but beyond that it's a fairly poor language once you go past a certain level of complexity."

One by one.

Awkward exception handling
Awkwardness is in the eye of the beholder but I'll give you that one. Too much is left up to the individual developer.

a lack of many built-in datatypes ... that you take for granted in most languages
It has the same datatypes as SQL, plus BOOLEAN. What more can you want?

lack of anything resembling proper support for multi-threaded programming
Multithreading is a tricky concept to combine with read-consistency in a multi-user environment. The Aurora JVM doesn't support mult-threaded Java in Java Stored Procedures either. It's not a limitation of PL/SQL.

inability to access the filesystem of the person running the script
Again, that's not a PL/SQL issue. Ask your network administrator to map a fileshare on your PC to the network.

hugely awkward for OOP
Yeah well, OOP is on its way out, everybody's down with functional programming these days.

dealing with packages can be a PITA
Personal opinion. I love packages.

doesn't respect role-base grants
Again, that's not PL/SQL, it's the database security model. Views don't respect role-based grants either.

once you go past a certain level of complexity
Write it in Java and call if from a Java Stored Procedure. Or write it on C and call it from an extproc.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Is PL/SQL functional programming? I didn't understand the OOP comment. – johnny Dec 14 '18 at 20:52
  • @johnny - my Clojurian friends assure me that functional programming aligns better with imperative PL/SQL and set-based SQL than OOP does. – APC Dec 14 '18 at 22:39
1

I guess there is plenty of alternatives with various level of conformance to your set of requirements. Take a look e.g to:

user272735
  • 10,473
  • 9
  • 65
  • 96
0

TOAD is pretty good -- works for PL/SQL, TSQL etc. http://www.quest.com/toad/

SQL Complete is an add-in for SSMS (TSQL): http://www.devart.com/dbforge/sql/sqlcomplete/

Redgate SQL Prompt: http://www.red-gate.com

These 3 above are pretty main-stream, well-liked, well-supported. But otherwise, just pick your desired sql dialect, and google for it -- i.e. "tsql auto-complete" or "pl/sql auto-complete" to find more.

Your question also asks about a good language... I wouldn't exactly call pl/sql mediocre (unless I was trying to get a rise out of one of our tech guys anyway :-). TSQL is also good, and getting more and more 'mature', as they say, though it's still often compared to pl/sql in the context of "is tsql as mature as pl/sql yet...". But whatever you choose, "good" just means is it appropriate to your application and skillset more than anything.

Chains
  • 12,541
  • 8
  • 45
  • 62