Re: Managing and Maintaining Report Queries in APEX

Personally I've never understood why APEX doesn't seem to accept a "function returning ref cursor" as a source.

If it did, that would be my preference, giving me the SQL in a stored package for source control.
And for 99% of cases that would be OPEN cursorvar FOR <<static>> sql statement, giving me automatic bind variables like any other static SQL in PL/SQL.
The last 1% cases might need a generated string and OPEN the cursorvar with <<dynamic>> SQL - but it still offers good use of binds similar to EXECUTE IMMEDIATE. I've used ref cursors a lot with success, but sorry it doesn't help in the case of APEX ;-)

Cheerio
/Kim

Regards

Kim Berg Hansen
Senior Consultant at Trivadis
Oracle ACE Director

Author of Practical Oracle SQL
<https://www.apress.com/gp/book/9781484256169> http://www.kibeha.dk
kibeha_at_kibeha.dk
_at_kibeha <http://twitter.com/kibeha>

On Wed, Apr 21, 2021 at 7:30 AM Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> What's your take on how to handle this?
>
> There's several possibilities, and I'd love to hear what y'all think about
> these.
>
>
> a) Toss the query straight into APEX, and manage it there.
>
> The main drawback here is the lack of proper source control. I can't see
> what changes are made to the queries and how exactly a bug was fixed by
> looking at source control (git, etc) and the fact that patching something
> or fixing a bug must be done through apex (export the page, and then import
> the page at the target end - lot of other stuff that can fail, and it's
> also not granular enough for my liking (e.g. there may be other changes on
> that page that I don't want to deploy just yet along with that 1 query's
> hotfix).
>
> b) Use views
>
> I like that a lot. But v() kills performance more often than not, and you
> lose the ability to properly use binds. That's a real killer.
> But manageability and maintainability is top.
>
> c) Use "Function Body returning SQL Query" and maintain it all in a
> package or some.
>
> It seems a bit of both, really? I get to maintain my queries in code with
> proper source control, and I can make good use of binds.
> But, particularly for larger and more complex queries, it quickly becomes
> a bit of a headache to maintain that code. And yeah, nobody likes SQL
> generators. It's easy to get caught in a neverending rabbit hole and your
> simple return 'select ..' becomes a nightmare of nested if's and cases and
> whatnot. Performance likely very good, but maintainability likely to become
> terrible very fast. I get headaches already just thinking of debugging that
> thing.
>
>
> d) Use pipelined table functions
>
> Your report query in apex becomes "select * from table(bind1, bind2,
> bind3)" and you manage your logic in functions.
> Downside is that you need to maintain a large number of types, some of
> which can't be handled fully online (e.g. you need to drop the table type
> before you can replace the underlying object() type).
>
>
> So yeah, sorry for the wall of text - but, what's your thoughts on this?
>
>
> PS: Yes I'm aware you can export the app and toss that into github - but
> it's just clumsy to use. And again, no granularity at all. APEX desperately
> needs proper source control integration.
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l