001: /*
002: * Created on Jul 2, 2004
003: *
004: * To change the template for this generated file go to
005: * Window>Preferences>Java>Code Generation>Code and Comments
006: */
007: package com.pk;
008:
009: import java.sql.Connection;
010: import java.sql.ResultSet;
011: import java.sql.SQLException;
012: import java.sql.Statement;
013: import java.util.Vector;
014:
015: /**
016: * @author Isabelle
017: *
018: * To change the template for this generated type comment go to
019: * Window>Preferences>Java>Code Generation>Code and Comments
020: */
021: public class OracleDialect implements DatabaseDialect {
022: public static final String QUERYPLANSQL = "SELECT ' '||level||' ' lev, lpad(operation, length(operation) + 4*(level-1)) || decode(id, 0, ' Optimizer='||optimizer, null) ||' '||options||\n"
023: + "decode(object_name,null,null,' OF ')||object_name||\n"
024: + "decode(object_type,'UNIQUE', ' (U) ', 'NON-UNIQUE',\n"
025: + "'(NU)',null) plan \n"
026: + "FROM PLAN_TABLE \n"
027: + "START with ID = 0 and STATEMENT_ID = 'pk00001' \n"
028: + "CONNECT by prior ID = PARENT_ID and STATEMENT_ID = 'pk00001' \n";
029:
030: public static final String DELETEPLANSQL = "DELETE FROM PLAN_TABLE";
031:
032: /* (non-Javadoc)
033: * @see com.pk.DatabaseDialect#getKeywords()
034: */
035: public Vector getKeywords() {
036: Vector keywords = new Vector();
037: keywords.addElement("select");
038: keywords.addElement("set");
039: keywords.addElement("or");
040: keywords.addElement("and");
041: keywords.addElement("where");
042: keywords.addElement("from");
043: keywords.addElement("insert");
044: keywords.addElement("update");
045: keywords.addElement("order");
046: keywords.addElement("by");
047: keywords.addElement("close");
048: keywords.addElement("commit");
049: keywords.addElement("rollback");
050: keywords.addElement("create");
051: keywords.addElement("database");
052: keywords.addElement("drop");
053: keywords.addElement("table");
054: keywords.addElement("view");
055: keywords.addElement("join");
056: keywords.addElement("into");
057: keywords.addElement("between");
058: keywords.addElement("values");
059: keywords.addElement("alter");
060: keywords.addElement("add");
061: keywords.addElement("modify");
062: keywords.addElement("close");
063: keywords.addElement("distinct");
064: keywords.addElement("index");
065: keywords.addElement("grant");
066: keywords.addElement("delete");
067: keywords.addElement("group");
068: keywords.addElement("as");
069: keywords.addElement("having");
070: keywords.addElement("delete");
071: keywords.addElement("like");
072: keywords.addElement("unique");
073: keywords.addElement("primary");
074: keywords.addElement("key");
075: keywords.addElement("not");
076: keywords.addElement("null");
077: keywords.addElement("asc");
078: keywords.addElement("desc");
079: keywords.addElement("revoke");
080: keywords.addElement("remark");
081: keywords.addElement("exists");
082: keywords.addElement("union");
083: keywords.addElement("decode");
084:
085: return keywords;
086: }
087:
088: /* (non-Javadoc)
089: * @see com.pk.DatabaseDialect#getTableInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
090: */
091: public String getTableInfoSQLString(String argTableName,
092: ConnectionInformation argConnectionInformation) {
093: String sqlString = "SELECT COLUMN_NAME, DECODE(NULLABLE,'N', 'NOT NULL', 'Y', NULL) AS NULLABLE, "
094: + "DATA_TYPE, DECODE(DATA_TYPE, 'VARCHAR2', TO_CHAR(DATA_LENGTH), 'NUMBER', "
095: + "DECODE(DATA_SCALE,0,TO_CHAR(DATA_PRECISION),NULL,NULL,DATA_PRECISION||','||DATA_SCALE)) AS \"SIZE\" "
096: + "FROM USER_TAB_COLUMNS "
097: + "WHERE TABLE_NAME = '"
098: + argTableName + "'" + " ORDER BY COLUMN_ID";
099: return sqlString;
100: }
101:
102: /* (non-Javadoc)
103: * @see com.pk.DatabaseDialect#getIndexInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
104: */
105: public String getIndexInfoSQLString(String argTableName,
106: ConnectionInformation argConnectionInformation) {
107: String sqlString = "SELECT TABLE_NAME,INDEX_NAME, "
108: + "COLUMN_POSITION AS ID,COLUMN_NAME "
109: + "FROM USER_IND_COLUMNS "
110: + "WHERE TABLE_NAME LIKE UPPER('" + argTableName
111: + "')||'%' "
112: + "ORDER BY TABLE_NAME,INDEX_NAME,COLUMN_POSITION";
113: return sqlString;
114: }
115:
116: /* (non-Javadoc)
117: * @see com.pk.DatabaseDialect#getProcedureInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
118: */
119: public String getProcedureInfoSQLString(String argProcedureName,
120: ConnectionInformation argConnectionInformation) {
121: String sqlString = "SELECT DECODE(UPPER(TRIM(TEXT)),'END;',TEXT,SUBSTR(TEXT,1,LENGTH(TEXT)-1)) AS \"PROCEDURE SOURCE\" FROM USER_SOURCE "
122: + "WHERE TYPE='PROCEDURE' "
123: + "AND NAME='"
124: + argProcedureName + "'" + "ORDER BY LINE";
125: return sqlString;
126: }
127:
128: /* (non-Javadoc)
129: * @see com.pk.DatabaseDialect#isSelectStatement(java.lang.String)
130: */
131: public boolean isSelectStatement(String argStatement) {
132: boolean isSelect = false;
133: if (argStatement.toUpperCase().startsWith("SELECT")
134: || argStatement.toUpperCase().startsWith("SHOW")) {
135: isSelect = true;
136: }
137: return isSelect;
138: }
139:
140: /* (non-Javadoc)
141: * @see com.pk.DatabaseDialect#isDescribeStatement(java.lang.String)
142: */
143: public boolean isDescribeStatement(String argStatement) {
144: boolean isDescribe = false;
145: if (argStatement.toUpperCase().startsWith("DESC")) {
146: isDescribe = true;
147: }
148: return isDescribe;
149: }
150:
151: /* (non-Javadoc)
152: * @see com.pk.DatabaseDialect#getDescribeSQLString(java.lang.String, com.pk.ConnectionInformation)
153: */
154: public String getDescribeSQLString(String argQuery,
155: ConnectionInformation argConnectionInformation) {
156: String tName = argQuery.substring(5);
157: String describeSQLString = "SELECT COLUMN_NAME, DECODE(NULLABLE,'N', 'NOT NULL', 'Y', NULL) AS NULLABLE, "
158: + "DATA_TYPE, DECODE(DATA_TYPE, 'VARCHAR2', TO_CHAR(DATA_LENGTH), 'NUMBER', "
159: + "DECODE(DATA_SCALE,0,TO_CHAR(DATA_PRECISION),NULL,NULL,DATA_PRECISION||','||DATA_SCALE)) AS \"SIZE\" "
160: + "FROM ALL_TAB_COLUMNS "
161: + "WHERE TABLE_NAME = '"
162: + tName.toUpperCase() + "'" + " ORDER BY COLUMN_ID";
163: return describeSQLString;
164: }
165:
166: /* (non-Javadoc)
167: * @see com.pk.DatabaseDialect#doExecutePlan(java.lang.String, com.pk.ConnectionInformation, java.sql.Connection)
168: */
169: public String doExecutePlan(String argStatement,
170: ConnectionInformation argConnectionInformation,
171: Connection argConnection) throws SQLException {
172: String query = "EXPLAIN PLAN SET STATEMENT_ID = 'pk00001' FOR "
173: + argStatement;
174: String planOut = "";
175: Statement statement = argConnection.createStatement();
176: statement.executeQuery(query);
177:
178: ResultSet planResult = statement.executeQuery(QUERYPLANSQL);
179:
180: while (planResult.next()) {
181: planOut = planOut
182: + //planResult.getString("id") +
183: //planResult.getString("p_id") +
184: planResult.getString("lev")
185: + planResult.getString("plan") + "\n";
186: }
187:
188: statement.executeUpdate(DELETEPLANSQL);
189:
190: return planOut;
191: }
192:
193: /* (non-Javadoc)
194: * @see com.pk.DatabaseDialect#getPackageInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
195: */
196: public String getPackageInfoSQLString(String argProcedureName,
197: ConnectionInformation argConnectionInformation) {
198: return "SELECT DECODE(UPPER(TRIM(TEXT)),'END;',TEXT,SUBSTR(TEXT,1,LENGTH(TEXT)-1)) AS \"PACKAGE SOURCE\" FROM USER_SOURCE "
199: + "WHERE TYPE='PACKAGE' "
200: + "AND NAME='"
201: + argProcedureName + "'" + "ORDER BY LINE";
202: }
203:
204: }
|