Process Interactive Grid with PL/SQL only - without a table

Most Application Express developers already have played with the Interactive Grid component introduced in the latest version 5.1. And indeed - Interactive Grid makes it more than easy to get an editable tabular form on a table or view. In Page Designer, just drag the component onto your page, add the SQL query, make it editable in the Attributes section ... and you're done.

Interactive Grid for the EMP table

But with Interactive Grid you can do even more. In practice there is sometimes the requirement for a tabular form - but there is no table. End users just enter data from scratch - and that is to be processed with custom PL/SQL code. The editing capabilities of interactive grid would come in handy here, but ...

• it seems, we don't have a SQL query for the data to be displayed. There is no table or view to select data from; we just want to display a few empty rows or rows with some sample or template data.
• for the same reason there is no table or view to execute DML statements (INSERT, UPDATE or DELETE) on. So it seems that interactive Grid will not be able to process the end user inputs.

So at the first glance it seems that Interactive Grid cannot be used here. But that's wrong: We will be able to use Interactive Grid - we just have to change a few attributes, and use SQL and PL/SQL properly. Let's first have a look at the SQL query to be used as the region source. There is no table to select from - so we just generate rows ...

select
     level as id,
     level as grid_row,
     'First Name ' || level as firstname,
     'Last name  ' || level as lastname,
     sysdate as datum,
     cast(null as varchar2(4000)) as text 
from dual connect by level <= 3

Use that SQL query in order to create your interactive grid. Then navigate to the individual columns and change their settings as follows.

• Change the column ID to Hidden and declare it as Primary Key.
• Change the GRID_ROW column to Display Only.
• For the TEXT column, use a text area and make sure that the maximum length is set to 4.000 (and not to zero (0)).

Then make the interactive grid editable and allow the INSERT and UPDATE operations. DELETE does not make any sense in this context.

Interactive Grid gets ediable: INSERT and UPDATE are allowed

Now run your page. The preliminary result should look as follows.

An interactive grid without a table - just with some generated data

Of course, you can further configure your interactive grid - just as you need it. But in this example, we'll now focus on the processing the end user inputs. As stated above, we just want to execute PL/SQL code - there is no connection to any table or view at all. PL/SQL code can do anything - there are no limits.

Let's simulate some fancy PL/SQL processing: The end user inputs will be converted to some comma-separated format, stored in a text area item (PX_RESULT) and simply displayed. Thus we'll be able to verify, that everything works as we expect it.

Create another Static Content region on your page (below the interactive grid). Add a text area item named PX_RESULT and a button to submit the page to that region.

The results of processing end user inputs will appear in the "Result" region

Then, in Page Designer, navigate to the Processing section, then to the Save Interactive Grid Data process and change it as follows:

• Change the Target Type to PL/SQL Code; since we want to have PL/SQL code executed.
• Turn Lock Row off (set to No).
• Also turn Prevent Lost Updates off; we don't need this.
• Finally store the following PL/SQL code:

  begin
      :PX_RESULT := :PX_RESULT || :ID || ',"' || 
                                  replace( :VORNAME, '"', '\"' ) || '",' ||
                                  replace( :NACHNAME, '"', '\"' ) || '",' ||
                                  '"' || :DATUM || '","' ||
                                  replace( :TEXT, '"', '\"' ) || '"' || chr(10);
  end;
  

New Settings for the "Save Interactive Grid Data" process

This Save Interactive Grid Data process will be executed once for each changed or new interactive grid row, appending a line with comma-separated values to the PX_RESULT content. Thus we want to clear that element at the very beginning of processing. So create another process which is being executed before the Save Interactive Grid Data process. That process is just supposed to clear the PX_RESULT page item.

Clear page item PX_RESULT at the beginning of page processing

Finally turn off the Save button which has been automatically generated by interactive grid. We don't need that button here, because we want to submit the page when the end user has finished entering data. As an alternative you also might to disable the toolbar completely - for such a form for data entry from scratch we might not need it at all.

Disable the "Save" button or even the whole tool bar

Now it's time to test what you have build. Run the page, enter data for a few rows and click the Submit Page button. The result should look as follows.

Interactive Grid: enter some data from scratch

After Page Submit the data has been processed with PL/SQL

From here, you can do almost anything: On the processing side, you are within the PL/SQL domain: You might process the data and finally store it some tables, call web services or do whatever you need. Thus interactive grid is much more than just a tabular form on a table; your application can do anything with the end user inputs.

And, of course, you can also apply any client-side customization to the interactive grid. Use item type plug-ins or custom javascript code to change interactive grid behavior as you need it. On John Snyders' Blog you'll find several ideas, concepts and good advice. Have fun trying these things out!

back to blogs.oracle.com/apex