5 Replies Latest reply: Feb 7, 2014 4:22 AM by Aliaksei RSS

    How to validate view SQL during transformation script

    Justin B-Oracle

      Hi,

       

      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 3.3.0.744 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.

      Example:

       

      -----------------------------------------------------------------------------------------------------

      //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.setName("V_ON_FAKE_TABLE");

      v.setUserDefined(true);

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

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

       

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

      msgBox(v.generateSQL());

      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();

      t.setName("REAL_TABLE")

      t.createColumn("COL1");

      t.createColumn("COL2");

       

      //Create a new view pointing to the real table

      v = tvs.createTableView();

       

      //Test custom attributes

      v.setName("V_ON_REAL_TABLE");

      v.setUserDefined(true);

      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(v.generateSQL());

      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');

              dialog.show();

      }