Re: Managing and Maintaining Report Queries in APEX

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