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.



on

13 comments:

  1. Patrick Wolf7 August 2008 at 21:26

    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

    ReplyDelete
  2. Denes Kubicek7 August 2008 at 21:44

    Patrick,

    Thanks. This wasn't my focus.

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

    Denes

    ReplyDelete
  3. Patrick Wolf7 August 2008 at 22:10

    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

    ReplyDelete
  4. Stew7 August 2008 at 23:21

    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

    ReplyDelete
  5. Denes Kubicek7 August 2008 at 23:29

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

    Denes

    ReplyDelete
  6. amd_group@iiui26 May 2011 at 08:58

    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

    ReplyDelete
  7. Rory9 March 2012 at 17:33

    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

    ReplyDelete
  8. Anonymous22 June 2015 at 13:53

    How to create a tabular form using PLSQL???

    There is no option for PLSQL...

    ReplyDelete
  9. Unknown19 October 2015 at 17:31

    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

    ReplyDelete
  10. Garv19 October 2015 at 17:32

    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

    ReplyDelete
  11. Denes Kubicek19 October 2015 at 17:37

    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

    ReplyDelete
  12. Garv19 October 2015 at 17:54

    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

    ReplyDelete
  13. Denes Kubicek20 October 2015 at 09:51

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

    ReplyDelete