Hi Kim,
Doesn't it support implicit result sets?
ie DBMS_SQL.RETURN_RESULT(refcursor)?
On Wed, Apr 21, 2021 at 9:17 AM Kim Berg Hansen <kibeha@xxxxxxxxx> wrote:
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@xxxxxxxxx
@kibeha <http://twitter.com/kibeha>
On Wed, Apr 21, 2021 at 7:30 AM Stefan Knecht <knecht.stefan@xxxxxxxxx>
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.
