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

Example 6 with OracleStatementParser

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

the class OracleLiteralTest method test_oracle.

public void test_oracle() throws Exception {
    String sql = "SELECT FROM_TZ(TIMESTAMP '2007-11-20 08:00:00', '3:00') FROM DUAL;";
    String expect = "SELECT FROM_TZ(TIMESTAMP '2007-11-20 08:00:00', '3:00')\n" + "FROM DUAL;\n";
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    String text = TestUtils.outputOracle(stmt);
    Assert.assertEquals(expect, text);
    System.out.println(text);
}

Example 7 with OracleStatementParser

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

the class OracleLiteralTest method test_date.

public void test_date() throws Exception {
    String sql = "SELECT DATE '1998-12-25' FROM DUAL;";
    String expect = "SELECT DATE '1998-12-25'\n" + "FROM DUAL;\n";
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    String text = TestUtils.outputOracle(stmt);
    Assert.assertEquals(expect, text);
    System.out.println(text);
}

Example 8 with OracleStatementParser

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

the class OracleMergeTest2 method test_0.

public void test_0() throws Exception {
    String sql = //
    "MERGE INTO \"ESCROW\".\"HT_TASK_TRADE_HISTORY_NEW\" SNA$ " + //
    " USING (" + //
    "      SELECT CURRENT$.\"ID\",CURRENT$.\"GMT_MODIFIED\"" + //
    "          ,CURRENT$.\"GMT_CREATE\",CURRENT$.\"TRADE_ID\",CURRENT$.\"STATUS\",CURRENT$.\"OWNER\"" + //
    "          ,CURRENT$.\"GMT_FETCH_TASK\",CURRENT$.\"GMT_FINISH_TASK\",CURRENT$.\"VERSION\"" + //
    "          ,CURRENT$.\"RECORD_TYPE\",CURRENT$.\"TASK_FLOW_LEVEL\",CURRENT$.\"DEAL_TYPE\"" + //
    "          ,CURRENT$.\"END_REASON\",CURRENT$.\"TRANSIT_TIME\" " + //
    "      FROM (" + //
    "          SELECT \"HT_TASK_TRADE_HISTORY\".\"ID\" \"ID\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"GMT_MODIFIED\" \"GMT_MODIFIED\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"GMT_CREATE\" \"GMT_CREATE\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"TRADE_ID\" \"TRADE_ID\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"STATUS\" \"STATUS\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"OWNER\" \"OWNER\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"GMT_FETCH_TASK\" \"GMT_FETCH_TASK\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"GMT_FINISH_TASK\" \"GMT_FINISH_TASK\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"VERSION\" \"VERSION\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"RECORD_TYPE\" \"RECORD_TYPE\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"TASK_FLOW_LEVEL\" \"TASK_FLOW_LEVEL\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"DEAL_TYPE\" \"DEAL_TYPE\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"END_REASON\" \"END_REASON\"" + //
    "          ,\"HT_TASK_TRADE_HISTORY\".\"TRANSIT_TIME\" \"TRANSIT_TIME\" " + //
    "          FROM \"ESCROW\".\"HT_TASK_TRADE_HISTORY\" \"HT_TASK_TRADE_HISTORY\"" + //
    "          ) CURRENT$, " + //
    "          (SELECT DISTINCT MLOG$.\"ID\" " + //
    "          FROM \"ESCROW\".\"MLOG$_HT_TASK_TRADE_HISTOR\" MLOG$ " + //
    "          WHERE \"SNAPTIME$$\" > :1 AND (\"DMLTYPE$$\" != 'D')" + //
    "          ) LOG$ " + "      WHERE CURRENT$.\"ID\" = LOG$.\"ID\") AS OF SNAPSHOT(:SCN)  MAS$ ON (SNA$.\"ID\" = MAS$.\"ID\") WHEN MATCHED THEN UPDATE SET SNA$.\"ID\" = MAS$.\"ID\", SNA$.\"GMT_MODIFIED\" = MAS$.\"GMT_MODIFIED\", SNA$.\"GMT_CREATE\" = MAS$.\"GMT_CREATE\", SNA$.\"TRADE_ID\" = MAS$.\"TRADE_ID\", SNA$.\"STATUS\" = MAS$.\"STATUS\", SNA$.\"OWNER\" = MAS$.\"OWNER\", SNA$.\"GMT_FETCH_TASK\" = MAS$.\"GMT_FETCH_TASK\", SNA$.\"GMT_FINISH_TASK\" = MAS$.\"GMT_FINISH_TASK\", SNA$.\"VERSION\" = MAS$.\"VERSION\", SNA$.\"RECORD_TYPE\" = MAS$.\"RECORD_TYPE\", SNA$.\"TASK_FLOW_LEVEL\" = MAS$.\"TASK_FLOW_LEVEL\", SNA$.\"DEAL_TYPE\" = MAS$.\"DEAL_TYPE\", SNA$.\"END_REASON\" = MAS$.\"END_REASON\", SNA$.\"TRANSIT_TIME\" = MAS$.\"TRANSIT_TIME\" WHEN NOT MATCHED THEN INSERT (\"ID\",\"GMT_MODIFIED\",\"GMT_CREATE\",\"TRADE_ID\",\"STATUS\",\"OWNER\",\"GMT_FETCH_TASK\",\"GMT_FINISH_TASK\",\"VERSION\",\"RECORD_TYPE\",\"TASK_FLOW_LEVEL\",\"DEAL_TYPE\",\"END_REASON\",\"TRANSIT_TIME\") VALUES (MAS$.\"ID\",MAS$.\"GMT_MODIFIED\",MAS$.\"GMT_CREATE\",MAS$.\"TRADE_ID\",MAS$.\"STATUS\",MAS$.\"OWNER\",MAS$.\"GMT_FETCH_TASK\",MAS$.\"GMT_FINISH_TASK\",MAS$.\"VERSION\",MAS$.\"RECORD_TYPE\",MAS$.\"TASK_FLOW_LEVEL\",MAS$.\"DEAL_TYPE\",MAS$.\"END_REASON\",MAS$.\"TRANSIT_TIME\")";
    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());
    Assert.assertEquals(3, visitor.getTables().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("ESCROW.HT_TASK_TRADE_HISTORY")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("ESCROW.MLOG$_HT_TASK_TRADE_HISTOR")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("ESCROW.HT_TASK_TRADE_HISTORY_NEW")));
    Assert.assertEquals(31, visitor.getColumns().size());
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "salary")));
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "employee_id")));
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "bonus")));
}

