Using Jailer: Extraction of oracle db subset.

2010-06-17
2012-12-07
  • Vijay Ingole

    Vijay Ingole - 2010-06-17

    Looking for a solution in Jailer 3.4.3 that could pull out a valid subset of records from Oracle schema based on certain filtering criteria. By the term valid, I mean that it should maintain the Primary-foreign key relationship among the tables.
    While trying to do that I was getting errors for Primary key not found. Created Primary key to all tables in the schema, proceeded to some extent. It again comes up with errors while exporting the subset data.
    Error is:  ORA-00904: "A"."ID_WF_ACTION": invalid identifier.
    This error says that the column name entered is either missing or invalid. This is actually true. This column do not exist, in fact the table only does not exist. Unable to figure out as where is it taking the reference of this table from.
    Can someone please help on this?

     
  • Ralf Wisser

    Ralf Wisser - 2010-06-17

    Hi,

    "A" is an alias for the source table in the SQL statements for collecting associated rows. Please take a look into sql.log file, you should find the incorrect statement there. Post it here. Btw, the current release 3.4.5 offers better error logging.

    HTH,
    Ralf

     
  • Vijay Ingole

    Vijay Ingole - 2010-06-17

    Thanks for your reply.

    The sql.log shows the below qry.

    Insert into JAILER_ENTITY_T (r_entitygraph, PK1, birthday, type) Select distinct 1170796854 as GRAPH_ID, B.ID AS PK1, 3 AS TODAY, 'cocpt_29mar10.EG_WF_ACTIONS' AS TYPE From cocpt_29mar10.EG_WF_ACTIONS B left join JAILER_ENTITY_T Duplicate on Duplicate.r_entitygraph=1170796854 and Duplicate.type='cocpt_29mar10.EG_WF_ACTIONS' and Duplicate.PK0 is null and Duplicate.PK1=B.ID and Duplicate.PK2 is null and Duplicate.PK3 is null and Duplicate.PK4 is null and Duplicate.PK5 is null, cocpt_29mar10.EGPAY_SALARYCODES A , JAILER_ENTITY_T E Where (E.r_entitygraph=1170796854 and E.birthday = 2 and E.type='cocpt_29mar10.EGPAY_SALARYCODES' and E.PK0 is null and E.PK1=A.ID and E.PK2 is null and E.PK3 is null and E.PK4 is null and E.PK5 is null) and Duplicate.type is null and (A.ID_WF_ACTION=B.ID)
    2010-06-17 17:31:27,046  ERROR  - Error executing statement
    java.sql.SQLException: ORA-00904: "A"."ID_WF_ACTION": invalid identifier

    This shows that "A" is alias to EGPAY_SALARYCODES table. When i describe this table, it shows as…

    DESC EGPAY_SALARYCODES
    Name Null?   Type


    ID NOT NULL NUMBER(12)
    HEAD NOT NULL VARCHAR2(64)
    CATEGORYID NOT NULL NUMBER(12)
    CREATEDBY NOT NULL NUMBER
    CREATEDDATE NOT NULL DATE
    LASTMODIFIEDBY NOT NULL NUMBER
    LASTMODIFIEDDATE NOT NULL DATE
    DESCRIPTION NOT NULL NVARCHAR2(1000)
    IS_TAXABLE NOT NULL CHAR(1)
    CAL_TYPE NOT NULL VARCHAR2(32)
    GLCODEID   NUMBER(12)
    PCT_BASIS   NUMBER(12)
    ORDER_ID   NUMBER(12)
    TDS_ID   NUMBER(12)
    LOCAL_LANG_DESC   NVARCHAR2(1000)
    INTEREST_GLCODEID   NUMBER(12)
    ISATTENDANCEBASED NOT NULL VARCHAR2(1)
    ISRECOMPUTED NOT NULL VARCHAR2(1)
    ISRECURRING NOT NULL VARCHAR2(1)
    CAPTURE_RATE NOT NULL VARCHAR2(1)

     
  • Ralf Wisser

    Ralf Wisser - 2010-06-17

    Obviously there is an association between "EG_WF_ACTIONS" and "EGPAY_SALARYCODES", where the join-condition is incorrect, as it refers to EGPAY_SALARYCODES.ID_WF_ACTION. (Does the table "EG_WF_ACTIONS" have a column "ID_WF_ACTION"? Maybe the aliases A and B are not correctly used in the join-condition) Did you define the association manually? Or did you modify an association after analysing the schema? If it is possible for you to send the data model. I would like to take a look into it. If you like, you may send the files in the "datamodel" folder to rwisser@users.sourceforge.net Ralf

     
  • Ralf Wisser

    Ralf Wisser - 2010-06-17

    Is it possible that the column "EGPAY_SALARYCODES"."ID_WF_ACTION" exists in the schema you've analysed, but not in the schema from where you exported data from?

     
  • Vijay Ingole

    Vijay Ingole - 2010-06-17

    Thanks again…
    I am reanalysing the schema. As that finishes, I'll mail the files. I am doing the analysis and export using same schema.

     


Anonymous

Cancel  Add attachments





Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks