CODEWITHSUNDEEP
oracle-apex
smackenzie
smackenzie

Reputation: 3022

Dynamic Content Region not able to read in page item in Oracle Apex

I have a Dynamic Content section in APEX.

declare
    l_result                clob;
    l_email_id              number;
    l_email_subject         VARCHAR2(200);
    l_file_name             VARCHAR2(200);
    l_url                   VARCHAR2(400);
    l_app                   number := v('APP_ID');
    l_session               number := v('APP_SESSION');
    l_attachment_id         number := v('P1_ID');

    
begin

    sys.dbms_output.enable;
    sys.dbms_output.put_line('some data'); 
    sys.dbms_output.put_line('other data'); 
    sys.DBMS_OUTPUT.PUT_LINE('DBMS --> P1_ID: ' || l_attachment_id);
    sys.DBMS_OUTPUT.PUT_LINE('DBMS --> l_app: ' || l_app);
    sys.DBMS_OUTPUT.PUT_LINE('DBMS --> l_session: ' || l_session);
    apex_debug.log_dbms_output; 

end;

While my debug logs return valid values for the APP_ID and the SESSION, my page item 'P1_ID' is being resolved to '&P1_ID.' rather than '15' which is the value of the page item.

So this is the issue:

 l_attachment_id         number := v('P1_ID');

So I am getting "invalid number" type errors.

Neither does using bind syntax work ':P1_ID'.

Surely I can read in a page item in a PL\SQL block in a Dynamic Content component?

Why is:

v('P1_ID')

returning:

&P1_ID.

And not 15, the value of the page item in session state?

I am using APEX 23.1.

Upvotes: 0

Views: 430

Answers (2)

smackenzie
smackenzie

Reputation: 3022

P1_ID query parameter was missing.!

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18565

The dynamic content region changed a couple of versions ago. In the old version (now called PL/SQL Dynamic Content [legacy]) the syntax is:

htp.p(:P104_ITEM1);
htp.p(:APP_PAGE_ID);

The new one called Dynamic Content is a pl/sql function returning a CLOB

declare
    l_result                clob;
begin
    l_result := :P104_ITEM1;
    l_result := l_result ||'<br>'||:APP_PAGE_ID;
    return l_result;
end;

I'd refrain from using the "V-syntax" V('P104_ITEM1') within the app. It is a lot slower than just using bind variable syntax :P104_ITEM. The V-syntax is meant to be used in other objects to make them compile (packages, views, triggers).

I also think the use of the htp procedures is preferred over using dbms_output.

Upvotes: 1

Related Questions