5 Replies Latest reply on Feb 7, 2014 10:22 AM by Aliaksei

    How to validate view SQL during transformation script

    Justin B-Oracle



      Is it possible to verify a TableView has been properly created after doing setUserDefinedSQL. I see functions for isValidSQL and isIncorrectSQL but it doesn't seem to be returning the values I expect. Is there another method I should be trying?


      I'm using SQL DM and Groovy scripting engine 2.0.0


      Here is an example of 2 views, 1 created pointing to a table that doesn't exist, and the other that does. I would explect isValidSQL or isIncorrectSQL to return a boolean value that will tell me the view was not successfully created because the table doesn't exist, but in this example, it doesn't seem to change.




      //import for msgBox

      import groovy.swing.SwingBuilder;

      import javax.swing.JOptionPane;


      //import for SQL DM objects

      import oracle.dbtools.crest.model.design.relational.TableViewSet;

      import oracle.dbtools.crest.model.design.relational.TableView;

      import oracle.dbtools.crest.model.design.relational.Table;

      import oracle.dbtools.crest.model.design.relational.TableSet;


      //Get TableView object

      TableViewSet tvs = model.getTableViewSet();

      TableView v = tvs.createTableView();


      //Test custom attributes



      v.setUserDefinedSQL("SELECT COL1, COL2 FROM FAKE_TABLE");

      v.validateSQL();     //this is void method


      msgBox("This is SQL stored in DM:");


      msgBox("The FROM part of SQL statement is missing");

      msgBox("Yet, is valid SQL returns: '" + v.isValidSQL() + "'");                  //true -> I would expect this to be false

      msgBox("Is incorrect SQL also returns :" + v.isIncorrectSQL());             //false -> I would expect this to be false


      //Now create a table to create view against

      msgBox("Now creating a table to create view against");

      TableSet ts = model.getTableSet();

      Table t = ts.createTable();





      //Create a new view pointing to the real table

      v = tvs.createTableView();


      //Test custom attributes



      v.setUserDefinedSQL("SELECT COL1, COL2 FROM REAL_TABLE");

      v.validateSQL();     //this is void method


      msgBox("Here is the SQL stored in DM for the new view");


      msgBox("The FROM part of SQL statement now part the DDL");

      msgBox("Is valid SQL seems correct: " + v.isValidSQL());                  //Still true

      msgBox("Is incorrect SQL does also: " + v.isIncorrectSQL());     //Still false


      private void msgBox(String msg){

              def swingBuilder = new SwingBuilder();

              def pane = swingBuilder.optionPane(message:msg, optionType:JOptionPane.CLOSED_OPTION);

              def dialog = pane.createDialog(null, 'Alert Message');