Re: Managing and Maintaining Report Queries in APEX

Hmmm... You may have a point, Sayan.
I haven't used implicit result sets, so I'll have to experiment. Thanks for the idea :-)

Cheerio
/Kim

On Wed, Apr 21, 2021 at 1:12 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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_at_gmail.com> 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_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.
>>>
>>>
>>>
>>>
>>>
>>>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

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