3 Replies Latest reply on Oct 16, 2015 7:42 AM by prashanthi1

    insert in to oracle db by sequence generated number

    prashanthi1
    prashanthi1 Oct 14, 2015 9:05 AM

      Hi,

       

      We need to insert in to a table in Oracle Database whose primary key is a sequence generated number.

      We created a sequence in Source with columns nextval and currval having 'Name in source' as '<SequenceName>.NEXTVAL'  and '<SequenceName.CURRVAL>'.

      when a view table is created for this source table, we are able to Preview data and get the nextval and currval.

      For insert we declared a variable and used it for insert like below:

       

      FOR EACH ROW

      BEGIN ATOMIC

        DECLARE string VARIABLES.nextVal = (SELECT cast(OracleConn.sequence.nextval AS string) FROM OracleConn.sequence);

        INSERT INTO OracleConn.Table1(OracleConn.Table1.ID, OracleConn.Table1.FIRST_NAME, OracleConn.Table1.LAST_NAME) VALUES (VARIABLES.nextVal, "NEW".FIRST_NAME, "NEW".LAST_NAME);

      END

       

      We are getting multiple errros:

      1. when we created a sequence in source, odata url gives error - Capabilites of this OracleConn are not available.

      2. "TEIID30328 Unable to evaluate (SELECT nextval FROM v2.seq LIMIT 2): TEIID30504 OracleConn: 17002 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT Table1_SEQ.NEXTVAL AS c_0 FROM DUAL]"

      if we give 'Name in source'  for the sequence table as DUAL


      How do we insert in to a table by a sequence generated number?

      Any documentations/links will be of much help.


      Thanks in advance.

      Prashanthi.