Using PL/SQL region in Oracle Application Express

Dave October 2, 2008

I'm still an Application Express beginner -- I'm used to web development that is closer to the HTML. While I am getting used to Apex's Regions and Templates sometimes you just need to build some HTML on your own. I have found some Apex helper functions that can help you whip up an anonymous PL/SQL block that can generate this.

In Apex, add a "PL/SQL" region. You will get a blank textbox where you can enter some PL/SQL code. If you have the Demo application installed you can try this out:

DECLARE
   mystring VARCHAR2(80);
BEGIN

FOR c1 in (SELECT CUST_FIRST_NAME || CUST_LAST_NAME as CUSTNAME
           FROM DEMO_CUSTOMERS
           ORDER BY 2, 1)
LOOP

    -- Output text for this entry
    htp.p('<div>'); 
    htp.p(mystring); 
    htp.p('/div'); END LOOP; END;

If you want to get fancy and say ...highlight a search term... pass in a Page Item called P_SEARCH_TEXT:

DECLARE
   mystring VARCHAR2(80);
BEGIN

FOR c1 in (SELECT CUST_FIRST_NAME || CUST_LAST_NAME as CUSTNAME
           FROM DEMO_CUSTOMERS
           ORDER BY 2, 1)
LOOP

    -- Highlight search term
    mystring := REPLACE(c1.CUSTNAME, 
                '&P_SEARCH_TEXT.', 
                '<b>&P_SEARCH_TEXT.</b>);

    -- Output text for this entry
    htp.p('<div>'); 
    htp.p(mystring); 
    htp.p(''); 
END LOOP; 
END;

Application Development Guide - Generating HTML Output with PL/SQL : here
Oracle® Database PL/SQL Packages and Types Reference - HTP : here

You should also read: