This discussion is archived
12 Replies Latest reply: Jan 2, 2013 2:28 PM by 560577 RSS

Are Virtual Columns a good idea in APEX apps?

GiuseppeL Newbie
Currently Being Moderated
Hi all,

Searching for a way to get fields auto-populated (a QTY*PRICE for example) I discovered Virtual Columns.

Looks very good, but.
a) You can't create Virtual Columns under APEX.
b) You can't create Virtual Columns under SQL Developer.

You must do it with SQL directly. And if you do it.
a) You can't insert rows under Object Browser (APEX)
b) You can't modify anything in this table under SQL Developer

My surprise, is, thath as far as I see, Virtual columns exists since 2007, but seems APEX nor SQL Developer, with updates released some days, are'nt compatible with this "new" feature.

There are some reason for this? In Oracle World anybody uses computable fields?

Regards.
  • 1. Re: Are Virtual Columns a good idea in APEX apps?
    TexasApexDeveloper Guru
    Currently Being Moderated
    Why not use display only columns with a pl/sql process to populate them??

    Would seem to fulfill the requirement that you have ...

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 2. Re: Are Virtual Columns a good idea in APEX apps?
    GiuseppeL Newbie
    Currently Being Moderated
    I don't understand you, I'm new to oracle. Can you explain it, or point me to some doc? I can't find on APEX nor SQL DEV, "Display only" property.

    I know use procedures is possible, but, extremelly unnecesary IMHO.
    Imagine an "orders_detail" table for example, where each row has a field of TOTAL_LINE, and the unique calc you need is QUANTITY*PRICE.

    Regards.
  • 3. Re: Are Virtual Columns a good idea in APEX apps?
    TexasApexDeveloper Guru
    Currently Being Moderated
    Is this for a report or a form being displayed to users?

    if it is a report, then you can BUILD it in your query.. If it a form, then you can build form ITEMS that are Display ONLY..

    Please explain whet you are trying to do...

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 4. Re: Are Virtual Columns a good idea in APEX apps?
    GiuseppeL Newbie
    Currently Being Moderated
    For the moment I don't know. I'm building the Database model.

    Is an application I'm migrating from another enviroment.
  • 5. Re: Are Virtual Columns a good idea in APEX apps?
    TexasApexDeveloper Guru
    Currently Being Moderated
    Then most definitely you do NOT need these columns.. Building calculated columns into a data model is CRAZY... Build view off of the data with those items or build forms that DISPLAY those amounts.. Don't store them...

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 6. Re: Are Virtual Columns a good idea in APEX apps?
    GiuseppeL Newbie
    Currently Being Moderated
    For this reason I want Virtual columns, as far as I understood, is not stored.

    http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php
    "When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below."
  • 7. Re: Are Virtual Columns a good idea in APEX apps?
    TexasApexDeveloper Guru
    Currently Being Moderated
    BUT since APEX does NOT support their usage, it would seem to be a no-brainer.. DON'T USE what APEX DOESN'T SUPPORT.....

    Since you are designing the data model, tailor your model to what you need..

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 8. Re: Are Virtual Columns a good idea in APEX apps?
    GiuseppeL Newbie
    Currently Being Moderated
    Is a way to see it :)

    Thanks.

    But still thinking about why if virtual columns is not something new, why is not supported 5 years later.
  • 9. Re: Are Virtual Columns a good idea in APEX apps?
    TexasApexDeveloper Guru
    Currently Being Moderated
    Because they have been working on getting numerous OTHER features in APEX.. Again, as noted you can use Display only columns in your forms.. Why would you REALLY need these columns in your data tables? Please give me a an example, because I am missing the point so far..

    Also, why is it that APEX doesn't support REF Cursor's? They have been in Oracle for a few versions, Right?

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 10. Re: Are Virtual Columns a good idea in APEX apps?
    trent Expert
    Currently Being Moderated
    Interesting.

    I had never come across virtual columns until now.

    It seems like you can use them in apex

    Reports work fine, as you would expect
    DML processes work fine, so long as you dont include the virtual columns.

    Bit of a downer that you won't be able to insert data through the GUI of SQL Workshop, SQL Dev, but it takes like 2 mins to make a form page in apex to insert data - no biggie, i think, if you really want to use the feature.
  • 11. Re: Are Virtual Columns a good idea in APEX apps?
    GiuseppeL Newbie
    Currently Being Moderated
    TexasApexDeveloper wrote:
    Because they have been working on getting numerous OTHER features in APEX.. Again, as noted you can use Display only columns in your forms.. Why would you REALLY need these columns in your data tables? Please give me a an example, because I am missing the point so far..

    Also, why is it that APEX doesn't support REF Cursor's? They have been in Oracle for a few versions, Right?

    Thank you,

    Tony Miller
    Ruckersville, VA
    Well, about REF Cursor's I don't know, I'm new to Oracle world :P

    I will avoid VColumns for the moment.

    Thanks for your support.
  • 12. Re: Are Virtual Columns a good idea in APEX apps?
    560577 Pro
    Currently Being Moderated
    I would say "no" to using virtual columns in the database when Apex is laid over them as the front-end app. It just doesn't like them.

    If your page in question is about page items, I would do the following:

    1. Create a pl/sql function or packaged function in the database to do your computation(s).
    2. Assuming you're in version 4.x, create a dynamic action that populates that item by calling the PL/SQL above and returning it to the item.
    3. Turn on (most likely) the "fire on page load" option for that dynamic action task.

    If it's a tabular form (or the detail region of a master-detail form), oh my this part sucks. I could not find any way around this that wasn't ugly (and even so my workaround is a hack that I don't like) mostly because tabular forms have fewer options for HTML items than page HTML items do and I had to define my computed items as database columns just to support the tabular form, but read on...

    1. Make your computed item in the table in question as a column.
    2. Make it nullable.
    3. Place a check constraint that this column that it must always be null (ugly, but works...e.g.: MYCOL IS NULL)
    4. Place a comment on the column as to why you did this so that future developers that see your work know that you had a reason, however bizarre that reason may be (e.g.: '** Do not use ** This is for an Apex workaround. Do not store any values in this column; always derive them.')
    5. Build your tabular form and make the computed column readOnly via HTML Element Attributes readOnly="true")
    6. Same as #1 above, write a PL/SQL function or packaged function.
    7. Make an app on-demand process and javascript to run AJAX to get the value for each row and to set the value.
    8. Call the JS and AJAX for every row after the region paints (usually in a DA that is after-refresh of the tabular form region and it calls JS to call your AJAX to get the values for the computed field on every row).
    9. Call the JS and AJAX for a single row when onChange fires on another item in the row (presumably your computed value is based on one or more other values on the row in question).
    10. Before submit of your tabular form, clear all values in this computed field on all rows using Javascript.

    Why did I do it all that way? Because that was the only way I could find to effectively NOT store a computed value in the DB but also that Apex would generate for me the HTML IDs (e.g.: f06_0001, f06_0002, etc.) for the computed fields in the tabular form (without having to do a fully manual tabular form) so that I could manipulate the fields real-time w/o the user submitting the page, all while not giving me errors about checksums or virtual columns.