Example 9 with OracleStatementParser

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

the class OracleMergeTest3 method test_0.

public void test_0() throws Exception {
    String sql = "merge /*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm) dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */ into sys.mon_mods_all$ mm using (select m.obj# obj#, m.inserts inserts, m.updates updates, m.deletes deletes, m.flags flags, m.timestamp timestamp, m.drop_segments drop_segments from sys.mon_mods$ m, tab$ t where m.obj# = t.obj# ) v on (mm.obj# = v.obj#) when matched then update set mm.inserts = mm.inserts + v.inserts, mm.updates = mm.updates + v.updates, mm.deletes = mm.deletes + v.deletes, mm.flags = mm.flags + v.flags - bitand(mm.flags,v.flags) /* bitor(mm.flags,v.flags) */, mm.timestamp = v.timestamp, mm.drop_segments = mm.drop_segments + v.drop_segments when NOT matched then insert (obj#, inserts, updates, deletes, timestamp, flags, drop_segments) values (v.obj#, v.inserts, v.updates, v.deletes, sysdate, v.flags, v.drop_segments);";
    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());
    Assert.assertEquals(3, visitor.getTables().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sys.mon_mods$")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("tab$")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sys.mon_mods_all$")));
    Assert.assertEquals(16, visitor.getColumns().size());
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "salary")));
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "employee_id")));
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "bonus")));
}

Example 10 with OracleStatementParser

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

the class OracleMergeTest6 method test_0.

public void test_0() throws Exception {
    String sql = //
    "MERGE INTO console_stb_ipstatus T1 " + //
    "USING (SELECT '02222601005592002863423471' AS stbid  FROM dual) T2 " + //
    "ON ( T1.stbid=T2.stbid) " + //
    "WHEN MATCHED THEN " + //
    "update set t1.ip='10.104.131.175',t1.port='6666',t1.status = 1, t1.time = to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')  " + //
    "WHEN NOT MATCHED THEN  insert (id, stbid, ip, port, time, firsttime, status) " + "values (CONSOLE_SEQ.nextval,'02222601005592002863423471','10.104.131.175','6666',to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss'),to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss'),1) ";
    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());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("console_stb_ipstatus")));
    Assert.assertEquals(6, visitor.getColumns().size());
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "salary")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "employee_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("bonuses", "bonus")));
}