4 Replies Latest reply on Apr 25, 2014 2:57 PM by Mike Kutz

    APEX table (instead of view)




      How is it possible to make a table instead of a view with the APPS user (Toad) for apex.


      I have the following code



      Create table apex_ebs_TIMETABLE



      , address1

      , address2

      , postal_code

      , city

      , country

      , cma.monday_first_start

      , monday_first_end

      , monday_second_start

      , monday_second_end

      , tuesday_first_start

      , tuesday_first_end

      , tuesday_second_start

      , tuesday_second_end

      , wednesday_first_start

      , wednesday_first_end

      , wednesday_second_start

      , wednesday_second_end

      , thursday_first_start

      , thursday_first_end

      , thursday_second_start

      , thursday_second_end

      , friday_first_start

      , friday_first_end

      , friday_second_start

      , friday_second_end

      , saturday_first_start

      , saturday_first_end

      , saturday_second_start

      , saturday_second_end

      , sunday_first_start

      , sunday_first_end

      , sunday_second_start

      , sunday_second_end







      select cii.instance_number

      , hl.address1

      , hl.address2

      , hl.postal_code

      , hl.city

      , hl.country

      , cma.monday_first_start

      , cma.monday_first_end

      , cma.monday_second_start

      , cma.monday_second_end

      , cma.tuesday_first_start

      , cma.tuesday_first_end

      , cma.tuesday_second_start

      , cma.tuesday_second_end

      , cma.wednesday_first_start

      , cma.wednesday_first_end

      , cma.wednesday_second_start

      , cma.wednesday_second_end

      , cma.thursday_first_start

      , cma.thursday_first_end

      , cma.thursday_second_start

      , cma.thursday_second_end

      , cma.friday_first_start

      , cma.friday_first_end

      , cma.friday_second_start

      , cma.friday_second_end

      , cma.saturday_first_start

      , cma.saturday_first_end

      , cma.saturday_second_start

      , cma.saturday_second_end

      , cma.sunday_first_start

      , cma.sunday_first_end

      , cma.sunday_second_start

      , cma.sunday_second_end

      from   csi_item_instances cii


      , hz_party_sites hps

      , hz_locations hl

      where 1=1



      and cii.location_id = cma.customer_site_id

      and cii.location_id = hps.party_site_id

      and hps.location_id = hl.location_id





      create table APEX_EBS_TIMETABLE

      as select * from apps.APEX_EBS_TIMETABLE


      I have tried to give APEX the permissions but wih no success


      (I want to load this table in APEX so that I can change it with APEX and that it will insert the new changes directly into the Oracle table.


      Has somebody a solution for this?




        • 1. Re: APEX table (instead of view)
          Mike Kutz

          Your requirements are unclear.

          From what I can gather:

          You have a table in one schema. (schema 1)

          You want to copy the data into another schema (schema 2)

          And allow APEX to manipulate the data in the table of schema 2.

          You have Toad.


          I see this is a one-time task.

          I'd copy the data from schema 1 into schema 2 using Toad.(well, actually, SQL*Developer.. but, that is the tool I use.)

          If you are using the "Automatic DML", the workspace needs to be linked to schema 2

          If schema 2 is not the "parsing schema", then the parsing schema will need to have the appropriate permissions.


          remember, this is a one-way thing.

          Anything you do in Schema 2 will not be reflected in schema 1.



          • 2. Re: APEX table (instead of view)

            Hi Mike,


            Sorry that it was unclear.


            This is the situation now: I have some Tables in Oracle Ebs:

            csi_item_instances cii

            , CSF_MAP_ACCESS_HOURS_B cma

            , hz_party_sites hps

            , hz_locations hl


            now I want that those tables are directly show the data as a view plus that the data can be changed in APEX and directly is changed in the Oracle EBS tables.


            So when I change something in APEX I want to see this in my Oracle EBS.


            Is this possible?



            • 3. Re: APEX table (instead of view)

              Hi Elco,


              Had used Bi-directional Oracle Streams for your requirements,

              any updates to table/s will be reflected on mulitple schemas.

              Please check out the Streams forum below:



              Oracle is recommending users to use Goldengate instead as no more development

              will be done to Streams.


              The above setup when implemented will be transparent to Apex users,

              guess it's not really Apex related.




              • 4. Re: APEX table (instead of view)
                Mike Kutz

                Read this fat manual:

                Managing Views - Updating a Join View

                With restrictions, views can be used to update/insert/delete the underlying data table(s).

                And, if your view doesn't fit into that category, you could always write an INSTEAD OF trigger.