This content has been marked as final. Show 3 replies
Some basic concepts first. Apex is a suite of PL/SQL procedures and functions and packages and database tables. Apex runs totally inside the database as stored procedural code.
What makes it unique and different is that the procedures are called by a web browser URL. This is received by Apache and passed to a module called mod_psql. This module processes the URL from the web browser, determines what database to connect to and what stored procedure call to make.
Inside the PL/SQL environment there is a set of web packages - and a buffer area that can be used to create dynamic web pages. Apex PL/SQL code executes and creates a dynamic web page/web content in this buffer. The code terminates.
The mod_plsql module in Apache then reads this buffer from the Oracle session and it streams the content to the web browser.
DBMS_OUTPUT is not specifically supported by mod_plsql as it is another buffer area (quite primitive in use and interface). It supports a basic console output display for PL/SQL code - and is typically use by session-based clients like TOAD, SQL*Plus and SQL-Developer.
So if you want a web-enabled PL/SQL procedure to display data on the web browser, your code needs to write to the web buffer in PL/SQL and not the DBMS_OUTPUT buffer. Writing to the web buffer can be done using the HTP.prn() call.
Here's a very basic example of outputting data to a web browser using custom PL/SQL code. Create a Apex page. On this page create a text item called something like P1_FIRSTNAME. This enables the web user to supply a parameter value to your PL/SQL procedure.
Create a dynamic PL/SQL region on the page, that calls your procedure:
The user procedure that receives and processes the data, and supplied web output, will look something like the following:
begin --// if the user supplied a firstname, we call the procedure to process the data if :P1_FIRSTNAME is not null then MyTestProc( firstName => :P1_FIRSTNAME ); end if; end;
create or replace procedure MyTestProc( firstName varchar2 ) is cnt integer; begin htp.prn( 'You have entered the search criteria: '||firstName||'<br> '); select count(*) into cnt from emp where first_name like firstName; htp.prn( 'Number of matching employees: '||to_char(cnt)||' row(s)<br> '); end;