Skip to Main Content
Feature Request FR-3034
Product Area Developer Experience
Status CLOSED

1 Voters

APEX Session level temporary table

mike_kutz Public · Mar 15 2023

Idea Summary
A way to create an APEX Session level temporary table.

Use Case
- Alternative for APEX Collections.

- Uses actual column names ( `user_name`, `sal` ) instead of generic ones ( `C001`, `N001`, etc )

Preferred Solution (Optional)
(prefered solution would be to create & enhance the ability to create a RAS Session temporary table at the SQL prompt. `create xs temporary table ….`. But, that may be out of scope)

For RAS capable editions, a hidden column + a generic RAS Policy could be used.

`alter table apex_temp add ( “__session_id” varchar2(32) invisible default v('session_id') not null )`

For SE, this would have to be an update-able view on the hidden version of the above table.

utilities for cleanup will be required. queue for deletion of row will also be required.

This idea has been closed due to the lack of community activity during the period since it was submitted.

Comments

Comments

  • stew stryker OP 2.2 years ago

    Warning: Half-baked idea ahead

    I like that you're trying to solve the issue of the APEX Collections having generic column names, which are a pain to deal with IMHO. I wonder if there might be another way to make it simpler to deal with these using other APEX collections? I can't quite work this out, but maybe someone smarter could?

    I've seen that the Data Load Wizard's “Data Load Results” page includes a “Failed Records” report that references an APEX collection named ‘LOAD_CONTENT’, which contains the Data Load Definition's columns. 

    What if APEX provided a way that, when you initially load an APEX Collection, you could provide those column names to another APEX Collection? And then there were a way to reference those in subsequent queries/updates, etc.?

  • mike_kutz OP 2.2 years ago

    @stew stryker 

    Storing the column names (and data types) in another APEX collection only makes sense if the “temp table” can be different for every web session.  Alternatively, this "temp table" could be built as a Shared Component with the metadata stored in a real table under APEX_xxxxx 

    In a way, I'm already working on this idea.  But, since this is only one of many side projects, it won't be completed anytime soon.

  • stew stryker OP 2.2 years ago

    @mike_kutz You've obviously thought about this much more, and more recently than I have, since I don't have an immediate need to modify the two apps we've done using APEX Collections.

    I guess I misunderstood your request, as I thought you wanted a temp table that might have various column names, differing by session.

    Now it sounds like you want a fixed table, but the records are only available in a single session? Each user has their own workspace, and when their session's gone, so is their data?

    If that's the case, could you:

    1. Create a permanent table
    2. When a session starts (or whatever), load the records for that user session.
    3. Use Oracle's VPD (Virtual Private Database) functionality to limit the user's rows to those rows. I've used VPD in an APEX application before, and while the setup was tedious, it ensured the user only saw their records.
    4. Clean up when the user logs out, or create a scheduled process that deletes records when the session is no longer active.
  • mike_kutz OP 2.2 years ago

    @stew stryker 

    I'm further ahead of you than that.

    A yes. this “temp table” should act like a Global Temporary Table (GTT) but for APEX Web Session

    B I've already built a code generator that implements these “temp tables” as a VIEW on Collection. It also generates API code (Packages) that replicates the behavior of the Automatic DML (and code for IGs row process). This makes is extremely easy for APEX Developer.  As such, these “temp tables” are a stock tool that I've used for most of my applications.

    C Real Application Security (RAS) is VPD on steroids (see OP). Think of it as VPD 2.0

    APEX+RAS (external)+Social Sign-In === This is the way.

  • stew stryker OP 2.2 years ago

    @mike_kutz You're definitely miles ahead of me on this, congrats!

    DOH! I've used VPD in the distant past, and I glanced at RAS about 18 months ago. But I didn't dig in enough to even recognize the acronym when you used it in your OP! :-(

    Congrats on all you've achieved and good luck getting this as a built-in feature.

  • mike_kutz OP 2.2 years ago

    @stew stryker 

    If interested, I have a GitHub repository that goes through a lot of RAS capabilities.

    More importantly, it contains a list of ~11~ 12 bugs I've discovered along the way.

    (The last one I found while trying to build a RAS  Policy for APEX GTT)

  • joep.hendrix OP 2.2 years ago

    RAS is only available for enterprise license afaik.

  • mike_kutz OP 2.2 years ago

    @joep.hendrix 

    RAS is only available for enterprise license afaik.

    VPD & RAS are only available on EE and XE/FREE (excluding earlier versions of XE)

    But, there are ways to simulate Row Level Security (RLS) in SE without VPD/RAS.  With SE, you would use a table with a view that limits rows with sys_context() (and leaves out that column)

  • joep.hendrix OP 2.2 years ago

    I know Mike, that is currently our poorman's vpd solution!