Process Interactive Grid with PL/SQL only - without a table
|| APEX version
|| Database version
||5.1 or higher
||11.2 or higher
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
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 ...
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:
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
John Snyders' Blog
you'll find several ideas, concepts and good advice. Have fun trying these things out!
back to blogs.oracle.com/apex