001: /*
002: * OracleMetadata.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.oracle;
013:
014: import java.sql.PreparedStatement;
015: import java.sql.ResultSet;
016: import java.sql.SQLException;
017: import java.sql.Statement;
018: import java.sql.Types;
019: import java.util.Collections;
020: import java.util.HashSet;
021: import java.util.Properties;
022: import java.util.Set;
023: import workbench.db.ConnectionProfile;
024: import workbench.db.DataTypeResolver;
025: import workbench.db.ErrorInformationReader;
026: import workbench.db.TableIdentifier;
027: import workbench.db.WbConnection;
028: import workbench.resource.Settings;
029: import workbench.util.ExceptionUtil;
030: import workbench.log.LogMgr;
031: import workbench.util.SqlUtil;
032: import workbench.util.StringUtil;
033:
034: /**
035: *
036: * @author support@sql-workbench.net
037: */
038: public class OracleMetadata implements ErrorInformationReader,
039: DataTypeResolver {
040: private WbConnection connection;
041: private PreparedStatement columnStatement;
042: private int version;
043: private boolean retrieveSnapshots = true;
044: static final int BYTE_SEMANTICS = 0;
045: static final int CHAR_SEMANTICS = 1;
046: private int defaultLengthSemantics = -1;
047: private boolean alwaysShowCharSemantics = false;
048: private boolean useOwnSql = true;
049:
050: /**
051: * Only for testing purposes
052: */
053: OracleMetadata(int defaultSemantics, boolean alwaysShowSemantics) {
054: defaultLengthSemantics = defaultSemantics;
055: alwaysShowCharSemantics = alwaysShowSemantics;
056: }
057:
058: public OracleMetadata(WbConnection conn) {
059: this .connection = conn;
060: try {
061: this .version = this .connection.getSqlConnection()
062: .getMetaData().getDatabaseMajorVersion();
063: } catch (Throwable th) {
064: // The old Oracle 8 driver (classes12.jar) does not implement getDatabaseMajorVersion()
065: // and throws an AbstractMethodError
066: this .version = 8;
067: }
068:
069: alwaysShowCharSemantics = Settings
070: .getInstance()
071: .getBoolProperty(
072: "workbench.db.oracle.charsemantics.displayalways",
073: true);
074:
075: if (!alwaysShowCharSemantics) {
076: Statement stmt = null;
077: ResultSet rs = null;
078: try {
079: stmt = this .connection.createStatement();
080: String sql = "select value from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS'";
081: rs = stmt.executeQuery(sql);
082: if (rs.next()) {
083: String v = rs.getString(1);
084: if ("BYTE".equalsIgnoreCase(v)) {
085: defaultLengthSemantics = BYTE_SEMANTICS;
086: } else if ("CHAR".equalsIgnoreCase(v)) {
087: defaultLengthSemantics = CHAR_SEMANTICS;
088: }
089: }
090: } catch (Exception e) {
091: defaultLengthSemantics = BYTE_SEMANTICS;
092: LogMgr
093: .logWarning(
094: "OracleMetadata.<init>",
095: "Could not retrieve NLS_LENGTH_SEMANTICS from v$nls_parameters. Assuming byte semantics",
096: e);
097: } finally {
098: SqlUtil.closeAll(rs, stmt);
099: }
100: }
101:
102: boolean fixNVARCHAR = Settings.getInstance()
103: .useOracleNVarcharFix();
104: boolean checkCharSemantics = Settings.getInstance()
105: .useOracleCharSemanticsFix();
106:
107: useOwnSql = (version > 8 && (checkCharSemantics || fixNVARCHAR));
108: }
109:
110: public boolean isOracle8() {
111: return this .version == 8;
112: }
113:
114: private boolean getRemarksReporting() {
115: // The old "remarksReporting" property should not be taken from the
116: // System properties as a fall-back
117: String value = getDriverProperty("remarksReporting", false);
118: if (value == null) {
119: value = getDriverProperty("oracle.jdbc.remarksReporting",
120: true);
121: }
122: return "true".equalsIgnoreCase(value == null ? "false" : value
123: .trim());
124: }
125:
126: public boolean getMapDateToTimestamp() {
127: // Newer Oracle drivers support a connection property to automatically
128: // return DATE columns as Types.TIMESTAMP. We have to mimic that
129: // when using our own statement to retrieve column definitions
130: String value = getDriverProperty(
131: "oracle.jdbc.mapDateToTimestamp", true);
132: return "true".equalsIgnoreCase(value);
133: }
134:
135: private String getDriverProperty(String property,
136: boolean includeSystemProperty) {
137: String value = null;
138: ConnectionProfile profile = this .connection.getProfile();
139: if (profile != null) {
140: Properties props = profile.getConnectionProperties();
141: value = (props != null ? props.getProperty(property, null)
142: : null);
143: if (value == null && includeSystemProperty) {
144: value = System.getProperty(property, null);
145: }
146: }
147: return value;
148: }
149:
150: public ResultSet getColumns(String catalog, String schema,
151: String table, String cols) throws SQLException {
152: // make sure the statement object is closed properly
153: columnsProcessed();
154:
155: if (!useOwnSql) {
156: this .columnStatement = null;
157: return this .connection.getSqlConnection().getMetaData()
158: .getColumns(catalog, schema, table, cols);
159: }
160:
161: boolean fixNVARCHAR = Settings.getInstance()
162: .useOracleNVarcharFix();
163:
164: // Oracle 9 and above reports a wrong length if NLS_LENGTH_SEMANTICS is set to char
165: // this statement fixes this problem and also removes the usage of LIKE
166: // to speed up the retrieval.
167: final String sql1 = "SELECT NULL AS table_cat, \n"
168: + " t.owner AS table_schem, \n"
169: + " t.table_name AS table_name, \n"
170: + " t.column_name AS column_name, \n "
171: + " DECODE(t.data_type, 'CHAR', "
172: + Types.CHAR
173: + ", "
174: + " 'VARCHAR2', "
175: + Types.VARCHAR
176: + ", "
177: + " 'NVARCHAR2', "
178: + (fixNVARCHAR ? Types.VARCHAR : Types.OTHER)
179: + ", "
180: + " 'NCHAR', "
181: + (fixNVARCHAR ? Types.VARCHAR : Types.OTHER)
182: + ", "
183: + " 'NUMBER', "
184: + Types.DECIMAL
185: + ", "
186: + " 'LONG', "
187: + Types.LONGVARCHAR
188: + ", "
189: + " 'DATE', "
190: + (getMapDateToTimestamp() ? Types.TIMESTAMP
191: : Types.DATE)
192: + ", "
193: + " 'RAW', "
194: + Types.VARBINARY
195: + ", "
196: + " 'LONG RAW', "
197: + Types.LONGVARBINARY
198: + ", "
199: + " 'BLOB', "
200: + Types.BLOB
201: + ", "
202: + " 'CLOB', "
203: + Types.CLOB
204: + ", "
205: + " 'NCLOB', "
206: + (fixNVARCHAR ? Types.CLOB : Types.OTHER)
207: + ", "
208: + " 'BFILE', -13, "
209: + " 'FLOAT', "
210: + Types.FLOAT
211: + ", "
212: + " 'TIMESTAMP(6)', "
213: + Types.TIMESTAMP
214: + ", "
215: + " 'TIMESTAMP(6) WITH TIME ZONE', -101, "
216: + " 'TIMESTAMP(6) WITH LOCAL TIME ZONE', -102, "
217: + " 'INTERVAL YEAR(2) TO MONTH', -103, "
218: + " 'INTERVAL DAY(2) TO SECOND(6)', -104, "
219: + " 'BINARY_FLOAT', 100, "
220: + " 'BINARY_DOUBLE', 101, "
221: + " "
222: + Types.OTHER
223: + ") AS data_type, \n"
224: + " t.data_type AS type_name, \n"
225: + " DECODE(t.data_precision, null, "
226: + " decode(t.data_type, 'VARCHAR', t.char_length, "
227: + " 'VARCHAR2', t.char_length, "
228: + " 'NVARCHAR', t.char_length, "
229: + " 'NVARCHAR2', t.char_length, "
230: + " 'CHAR', t.char_length, "
231: + " 'NCHAR', t.char_length, t.data_length), "
232: + " t.data_precision) AS column_size, \n"
233: + " 0 AS buffer_length, \n"
234: + " t.data_scale AS decimal_digits, \n"
235: + " 10 AS num_prec_radix, \n"
236: + " DECODE (t.nullable, 'N', 0, 1) AS nullable, \n";
237:
238: final String sql2 = " t.data_default AS column_def, \n"
239: + " decode(t.data_type, 'VARCHAR2', "
240: + " decode(t.char_used, 'B', "
241: + BYTE_SEMANTICS
242: + ", 'C', "
243: + CHAR_SEMANTICS
244: + ", 0), "
245: + " 0) AS sql_data_type, \n "
246: + " 0 AS sql_datetime_sub, \n"
247: + " t.data_length AS char_octet_length, \n"
248: + " t.column_id AS ordinal_position, \n"
249: + " DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable \n"
250: + " FROM all_tab_columns t";
251:
252: // I'm not using LIKE for the condition to select owner/table
253: // because internally we never call this with wildcards and leaving out the
254: // like (which is used in the original statement from Oracle's driver)
255: // speeds up the statement
256: final String where = " WHERE t.owner = ? AND t.table_name = ? AND t.column_name LIKE ? ESCAPE '/' \n";
257: final String comment_join = " AND t.owner = c.owner (+) AND t.table_name = c.table_name (+) AND t.column_name = c.column_name (+) \n";
258: final String order = "ORDER BY table_schem, table_name, ordinal_position";
259:
260: final String sql_comment = sql1
261: + " c.comments AS remarks, \n" + sql2
262: + ", all_col_comments c \n" + where + comment_join
263: + order;
264: final String sql_no_comment = sql1
265: + " null AS remarks, \n" + sql2 + where + order;
266:
267: String sql = null;
268:
269: if (getRemarksReporting()) {
270: sql = sql_comment;
271: } else {
272: sql = sql_no_comment;
273: }
274:
275: ResultSet rs = null;
276:
277: int pos = table != null ? table.indexOf('@') : -1;
278:
279: if (pos > 0) {
280: String dblink = table.substring(pos);
281: table = table.substring(0, pos);
282: sql = StringUtil.replace(sql, "all_tab_columns",
283: "all_tab_columns" + dblink);
284: sql = StringUtil.replace(sql, "all_col_comments",
285: "all_col_comments" + dblink);
286: String dblinkOwner = this .getDbLinkTargetSchema(dblink
287: .substring(1), schema);
288: if (StringUtil.isEmptyString(schema)
289: && !StringUtil.isEmptyString(dblinkOwner)) {
290: schema = dblinkOwner;
291: }
292: }
293:
294: this .columnStatement = this .connection.getSqlConnection()
295: .prepareStatement(sql);
296: this .columnStatement.setString(1, schema);
297: this .columnStatement.setString(2, table);
298: this .columnStatement.setString(3, cols != null ? cols : "%");
299: // if (Settings.getInstance().getDebugMetadataSql())
300: // {
301: // LogMgr.logDebug("OracleMetadata.getColumns()", "Using: " + sql);
302: // }
303: rs = this .columnStatement.executeQuery();
304: return rs;
305: }
306:
307: private String getDbLinkTargetSchema(String dblink, String owner) {
308: String sql = null;
309: PreparedStatement stmt = null;
310: ResultSet rs = null;
311: String linkOwner = null;
312:
313: // check if DB Link name contains a domain
314: // If yes, use the link name directly
315: if (dblink.indexOf('.') > 0) {
316: sql = "SELECT username FROM all_db_links WHERE db_link = ? AND (owner = ? or owner = 'PUBLIC')";
317: } else {
318: // apparently Oracle stores all DB Links with the default domain
319: // appended. I did not find a reliable way to retrieve the domain
320: // name, so I'm using a like to retrieve the definition
321: // hoping that there won't be two dblinks with the same name
322: // but different domains
323: sql = "SELECT username FROM all_db_links WHERE db_link like ? AND (owner = ? or owner = 'PUBLIC')";
324: dblink = dblink + ".%";
325: }
326:
327: try {
328: synchronized (connection) {
329: stmt = this .connection.getSqlConnection()
330: .prepareStatement(sql);
331: stmt.setString(1, dblink);
332: stmt.setString(2, owner);
333: rs = stmt.executeQuery();
334: if (rs.next()) {
335: linkOwner = rs.getString(1);
336: }
337: }
338: } catch (Exception e) {
339: LogMgr.logError("OracleMetadata.getDblinkSchema()",
340: "Error retrieving target schema for DBLINK "
341: + dblink, e);
342: } finally {
343: SqlUtil.closeAll(rs, stmt);
344: }
345:
346: return linkOwner;
347: }
348:
349: /**
350: * Return the errors reported in the all_errors table for Oracle.
351: * This method can be used to obtain error information after a CREATE PROCEDURE
352: * or CREATE TRIGGER statement has been executed.
353: *
354: * @return extended error information if available
355: */
356: public String getErrorInfo(String schema, String objectName,
357: String objectType) {
358: final String ERROR_QUERY = "SELECT line, position, text "
359: + " FROM all_errors " + "WHERE owner = ? "
360: + " AND type = ? " + " AND name = ? ";
361:
362: if (objectType == null || objectName == null) {
363: return "";
364: }
365: PreparedStatement stmt = null;
366: ResultSet rs = null;
367:
368: StringBuilder result = new StringBuilder(250);
369: try {
370: if (objectName.indexOf('.') > -1) {
371: schema = objectName.substring(0, objectName
372: .indexOf('.'));
373: } else if (schema == null) {
374: schema = this .connection.getCurrentSchema();
375: }
376: stmt = this .connection.getSqlConnection().prepareStatement(
377: ERROR_QUERY);
378: stmt.setString(1, schema.toUpperCase().trim());
379: stmt.setString(2, objectType.toUpperCase().trim());
380: stmt.setString(3, StringUtil.trimQuotes(objectName));
381:
382: rs = stmt.executeQuery();
383: int count = 0;
384: while (rs.next()) {
385: if (count > 0) {
386: result.append("\r\n");
387: }
388: int line = rs.getInt(1);
389: int pos = rs.getInt(2);
390: String msg = rs.getString(3);
391: result.append("Error at line ");
392: result.append(line);
393: result.append(", position ");
394: result.append(pos);
395: result.append(": ");
396: result.append(msg);
397: count++;
398: }
399: } catch (SQLException e) {
400: LogMgr.logError("OracleMetadata.getExtendedErrorInfo()",
401: "Error retrieving error information", e);
402: } finally {
403: SqlUtil.closeAll(rs, stmt);
404: }
405: return result.toString();
406: }
407:
408: /**
409: * Returns a Set with Strings identifying available Snapshots (materialized views)
410: * The names will be returned as owner.tablename
411: * In case the retrieve throws an error, this method will return
412: * an empty set in subsequent calls.
413: */
414: public Set<String> getSnapshots(String schema) {
415: if (!retrieveSnapshots) {
416: return Collections.emptySet();
417: }
418: Set<String> result = new HashSet<String>();
419: String sql = "SELECT owner||'.'||mview_name FROM all_mviews";
420: if (schema != null) {
421: sql += " WHERE owner = ?";
422: }
423:
424: PreparedStatement stmt = null;
425: ResultSet rs = null;
426:
427: try {
428: stmt = this .connection.getSqlConnection().prepareStatement(
429: sql);
430: if (schema != null) {
431: stmt.setString(1, schema);
432: }
433: rs = stmt.executeQuery();
434: while (rs.next()) {
435: String name = rs.getString(1);
436: result.add(name);
437: }
438: } catch (SQLException e) {
439: LogMgr.logWarning("OracleMetadata.getSnapshots()",
440: "Error accessing all_mviews", e);
441: // When we get an exception, most probably we cannot access the ALL_MVIEWS view.
442: // To avoid further (unnecessary) calls, we are disabling the support
443: // for snapshots
444: this .retrieveSnapshots = false;
445: result = Collections.emptySet();
446: } finally {
447: SqlUtil.closeAll(rs, stmt);
448: }
449: return result;
450: }
451:
452: public String getSnapshotSource(TableIdentifier tbl) {
453: if (!retrieveSnapshots) {
454: return StringUtil.EMPTY_STRING;
455: }
456: String result = null;
457: PreparedStatement stmt = null;
458: ResultSet rs = null;
459: String sql = "SELECT query FROM all_mviews WHERE owner = ? and mview_name = ?";
460:
461: try {
462: stmt = this .connection.getSqlConnection().prepareStatement(
463: sql);
464: stmt.setString(1, tbl.getSchema());
465: stmt.setString(2, tbl.getTableName());
466: rs = stmt.executeQuery();
467: if (rs.next()) {
468: result = rs.getString(1);
469: if (rs.wasNull()) {
470: result = "";
471: } else {
472: result = result.trim();
473: }
474:
475: if (!result.endsWith(";")) {
476: result = result + ";";
477: }
478: }
479: } catch (SQLException e) {
480: LogMgr.logWarning("OracleMetadata.getSnapshotSource()",
481: "Error accessing all_mviews", e);
482: this .retrieveSnapshots = false;
483: result = ExceptionUtil.getDisplay(e);
484: } finally {
485: SqlUtil.closeAll(rs, stmt);
486: }
487: return result;
488: }
489:
490: /**
491: * Close the statement object that was used in {@link #getColumns(String, String, String, String)}.
492: * This method should be called after closing the ResultSet obtained from that method.
493: */
494: public void columnsProcessed() {
495: SqlUtil.closeStatement(columnStatement);
496: columnStatement = null;
497: }
498:
499: private String getVarcharType(String type, int size, int semantics) {
500: StringBuilder result = new StringBuilder(25);
501: result.append(type);
502: result.append('(');
503: result.append(size);
504:
505: // Only apply this logic vor VARCHAR columns
506: // NVARCHAR (which might have been reported as type == VARCHAR) does not
507: // allow Byte/Char semantics
508: if (type.startsWith("VARCHAR")) {
509: if (alwaysShowCharSemantics
510: || semantics != this .defaultLengthSemantics) {
511: if (semantics == BYTE_SEMANTICS) {
512: result.append(" Byte");
513: } else if (semantics == CHAR_SEMANTICS) {
514: result.append(" Char");
515: }
516: }
517: }
518: result.append(')');
519: return result.toString();
520: }
521:
522: public String getSqlTypeDisplay(String dbmsName, int sqlType,
523: int size, int digits, int byteOrChar) {
524: String display = null;
525: if (sqlType == Types.VARCHAR) {
526: // Hack to get Oracle's VARCHAR2(xx Byte) or VARCHAR2(xxx Char) display correct
527: // Our own statement to retrieve column information in OracleMetaData
528: // will return the byte/char semantics in the field WB_SQL_DATA_TYPE
529: // Oracle's JDBC driver does not supply this information (because
530: // the JDBC standard does not define a column for this)
531: display = getVarcharType(dbmsName, size, byteOrChar);
532: } else {
533: display = SqlUtil.getSqlTypeDisplay(dbmsName, sqlType,
534: size, digits);
535: }
536: return display;
537: }
538:
539: }
|