5 ms·

Coming from an Oracle background, both application and data warehousing, I wholeheartedly agree with the articles first paragraph saying that application based

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

nextaccountic 3y ago
What about some_user has characters that need to be escaped?
- rickboyce 3y ago
  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.)
- deleted 3y ago
  [deleted]
chasil 3y ago
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.
- justinclift 3y ago
  Yeah. This is a definite problem when working with user generated databases (eg untrusted), that could have pretty much anything in them. :/
- marcosdumay 3y ago
  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.

nextaccountic 3y ago

What about some_user has characters that need to be escaped?

rickboyce 3y ago
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.)
deleted 3y ago
[deleted]

chasil 3y ago

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.

justinclift 3y ago
Yeah. This is a definite problem when working with user generated databases (eg untrusted), that could have pretty much anything in them. :/
marcosdumay 3y ago
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.