/*
* Created on Jul 2, 2004
*
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package com.pk;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
/**
* @author Isabelle
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class OracleDialect implements DatabaseDialect
{
public static final String QUERYPLANSQL = "SELECT ' '||level||' ' lev, lpad(operation, length(operation) + 4*(level-1)) || decode(id, 0, ' Optimizer='||optimizer, null) ||' '||options||\n"
+ "decode(object_name,null,null,' OF ')||object_name||\n" + "decode(object_type,'UNIQUE', ' (U) ', 'NON-UNIQUE',\n" + "'(NU)',null) plan \n"
+ "FROM PLAN_TABLE \n" + "START with ID = 0 and STATEMENT_ID = 'pk00001' \n" + "CONNECT by prior ID = PARENT_ID and STATEMENT_ID = 'pk00001' \n";
public static final String DELETEPLANSQL = "DELETE FROM PLAN_TABLE";
/* (non-Javadoc)
* @see com.pk.DatabaseDialect#getKeywords()
*/
public Vector getKeywords()
{
Vector keywords = new Vector();
keywords.addElement("select");
keywords.addElement("set");
keywords.addElement("or");
keywords.addElement("and");
keywords.addElement("where");
keywords.addElement("from");
keywords.addElement("insert");
keywords.addElement("update");
keywords.addElement("order");
keywords.addElement("by");
keywords.addElement("close");
keywords.addElement("commit");
keywords.addElement("rollback");
keywords.addElement("create");
keywords.addElement("database");
keywords.addElement("drop");
keywords.addElement("table");
keywords.addElement("view");
keywords.addElement("join");
keywords.addElement("into");
keywords.addElement("between");
keywords.addElement("values");
keywords.addElement("alter");
keywords.addElement("add");
keywords.addElement("modify");
keywords.addElement("close");
keywords.addElement("distinct");
keywords.addElement("index");
keywords.addElement("grant");
keywords.addElement("delete");
keywords.addElement("group");
keywords.addElement("as");
keywords.addElement("having");
keywords.addElement("delete");
keywords.addElement("like");
keywords.addElement("unique");
keywords.addElement("primary");
keywords.addElement("key");
keywords.addElement("not");
keywords.addElement("null");
keywords.addElement("asc");
keywords.addElement("desc");
keywords.addElement("revoke");
keywords.addElement("remark");
keywords.addElement("exists");
keywords.addElement("union");
keywords.addElement("decode");
return keywords;
}
/* (non-Javadoc)
* @see com.pk.DatabaseDialect#getTableInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
*/
public String getTableInfoSQLString(String argTableName, ConnectionInformation argConnectionInformation)
{
String sqlString = "SELECT COLUMN_NAME, DECODE(NULLABLE,'N', 'NOT NULL', 'Y', NULL) AS NULLABLE, " + "DATA_TYPE, DECODE(DATA_TYPE, 'VARCHAR2', TO_CHAR(DATA_LENGTH), 'NUMBER', " + "DECODE(DATA_SCALE,0,TO_CHAR(DATA_PRECISION),NULL,NULL,DATA_PRECISION||','||DATA_SCALE)) AS \"SIZE\" " + "FROM USER_TAB_COLUMNS " + "WHERE TABLE_NAME = '" + argTableName + "'" + " ORDER BY COLUMN_ID";
return sqlString;
}
/* (non-Javadoc)
* @see com.pk.DatabaseDialect#getIndexInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
*/
public String getIndexInfoSQLString(String argTableName, ConnectionInformation argConnectionInformation)
{
String sqlString = "SELECT TABLE_NAME,INDEX_NAME, " + "COLUMN_POSITION AS ID,COLUMN_NAME " + "FROM USER_IND_COLUMNS " + "WHERE TABLE_NAME LIKE UPPER('" + argTableName + "')||'%' " + "ORDER BY TABLE_NAME,INDEX_NAME,COLUMN_POSITION";
return sqlString;
}
/* (non-Javadoc)
* @see com.pk.DatabaseDialect#getProcedureInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
*/
public String getProcedureInfoSQLString(String argProcedureName, ConnectionInformation argConnectionInformation)
{
String sqlString = "SELECT DECODE(UPPER(TRIM(TEXT)),'END;',TEXT,SUBSTR(TEXT,1,LENGTH(TEXT)-1)) AS \"PROCEDURE SOURCE\" FROM USER_SOURCE " + "WHERE TYPE='PROCEDURE' " + "AND NAME='" + argProcedureName + "'" + "ORDER BY LINE";
return sqlString;
}
/* (non-Javadoc)
* @see com.pk.DatabaseDialect#isSelectStatement(java.lang.String)
*/
public boolean isSelectStatement(String argStatement)
{
boolean isSelect = false;
if(argStatement.toUpperCase().startsWith("SELECT") || argStatement.toUpperCase().startsWith("SHOW") )
{
isSelect = true;
}
return isSelect;
}
/* (non-Javadoc)
* @see com.pk.DatabaseDialect#isDescribeStatement(java.lang.String)
*/
public boolean isDescribeStatement(String argStatement)
{
boolean isDescribe = false;
if(argStatement.toUpperCase().startsWith("DESC"))
{
isDescribe = true;
}
return isDescribe;
}
/* (non-Javadoc)
* @see com.pk.DatabaseDialect#getDescribeSQLString(java.lang.String, com.pk.ConnectionInformation)
*/
public String getDescribeSQLString(String argQuery, ConnectionInformation argConnectionInformation)
{
String tName = argQuery.substring(5);
String describeSQLString = "SELECT COLUMN_NAME, DECODE(NULLABLE,'N', 'NOT NULL', 'Y', NULL) AS NULLABLE, " + "DATA_TYPE, DECODE(DATA_TYPE, 'VARCHAR2', TO_CHAR(DATA_LENGTH), 'NUMBER', " + "DECODE(DATA_SCALE,0,TO_CHAR(DATA_PRECISION),NULL,NULL,DATA_PRECISION||','||DATA_SCALE)) AS \"SIZE\" " + "FROM ALL_TAB_COLUMNS " + "WHERE TABLE_NAME = '" + tName.toUpperCase() + "'" + " ORDER BY COLUMN_ID";
return describeSQLString;
}
/* (non-Javadoc)
* @see com.pk.DatabaseDialect#doExecutePlan(java.lang.String, com.pk.ConnectionInformation, java.sql.Connection)
*/
public String doExecutePlan(String argStatement, ConnectionInformation argConnectionInformation, Connection argConnection) throws SQLException
{
String query = "EXPLAIN PLAN SET STATEMENT_ID = 'pk00001' FOR " + argStatement;
String planOut = "";
Statement statement = argConnection.createStatement();
statement.executeQuery(query);
ResultSet planResult = statement.executeQuery(QUERYPLANSQL);
while (planResult.next())
{
planOut = planOut + //planResult.getString("id") +
//planResult.getString("p_id") +
planResult.getString("lev") + planResult.getString("plan") + "\n";
}
statement.executeUpdate(DELETEPLANSQL);
return planOut;
}
/* (non-Javadoc)
* @see com.pk.DatabaseDialect#getPackageInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
*/
public String getPackageInfoSQLString(String argProcedureName, ConnectionInformation argConnectionInformation)
{
return "SELECT DECODE(UPPER(TRIM(TEXT)),'END;',TEXT,SUBSTR(TEXT,1,LENGTH(TEXT)-1)) AS \"PACKAGE SOURCE\" FROM USER_SOURCE " + "WHERE TYPE='PACKAGE' " + "AND NAME='" + argProcedureName + "'" + "ORDER BY LINE";
}
}
|