Re: More Ammo Against Dynamic SQL?
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: michael.fontana@xxxxxxxxxxx, rgravens@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Nov 2009 18:27:07 +0000
It rather depends what you mean by enterprise scale. Metalink 2 (RIP)
Used APEX extensively. As of course does apex.oracle.com. There are a
fair few other non-oracle sites that also use it. Personally I think
that rumpi has happened upon a particular effect of APEX here, namely
that it doesn't (incredibly) support either ref cursors or (I Think)
pipelined table functions in its designers. This means that designing
a future proof PL/SQL api for apex apps is somewhat difficult. I do
however agree with rumpi that insisting on constructs that increase
the time to do the job for little apparent benefit is unhelpful. I'd
imagine that many on this list would object to being forced to use EM
for admin tasks for example, preferring instead to see if it increased
productivity or reliability or some such. It seems to me rather that
the arguments from supportability and debugging are more appropriate.
In addition I'd suggest that code reuse and auditing count here, I
don't know of any way to reuse code effectively if it is generated on
the fly. Calling the add order procedure from new years ruby on rails
app is no more difficult than from last millenium's cobol app.
Equally if multiple apps and app servers are calling the add order
app, how much easier to audit the add order procedure for correctness
etc if there is only one of them. Of course most developers never
imagine that there could be another app that might use the same
corporate data with the same business rules as theirs so coding data
access dynamically in the only true app seems reasonable. This even
applies to people selling an all new "single version of the truth"
app. So for all these reasons i think that the helsinki argument from
Toon applies, but that it won't prevail.
On 11/24/09, Michael Fontana <michael.fontana@xxxxxxxxxxx> wrote:
> Very clever, Rumpi. Using an oracle product against my argument. Good
> choice, too, since I've worked with developers who use this tool. What's
> also good about your choice is that it actually can design and build a data
> model on the fly. I wonder how many enterprise-scale applications are
> running with APEX? I hope not too many. I don't know if it was intended
> for this purpose.
>
> However, were the database and application properly designed to begin with,
> a case could be made that the existing stored business rules
> objects would be referenced, rather than generating ad-hoc or dynamic SQL.
>
> I do understand your reasoning. In the absence of such proper design, and
> those with the time and skills to do so, it is very tempting and inviting to
> skip such activities when a code-generating tool is available (not to
> mention good money having been spent on it). I still say that such tools,
> left uncontrolled, will wreak havoc and are ultimately not scaleable. This
> would be especially true with a successful web app.
>
>
>
> ----- Original Message -----
> From: "Rumpi Gravenstein" <rgravens@xxxxxxxxx>
> To: "Michael Fontana" <michael.fontana@xxxxxxxxxxx>
> Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
> Sent: Tuesday, November 24, 2009 10:49:39 AM GMT -06:00 US/Canada Central
> Subject: Re: More Ammo Against Dynamic SQL?
>
> One example of a wizard driven environment is Oracle's own application
> express. I don't view the code it generates as not productive to run.
> The front end can be up and running within minutes to show reports
> and drill throughs to perform updates -- all with the click of a few
> buttons. Do this on a table and things work very well indeed. If the
> table happens to be a view with instead of triggers, the underlying
> business logic can be implemented to just about any degree of
> complexity. If you follow this design pattern the web developer has
> complete control over the look and feel -- not the SQL -- something
> the procedure only interface also enforces.
>
> On Tue, Nov 24, 2009 at 8:27 AM, Michael Fontana
> <michael.fontana@xxxxxxxxxxx> wrote:
>>
>>
>>
>>>I've been working in a shop that only allowed procedure/function/package
>>> interfaces to the database. That's all and very well and good if time and
>>> budgets are not issues. I have yet to see a web development >environment
>>> who's development wizards do not work better with a table/view than a
>>> function/procedure interface to the underlying data.
>>
>> Who or what are these "wizards" you refer to? Would you agree that even
>> if a tool makes a programmer 100x more productive, if the code generated
>> puts a 100x load on the database, it is not productive to
>> run it?
>>
>>>I would submit that a nice compromise is to use views with instead of
>>> triggers. That can provide the best of both worlds -- access to
>>> development environment productivity tools like code wizards while
>>> >maintaining a pseudo procedure/function interface to the application.
>>
>> Please provide an example of how your suggestion is better for "both
>> worlds". It almost sounds like you're intending to placate those who seek
>> efficient SQL with tricks so that your tools and methods can still
>> prevail. Can you identify at least one "code wizard" you've used to
>> produce efficient SQL in rapid fashion? It's not that the SQL doesn't
>> look good, or doesn't provide the proper results that we're discussing,
>> it's that it is not scaleable in the long run, and perhaps not the short
>> run, either. And no one wants that.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>
> --
> Rumpi Gravenstein
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
> --
>
>
>
>
>
>
> Michael Fontana
>
> Sr. Technical Consultant
>
> Enkitec M: 214.912.3709
>
> enkitec
>
> oracle_certified_partner
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Sent from my mobile device
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
