[wfriesen]

Coming from an Oracle background, both application and data warehousing, I wholeheartedly agree with the articles first paragraph saying that application based dynamic SQL is horrible, but DBA (and I'll include data warehousing) dynamic SQL is great, but it's essentially about how complex the thing is.

A DBA is likely doing things like looping over metadata tables and running

  'grant select on ' || some_table || ' to ' || some_user

While an app developer with free reign will end up doing something much more complex, and much harder to reason about/tune

[chasil]

It is also somewhat frustrating that bind variables cannot be used with DDL.

I am perfectly able to:

  SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME=:var;

But I cannot:

  ALTER USER :var ACCOUNT UNLOCK;

I really don't understand why that capability wasn't added a decade ago, as loud as the advice is to avoid hard parsing.

[marcosdumay]

That, and the lack of functions with a varying number of parameters, and lack of type polymorphism, and of easy to construct local views, and the lack of sum types...

Dynamic SQL is used on practice to fix a lot of problems the SQL people have been refusing to touch.

[justinclift]

Yeah. This is a definite problem when working with user generated databases (eg untrusted), that could have pretty much anything in them. :/

[nextaccountic]

What about some_user has characters that need to be escaped?

[rickboyce]

The parent article touches on this point too - both Oracle and SQL Server (and any other major db) supports parameterising values in dynamic sql. some_user would become @some_user (SQL Server) or :some_user (Oracle).

(The characters never actually get escaped with parameterisation - they are not part of the query text when it is parsed so can’t affect it - hence parameterising a value in sql query replaces the need to escape it with something much more robust.)