1 2 Previous Next 17 Replies Latest reply: Apr 3, 2012 7:32 AM by lunicon RSS

    publication with updateable view

    lunicon
      I create publication item for updateable view (using triggers) in my database
      but when i trying to add it to publication (addPublicationItem) it failes with error
      CONS-10033: Parent table DBO_SOGINSFORORA.FACEJURIDICAL not published for DBO_SOGINSFORORA.V_B2B_JURIDICALFACES view
      I really need to add a publication items of the tables in the publication on which the view based on?
      may have another way?
        • 1. Re: publication with updateable view
          lunicon
          For example for this view with empty (but valid) triger
          CREATE OR REPLACE VIEW V_B2B_ADDRESS AS
          SELECT AD.AddressID, AD.Address,
               (SELECT ADT.Brief FROM AdressType ADT WHERE ADT.AdressTypeID = AD.AdressTypeID) AddressType,
                    AD.AdressTypeID
          FROM Address AD;
               
          CREATE OR REPLACE TRIGGER IOT_B2B_ADDRESS
          INSTEAD OF DELETE OR INSERT OR UPDATE
          ON DBO_SOGINSFORORA.V_B2B_ADDRESS
          REFERENCING NEW AS New OLD AS Old
          FOR EACH ROW
          BEGIN
          IF INSERTING
          THEN
          NULL;
          ELSIF UPDATING
          THEN
          NULL;
          ELSIF DELETING
          THEN
          NULL;
          END IF;
          EXCEPTION
          WHEN OTHERS
          THEN
          RAISE;
          END;
          I call parentHint as in documentation
          cm.parentHint(Consolidator.MAIN_DB, OWNER, "V_B2B_ADDRESS", OWNER, "ADDRESS");
          cm.dependencyHint(Consolidator.MAIN_DB, OWNER, "V_B2B_ADDRESS", OWNER, "ADDRESSTYPE");

          but function
          cm.createPublicationItem("PI_V_B2B_ADDRESS",
                    OWNER, "V_B2B_ADDRESS",
                    "F", SELECT, null, null);

          still cause an exception:
          CONS-10049: Consolidator Exception: ORA-25001: cannot create this trigger type on views

          somebody can explain what I'm doing wrong?

          Edited by: lunicon on 23.11.2011 4:44

          Edited by: lunicon on 24.11.2011 5:38
          • 2. Re: publication with updateable view
            vasileios
            you have not received any answers yet from an experienced member so ill try helping out although im not familiar with the publishing views.
            my personal opinion is that you shoudlnt use views unless you are abolutely constrainted to.

            anyway concerning your question on "I really need to add a publication items of the tables in the publication on which the view based on?"
            i think that the documendation is pretty clear

            http://docs.oracle.com/cd/E12095_01/doc.10303/e12090/sync.htm#BCGIHBGB
            2.4.1.3.1 Defining Publication Items for Updatable Multi-Table Views

            Publication items can be defined for both tables and views. When publishing updatable multi-table views, the following restrictions apply:

            *The view must contain a parent table with a primary key defined.
            *INSTEAD OF triggers must be defined for data manipulation language (DML) operations on the view. See Section 2.9, "Understanding Your Refresh Options" for more information.
            *All base tables of the view must be published.

            So yes all base tables of the view meaning all the tables that the view is depends on must be published. this is for updatable multi table views which i see is
            your case ("I create publication item for updateable view")

            concerning the apis, may i ask did you try the workbench?i honestly dont understand why people use the apis to create publication and pub items/sequences
            when the workbench does the job for you, next next next click clik
            perhaps write a big sql next next next click finish.

            btw in 3.3 Create a Synonym for Remote Database Link Support For a Publication Item it says
            If the remote object is a view that needs to be published in updatable mode and/or fast-refresh mode, the remote parent table must also be published locally

            so give it a try with the workbench, publish the base tables of the view and then publish the view and see what happens. or wait for a more experienced (in handling views) member of this forum
            answers

            thks
            • 3. Re: publication with updateable view
              lunicon
              Thank you for trying to help. Answer some of your questions, some of you did not ask:
              Why are you using view?
              There is a great workflow system. My job is to make it available to mobile customers without refactoring if it possible.
              Why people use the apis to create publication?
              I already have database model in xml form, so I'm trying to automate the process of publication. And even useful for the future :)
              When the workbench does the job for you, next next next click clik...
              Workbench is good only if you have multiple tables, but if you have several hundred it is not very convenient.
              Try to choose the related table hint in the unsorted combobox list. There are no search by name/mask :(
              I certainly have tried to use workbench, but got the same errors.

              The mobile database sync engine creates the same view base on the same tables, isn't it?

              for every view I follow this algorithm:
              1. create publication item for every based table
              2. add them to publication
              3. add parentHint for base table
              4. add dependencyHint for other tables (multi table view)
              5. create publication item for view
              6. add this item to publication

              step 5 fail for some views (even simple) with error ORA-25001: cannot create this trigger type on views
              • 4. Re: publication with updateable view
                vasileios
                sounds interesting what is diffrent between the views that are published and those not? you do know that since this is an ora error you can put traces on
                the oracle 11g with something like
                to enable tracing without restarting database run the following command in sqlplus

                SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;
                to stop trace run:
                SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;


                and then view the trace files to see what oracle lite is trying to execute on the oracle 11g database and throws the ora error. if the query is wrong
                then perhaps you need to check for a patch and/or open a service request.

                you can try also running in the sql developer window the
                select * from V$SQLAREA order by first_load_time desc

                to get the last queries executed against the database
                immediately after workbench gets the exception and perhaps you will see the create view query there also

                if you find it copy paste the query here and lets see whats wrong with the create view query that olite runs that throws the ora error. then perhaps we can understand whats missing
                • 5. Re: publication with updateable view
                  lunicon
                  well, let's consider a simple example
                  -----
                  test table and view:
                  -----
                  create table TEST.TESTDATA
                  (
                  DATAID NUMBER(10) not null,
                  ENTITYID NUMBER(10) not null, -- I deliberately made an unused/not_null field
                  CODE VARCHAR2(4 CHAR),
                  NAME VARCHAR2(100 CHAR),
                  FULLNAME VARCHAR2(100 CHAR)
                  );

                  alter table TEST.TESTDATA add constraint PK_TESTDATA primary key (DATAID);
                  grant select, insert, update, delete, references on TESTDATA to PUBLIC;

                  -- note ENTITYID not used
                  create view TEST.V_TESTDATA as Select D.DataID, D.Code, D.Name, D.FullName From TEST.TESTDATA D;
                  -----
                  code sample:
                  -----
                  Consolidator.PubItemProps cp = new Consolidator.PubItemProps();
                  String STORE_NAME = "TESTDATA";
                  String PUB_ITEM = "PI_" + STORE_NAME;

                  cp.owner = OWNER;
                  cp.store = STORE_NAME;
                  cp.refresh_mode = "F";
                  cp.select_stmt = "SELECT * FROM " + OWNER + "." + STORE_NAME;
                  cp.cbk_name = null;
                  cp.cbk_owner = null;
                  cp.isLogBased = false;

                  cm.createPublicationItem(PUB_ITEM, cp);
                  cm.setPubItemColOption(PUB_ITEM, "ENTITYID", Consolidator.NOT_NULL, "0", true);
                  cm.createPublicationItemIndex(PUB_ITEM + "-I1", PUB_ITEM, "P", "DATAID");
                  cm.addPublicationItem(PUBLICATION, PUB_ITEM, null, null, "S", null, null);

                  STORE_NAME = "V_TESTDATA";
                  PUB_ITEM = "PI_" + STORE_NAME;

                  cp.owner = OWNER;
                  cp.store = STORE_NAME;
                  cp.refresh_mode = "F";
                  cp.select_stmt = "SELECT * FROM " + OWNER + "." + STORE_NAME;
                  cp.cbk_name = null;
                  cp.cbk_owner = null;
                  cp.isLogBased = false;

                  cm.parentHint(Consolidator.MAIN_DB, OWNER, "V_TESTDATA", OWNER, "TESTDATA");

                  cm.createPublicationItem(PUB_ITEM, cp);
                  cm.addPublicationItem(PUBLICATION, PUB_ITEM, null, null, "S", null, null);
                  -----
                  last one cause an exception: oracle.lite.sync.ConsolidatorException:
                  CONS-10090: Parent hint is not complete for base table DBO_SOGINSFORORA.TESTDATA
                  can somebody explain why?
                  • 6. Re: publication with updateable view
                    vasileios
                    i apologize but i tried your example using sql developer and mdw and i didnt get any error.

                    in details
                    i use olite 10gr3(10.3.0.3) with oracle db 11g on win server 2003 os with ALL the latest patches releashed.

                    i used a schema i already had (created that through sql developer and i gave all priviledges) i opened sql developer created the table using the create table script, then i right click the tables properties created the primary key,
                    then i created the view through sql developer create view.
                    then i opened mdw a preexisting project create pub item and added the table as create new publication item.
                    then i went on and created new publication item for the view, (here i selected the first column as an index and i selected the primary base table dataid as parent hint to the view dataid)
                    and finished. then i added the two pub items in the publication of the preexisting project (as i mentioned) and i didnt get any errors

                    then i went to the webtogo page to the data synchronization->repository->publication->publication items->

                    and i saw my publication having the two new pub items and everything going with no problem.

                    ok i didnt download the project to my handheld ( i use olite for ppc60) and i didnt use the packaging wizard to move on with the publication but i guess that since mdw and webtogo didnt show any error then theres nothing wrong. did you try the example with mdw ?

                    i had in the past cases where i tried to use java apis and for some strange reasons i got errors (i used them to execute commands on the clients)

                    Edited by: vasileios on Nov 25, 2011 3:16 AM
                    • 7. Re: publication with updateable view
                      lunicon
                      i forgot, its updateable (multi-table) view add empty trigger (for this example only, realization will be written later)

                      CREATE OR REPLACE TRIGGER IOT_V_TESTDATA
                      INSTEAD OF DELETE OR INSERT OR UPDATE
                      ON TEST.V_TESTDATA
                      REFERENCING NEW AS NEW OLD AS OLD
                      FOR EACH ROW
                      BEGIN
                      IF INSERTING
                      THEN
                      NULL;
                      ELSIF UPDATING
                      THEN
                      NULL;
                      ELSIF DELETING
                      THEN
                      NULL;
                      END IF;
                      EXCEPTION
                      WHEN OTHERS
                      THEN
                      RAISE;
                      END;
                      -----
                      I tried to repeat the mistake in this simple example, but with
                      cp.select_stmt = null; and
                      cm.primaryKeyHint(PUB_ITEM, "DATAID", OWNER, "TESTDATA", "DATAID");
                      before cm.addPublicationItem it works.

                      my config:
                      Oracle Mobile Server 11g1
                      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                      i' ll try to make database logs..
                      • 8. Re: publication with updateable view
                        lunicon
                        So, i create database traces using thi two lines:
                        ALTER SESSION SET tracefile_identifier = 'MobileServer'
                        ALTER SESSION SET events '10046 trace name context forever, level 1'
                        And i figured out what createPublicationItem trying to execute this statement:
                        CREATE OR REPLACE TRIGGER "TEST"."V_B2B_ADDRESS_TYPECTR"
                        AFTER INSERT OR UPDATE OR DELETE ON "TEST"."V_B2B_ADDRESS_TYPE"
                        FOR EACH ROW
                        DECLARE
                        v_SID NUMBER;
                        v_TMP CHAR(1);
                        v_CLID VARCHAR2(60);
                        BEGIN
                        v_SID := MOBILEADMIN.C$UPDATE_PACKAGE.SessionSeq;
                        v_CLID := MOBILEADMIN.CONS_EXT.GET_CURR_CLIENT;
                        BEGIN
                        SELECT DIRTY
                        INTO v_TMP
                        FROM "MOBILEADMIN".C$ALL_SID_LOGGED_TABLES
                        WHERE OWNER = 'TEST'
                        AND STORE = 'V_B2B_ADDRESS_TYPE'
                        AND SID = v_SID
                        FOR UPDATE;
                        IF NVL(v_TMP, 'N') <> 'Y' THEN
                        UPDATE "MOBILEADMIN".C$ALL_SID_LOGGED_TABLES
                        SET DIRTY = 'Y'
                        WHERE OWNER = 'TEST'
                        AND STORE = 'V_B2B_ADDRESS_TYPE'
                        AND SID = v_SID;
                        END IF;
                        EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                        INSERT INTO "MOBILEADMIN".C$ALL_SID_LOGGED_TABLES
                        (OWNER, STORE, DIRTY, SID)
                        VALUES
                        ('TEST', 'V_B2B_ADDRESS_TYPE', 'Y', v_SID);
                        END;
                        IF INSERTING THEN
                        UPDATE "TEST"."CVR$V_B2B_ADDRESS_TYPE"
                        SET VERSION$$ = VERSION$$ + 1, DMLTYPE$$ = 'I', CLID$$OWN = v_CLID
                        WHERE "ADRESSTYPEID" = :new."ADRESSTYPEID"
                        AND DMLTYPE$$ = 'D';
                        END IF;
                        IF UPDATING THEN
                        IF NOT (:new."ADRESSTYPEID" = :old."ADRESSTYPEID") THEN
                        UPDATE "TEST"."CVR$V_B2B_ADDRESS_TYPE"
                        SET DMLTYPE$$ = 'D', CLID$$OWN = v_CLID
                        WHERE "ADRESSTYPEID" = :old."ADRESSTYPEID";
                        END IF;
                        UPDATE "TEST"."CVR$V_B2B_ADDRESS_TYPE"
                        SET VERSION$$ = VERSION$$ + 1, DMLTYPE$$ = 'I', CLID$$OWN = v_CLID
                        WHERE "ADRESSTYPEID" = :new."ADRESSTYPEID";
                        END IF;
                        IF SQL%NOTFOUND AND NOT DELETING THEN
                        INSERT INTO "TEST"."CVR$V_B2B_ADDRESS_TYPE"
                        ("ADRESSTYPEID", VERSION$$, DMLTYPE$$, CLID$$OWN)
                        VALUES
                        (:new."ADRESSTYPEID", 1, 'I', v_CLID);
                        END IF;
                        IF DELETING THEN
                        UPDATE "TEST"."CVR$V_B2B_ADDRESS_TYPE"
                        SET VERSION$$ = VERSION$$ + 1, DMLTYPE$$ = 'D', CLID$$OWN = v_CLID
                        WHERE "ADRESSTYPEID" = :old."ADRESSTYPEID";
                        END IF;
                        END;
                        As you can see it call CREATE TRIGGER AFTER INSERT
                        I thought that only INSTEAD OF TRIGGER can be created for view.

                        apparently this is a reason for error
                        CONS-10049: Consolidator Exception: ORA-25001: cannot create this trigger type on views
                        • 9. Re: publication with updateable view
                          gwilkinson
                          Sorry, not been on the forum for a while

                          It is perfectly fine to base publication items on updateable views - we have quite a number. Depending on the client platform i would recommend them in many cases purely on a client performance basis.

                          Few questions
                          1) what type of publication items are you creating? from the posting i guess probably fast refresh
                          2) is the base view in the same schema as the database tables it relates to?
                          3) how are you doing the publish (MDW, XML, Java)?
                          4) what version are you on, and what platform are the clients
                          5) if fast refresh have you done any customisation to the MGP prrocessing?
                          • 10. Re: publication with updateable view
                            lunicon
                            1) fast refresh for now. there are few tables that do complete refresh
                            2) yes, same schema
                            3) prefer java consolidator api (as many tables and want to automate at least some)
                            4) mobile server 11g, sqlite win32 (android in future)
                            5) now I just try to. no subsetting

                            yet I did not get a publication item for updatable view
                            (createPublicationItem cause an exception ORA-25001, but it appears in the database)
                            i create publication items for referenced tables, parent hint, parent key hint
                            see second post, need to try the previous version olite 10?
                            • 11. Re: publication with updateable view
                              lunicon
                              So, it turned out that this is a bug in the Mobile Server 11g.
                              It works in Olite 10g.
                              • 12. Re: publication with updateable view
                                gwilkinson
                                must be trying to create the fast refresh triggers.

                                sure you are aware of this, but if you have a fast view based PI, you will also need to include in the publication PIs for all of the view base tables or use a mycompose class otherwise changes may not be recognised by the MGP process as there will be no logging of changes. To save processing time we used dummy PIs wth selections of 1=2.

                                not using 11, but in 10 at least there are 3 possible ways to publish, via the mobile manager publication with uses a xml file inside a dummy jar file, via MDK and via the APIs. The XML method has tags to specifically state the base object type is a view rather than a table (not part of the API methods), so this may be a workaround, but needs to be either the whole application, or run first and then use of APIs to add to it.

                                If you have not yet done this, look at the queue based PIs, much better in my opinion as you can make them much more efficient, real time and get rid of the MGP overhead as well as better control
                                • 13. Re: publication with updateable view
                                  lunicon
                                  I want to clarify a bit, for example:
                                  create table T1 (
                                  ID NUMBER(10) not null PRIMARY KEY,
                                  FID NUMBER(10) not null,
                                  DATA VARCHAR2(100 CHAR));

                                  create table T2 (
                                  ID NUMBER(10) not null PRIMARY KEY,
                                  FDATA VARCHAR2(100 CHAR));

                                  CREATE VIEW V1 AS
                                  Select t.ID, t.DATA, f.FDATA From T1 t, T2 f WHERE t.FID = f.ID;
                                  as you suggested, for updatable view (V1) based on Tables (T1, T2) i do
                                  createPublicationItem(PI_T1, select_stmt="select * from T1 where 1=2", refresh_mode = "C")
                                  createPublicationItem(PI_T2, select_stmt="select * from T2 where 1=2", refresh_mode = "C")
                                  because these tables are not explicitly used in my application.
                                  must use the complete or fast refresh? no need add them in publication, is not it?

                                  then create publication item for view:
                                  createPublicationItem(PI_V1, select_stmt="select * from V1", refresh_mode = "F")
                                  make hint:
                                  cm.parentHint(Consolidator.MAIN_DB, OWNER, "V1", OWNER, "T1");
                                  cm.dependencyHint(Consolidator.MAIN_DB, OWNER, "V1", OWNER, "T2"); need it?
                                  cm.primaryKeyHint(PI_V1, "ID", OWNER, "T1", "ID");
                                  and finally add view item to publication:
                                  cm.addPublicationItem(PUBLICATION, PI_V1, null, null, "S", null, null);
                                  this is the correct sequence?
                                  • 14. Re: publication with updateable view
                                    lunicon
                                    I found a bug:
                                    If you create 'virtual' disabled primary key (need to trick the old unsupported software) like:
                                    ALTER VIEW "TEST"."V_B2B_ADDRESS" ADD CONSTRAINT "V_B2B_ADDRESS_PK" PRIMARY KEY ("ADDRESSID") DISABLE;

                                    and if you try to publish such a view will get an error:
                                    CONS-10049: Consolidator Exception: ORA-25001: cannot create this trigger type on views

                                    I drop it and never saw such an error
                                    1 2 Previous Next