Thursday 7 August 2008

Dynamic Tabular Form

It can happen that you get a requiremen similar to this one:

http://forums.oracle.com/forums/thread.jspa?threadID=691046


1. You have an unknow number of tables

2. You don't know the structure of the tables in advance

3. You still need forms for entering and maintaining data

The only solution that comes to my mind are dynamically created tabular forms. Tabular forms have already a bad reputation. Now, if you add the word "dynamical" to it, it will sound even more horrorful.

As I promised in the mentioned thread, I tried to create a working example. This is what I came up with:

http://apex.oracle.com/pls/otn/f?p=31517:195

It took me arround three hours to get that code together. Of course, this can be done much better - using a package and providing some additional functionalities like validations and other item types.



13 comments:

Patrick Wolf said...

Hi Denes,

just a security hint about your code.

The code isn't really SQL injection save, you should protect your access to :p195_tables with the DBMS_Assert.enquote_name. Have a look at Carstens blog. This will prevent SQL injections in your processes.

Greetings
Patrick

Denes Kubicek said...

Patrick,

Thanks. This wasn't my focus.

Just out of curiosity. Could you show how you would inject some SQL there?

Denes

Patrick Wolf said...

SQL injections are most time not the intention of the developer :-)

But because probably a lot of developers are looking/using your code I wanted to bring it to your attention.

About the instructions, the easiest reproducible steps are for the delete process:
1) select eg. employees to populate the tabular form
2) Use Firebug/whatever to change the value of P195_TABLES to

SOME_VALID_TABLENAME; --

3) Select the row selector checkbox for one of the records
4) Press the delete button

The -- at the end of the above value will comment out the rest of you code where you add the where clause, ... and will do a full delete on any table I specify.

Note, I haven't tried it out because I didn't want to make you mad :-)

Patrick

Stew said...

Denes,

Did you notice this line in one of the forum from the authors' replies?

it is also a final project from university and we want
a solution that is implemented and it works in
the best possible way.


Maybe you're cooler than me (that's almost guaranteed!) but should we really be helping these guys with their final exam? Personally I don't appreciate it when students pose questions in these forums instead of learning on their own.

Stew

Denes Kubicek said...

O.K. this was an issue. Thanks for pointing that out. I modified the code a bit and now it is secure.

Denes

amd_group@iiui said...

i m new to apex, please give me step by step guide how to implement this dynamic table. please contact me on adeelmunawar80@hotmail.com

adeel munawar

Rory said...

Hi Denes,
This is a great little application!

I've looked at the code and it looks like this should still work in the latest version of APEX4.1... Do you agree?

Given the added features of APEX 4.1 would you suggest there is a better way of implementing a dynamic tabular form or updateable datagrid?

Thanks,
Will

Anonymous said...

How to create a tabular form using PLSQL???

There is no option for PLSQL...

Unknown said...

Hi Denes,

Is creating dynamic tabular form still this cumbersome or do we have any simpler features now available in APEX latest version?

If not, can you please tell me which region type exactly to select because in the tabular form, it is not allowing to enter the PL/SQL code returning a query.

Regards,
/Garv

Garv said...

Hi Denes,

Is creating dynamic tabular form still this cumbersome or do we have any simpler features now available in APEX latest version?

If not, can you please tell me which region type exactly to select because in the tabular form, it is not allowing to enter the PL/SQL code returning a query.

Regards,
/Garv

Denes Kubicek said...

There are no changes in Tabular Forms in APEX 5.0. Tabular Forms should be replaced with a more modern grid in 5.1. There is no way to use Function returning Query for tabular forms. Sure, you can do that using normal reports and apex_item package but then you loose the built in tabular form features.

Denes Kubicek

Garv said...

Thanks Denes.

Then how do I use the code you have mentioned above (http://apex.oracle.com/pls/otn/f?p=31517:195) to generate dynamic tabular form. I mean which type of region(form, report, html) exactly to create to use this code as a PL/SQL source?

Regards,
Garv

Denes Kubicek said...

It says "SQL Query / PL/SQL Function Returning SQL Query".