CODEWITHSUNDEEP
plsqloracle-apex
kangaroo_pope
kangaroo_pope

Reputation: 49

How can the results of a large SQL query be added to an APEX Collection (issue with PL/SQL Code editor character limit)?

I'm using APEX to display the results of a complex (250 lines) query. I want to put the result of this query into a Collection, but when I define my query in the PL/SQL code editor for the Dynamic Action that populates the Collection, it says the value is too long ('Value too long by 4840 characters!')

I've looked up max varchar2 length (32767 bytes, if we assume the upper limit of 3 bytes per character, that's 10k and change characters, this query uses 9,200), and it seems to be measuring by the length of everything in the window (for example, if I delete the DECLARE statement, the too long warning goes down by 7 characters).

The above implies I couldn't just break it up into separate variables and concatenate them (unless they are global and the action executes them all in pieces).

How can I get around this apparent limit to the PL/SQL editor? The same code works just fine in a simple SQL editor.

DECLARE
    l_query varchar2(32767) := '--big query'
BEGIN
    IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => 'NEW1') THEN
            APEX_COLLECTION.DELETE_COLLECTION (p_collection_name => 'NEW1');
    end if;
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
        p_collection_name => 'NEW1', 
        p_query => l_query,
        p_generate_md5 => 'YES');
End;

Upvotes: 0

Views: 2138

Answers (2)

Giliam
Giliam

Reputation: 620

What do you mean with:

"it seems to be measuring by the length of everything in the window"

The PL/SQL code can be up to 32767 characters and that means from start to end, including declare begin end.

What you don't show is the actual query and the size of that. The code above seems valid, so maybe you can share the complete code including query?

One major advise is to put PL/SQL in the database. So in this case, create a package procedure with all the logic to create the collection for you, and in the Dynamic Action you just call that procedure.

Upvotes: 0

Patrick Wolf
Patrick Wolf

Reputation: 356

Why don't you put the query into a view? Security wise this would be better that having the query assigned into a page item. At least make sure that Session State Protection = Restricted - May not be set from browser, to avoid SQL injection vulnerability.

BTW, try to use APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (https://docs.oracle.com/en/database/oracle/application-express/20.1/aeapi/CREATE_COLLECTION_FROM_QUERY_B-Procedure-NBV.html) which uses bulk fetching for better performance.

Upvotes: 1

Related Questions