Examples with OracleStatementParser com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser used on opensource projects

Example 71 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleCreateTableTest36 method test_types.

public void test_types() throws Exception {
    //
    String sql = "CREATE TABLE composite_sales" + "    ( prod_id        NUMBER(6)" + "    , cust_id        NUMBER" + "    , time_id        DATE" + "    , channel_id     CHAR(1)" + "    , promo_id       NUMBER(6)" + "    , quantity_sold  NUMBER(3)" + "    , amount_sold         NUMBER(10,2)" + "    ) " + "PARTITION BY RANGE (time_id)" + "SUBPARTITION BY HASH (channel_id)" + "  (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY'))," + "   PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY'))," + "   PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY'))," + "   PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY'))," + "   PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY'))," + "   PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY'))," + "   PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY'))," + "   PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))," + "   PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY'))," + "   PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY'))" + "      SUBPARTITIONS 8," + "   PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY'))" + "     (SUBPARTITION ch_c," + "      SUBPARTITION ch_i," + "      SUBPARTITION ch_p," + "      SUBPARTITION ch_s," + "      SUBPARTITION ch_t)," + "   PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)" + "      SUBPARTITIONS 4)" + ";";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);
    Assert.assertEquals(1, statementList.size());
    Assert.assertEquals("CREATE TABLE composite_sales (" + "\n\tprod_id NUMBER(6)," + "\n\tcust_id NUMBER," + "\n\ttime_id DATE," + "\n\tchannel_id CHAR(1)," + "\n\tpromo_id NUMBER(6)," + "\n\tquantity_sold NUMBER(3)," + "\n\tamount_sold NUMBER(10, 2)" + "\n)" + "\nPARTITION BY RANGE (time_id)" + "\nSUBPARTITION BY HASH (channel_id)" + "\n(" + "\n\tPARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000', 'DD-MON-YYYY'))" + "\n\t\tSUBPARTITIONS 8," + "\n\tPARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000', 'DD-MON-YYYY'))" + "\n\t(" + "\n\t\tSUBPARTITION ch_c," + "\n\t\tSUBPARTITION ch_i," + "\n\t\tSUBPARTITION ch_p," + "\n\t\tSUBPARTITION ch_s," + "\n\t\tSUBPARTITION ch_t" + "\n\t)," + "\n\tPARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)" + "\n\t\tSUBPARTITIONS 4" + //
    "\n)", SQLUtils.toSQLString(stmt, JdbcConstants.ORACLE));
    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    stmt.accept(visitor);
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("relationships : " + visitor.getRelationships());
    System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(7, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("composite_sales", "prod_id")));
}

Example 72 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleBlockTest2 method test_0.

public void test_0() throws Exception {
    String sql = //
    "declare   i integer := 0; " + //
    "begin   " + //
    "  for c in (" + //
    "      select id " + //
    "      from wl_ship_order" + //
    "      where forwarder_service is null or status is null) " + //
    "  loop" + //
    "      update wl_ship_order" + //
    "          set forwarder_service = nvl(forwarder_service, 'UPS'), status = nvl(status, 500)" + //
    "      where id = c.id;" + //
    "      i := i + 1;" + //
    "      if mod(i, 100) = 0 then" + //
    "          commit;" + //
    "      end if;" + //
    "  end loop;" + //
    "  commit; " + "end;";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement statemen = statementList.get(0);
    print(statementList);
    Assert.assertEquals(1, statementList.size());
    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    statemen.accept(visitor);
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("relationships : " + visitor.getRelationships());
    System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("wl_ship_order")));
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertEquals(3, visitor.getConditions().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("wl_ship_order", "id")));
}

Example 73 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleBlockTest7 method test_0.

public void test_0() throws Exception {
    String sql = //
    "DROP TABLE employees_temp; " + //
    "CREATE TABLE employees_temp AS " + //
    "  SELECT employee_id, first_name, last_name " + //
    "  FROM employees;" + //
    " " + //
    "DECLARE" + //
    "  emp_id          employees_temp.employee_id%TYPE := 299;" + //
    "  emp_first_name  employees_temp.first_name%TYPE  := 'Bob';" + //
    "  emp_last_name   employees_temp.last_name%TYPE   := 'Henry';" + //
    "BEGIN" + //
    "  INSERT INTO employees_temp (employee_id, first_name, last_name) " + //
    "  VALUES (emp_id, emp_first_name, emp_last_name);" + //
    " " + //
    "  UPDATE employees_temp" + //
    "  SET first_name = 'Robert'" + //
    "  WHERE employee_id = emp_id;" + //
    " " + //
    "  DELETE FROM employees_temp" + //
    "  WHERE employee_id = emp_id" + //
    "  RETURNING first_name, last_name" + //
    "  INTO emp_first_name, emp_last_name;" + //
    "" + //
    "  COMMIT;" + //
    "  DBMS_OUTPUT.PUT_LINE (emp_first_name || ' ' || emp_last_name);" + //
    "END;";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    print(statementList);
    Assert.assertEquals(3, statementList.size());
    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    for (SQLStatement statement : statementList) {
        statement.accept(visitor);
    }
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("relationships : " + visitor.getRelationships());
    System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(2, visitor.getTables().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees_temp")));
    Assert.assertEquals(6, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
}

Example 74 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleBlockTest9 method test_0.

public void test_0() throws Exception {
    String sql = //
    "DECLARE" + //
    "  my_emp_id NUMBER(6);" + //
    "  my_job_id VARCHAR2(10);" + //
    "  my_sal    NUMBER(8,2);" + //
    "  CURSOR c1 IS" + //
    "    SELECT employee_id, job_id, salary" + //
    "    FROM employees FOR UPDATE;" + //
    "BEGIN" + //
    "  OPEN c1;" + //
    "  LOOP" + //
    "    FETCH c1 INTO my_emp_id, my_job_id, my_sal;" + //
    "    IF my_job_id = 'SA_REP' THEN" + //
    "      UPDATE employees" + //
    "      SET salary = salary * 1.02" + //
    "      WHERE CURRENT OF c1;" + //
    "    END IF;" + //
    "    EXIT WHEN c1%NOTFOUND;" + //
    "  END LOOP;" + //
    "END;";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    print(statementList);
    Assert.assertEquals(1, statementList.size());
    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    for (SQLStatement statement : statementList) {
        statement.accept(visitor);
    }
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("relationships : " + visitor.getRelationships());
    System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees")));
    Assert.assertEquals(3, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "salary")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "job_id")));
}

Example 75 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleCreateDbLinkTest method test_1.

public void test_1() throws Exception {
    //
    String sql = "CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local';";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement statement = statementList.get(0);
    print(statementList);
    Assert.assertEquals("CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local'", SQLUtils.toSQLString(statement, JdbcConstants.ORACLE));
    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    statement.accept(visitor);
    Assert.assertEquals(0, visitor.getTables().size());
}