2 Replies Latest reply: Jan 24, 2013 6:09 AM by PeakIndicators_Alastair RSS

    ORA-01747: invalid user.table.column, table.column, or column specification

    Prerna Vij
      Hi,

      I am using IKM Oracle Slowly Changing Dimension to implement SCD type 2.
      I have a sequence marked as Surrogate Key, A Natural Key, 4 columns with behaviour set as 'Add Row On Change', 2 columns as ' Overwrite on change', 1 Start_timestamp, end timestamp and flag column in the datastore.
      I have the flow control turned on.
      When I execute the interface, I get the following error in step Update Existing Rows
      ORA-01747: invalid user.table.column, table.column, or column specification"


      I have gone through the ODI Experts blog and cross-checked that I am following everything. I have also gone through the threads on OTN.
      Please provide me with a solution. It is urgent.

      Regards,
      Prerna
        • 1. Re: ORA-01747: invalid user.table.column, table.column, or column specification
          JeromeFr
          Hi,

          Could you paste the code generated for this step?


          Thanks,

          JeromeFr
          • 2. Re: ORA-01747: invalid user.table.column, table.column, or column specification
            PeakIndicators_Alastair
            Hi,

            That message means the syntax of the SQL ODI has generated and sent to the database is incorrect, usually means something has been mis-configured in your ODI Model, Interface etc.

            Go into Operator, copy the SQL from the step that is failing , run it in the target DB as the same user configured for that data server in Topology, it will give you the line number which (at least) which will be a start.

            Also paste up the generated SQL here,
            What I would do next is have a look at the Knowledge module step, find the specific API call generating the incorrect syntax and see what its looking for in the ODI metadata, e.g
            Consult the Substitution API reference documentation - its well written up.

            <%=odiRef.getColList("", "Y.[COL_NAME]=X.[COL_NAME]", "\n\tand\t", "", "PK")%> for example is looking for primary key columns to be defined in the interface. Usually you can debug it from there pretty easily.