3 Replies Latest reply on Feb 4, 2019 4:22 PM by Steven Hawkins

    loadMatView EXECUTE IMMEDIATE is truncating Insert SQL statement

    Mark Tawk Novice

      I am using Teiid embedded 10.2.1 with SQL server translator

      I had a view that was not materializing without any raised exception or log error.

      After investigating, we find out that the issue is coming from the stored procedure loadMatView located in SYSADMIN.sql.

      The line below is truncating the SQL string to 4000 characters, and the insert statement for our view is larger than 4000 characters

                      EXECUTE IMMEDIATE 'INSERT INTO ' || matViewStageTable || '(' || columns ||') SELECT '|| columns ||' FROM ' || schemaName || '.' || viewName || ' OPTION NOCACHE ' || schemaName || '.' || viewName;

      We tried to put the insert statement SQL into a clob variable and it is still truncated on 4000

      Your advice is appreciated to solve this issue.

       

      Also, we noticed that some log contexts used in SYSADMIN.sql are not valid (org.teiid.MATVIEWS) or it should be org.teiid.PROCESSOR.MATVIEWS

              EXECUTE logMsg(context=>'org.teiid.MATVIEWS', level=>'WARN', msg=>e.exception);