This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Apr 3, 2012 5:32 AM by lunicon RSS

publication with updateable view

lunicon Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points