001: /*
002: * OracleProcedureReader.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 workbench.db.JdbcProcedureReader;
017: import workbench.db.JdbcProcedureReader;
018: import workbench.db.NoConfigException;
019: import workbench.db.ProcedureDefinition;
020: import workbench.db.WbConnection;
021: import workbench.resource.Settings;
022: import workbench.sql.DelimiterDefinition;
023: import workbench.util.SqlUtil;
024:
025: /**
026: * A ProcedureReader to read the source of an Oracle procedure.
027: * Packages are handled properly. The Oracle JDBC driver
028: * reports the package name in the catalog column
029: * of the getProcedures() ResultSet.
030: * The method {@link #readProcedureSource(ProcedureDefinition)} the
031: * catalog definition of the ProcedureDefinition is checked. If it's not
032: * null it is assumed that this the definition is actually a package.
033: *
034: * @see workbench.db.JdbcProcedureReader
035: * @author support@sql-workbench.net
036: */
037: public class OracleProcedureReader extends JdbcProcedureReader {
038: public OracleProcedureReader(WbConnection conn) {
039: super (conn);
040: }
041:
042: private final StringBuilder PROC_HEADER = new StringBuilder(
043: "CREATE OR REPLACE ");
044:
045: public StringBuilder getProcedureHeader(String catalog,
046: String schema, String procname, int procType) {
047: return PROC_HEADER;
048: }
049:
050: public CharSequence getPackageSource(String owner,
051: String packageName) {
052: final String sql = "SELECT text \n" + "FROM all_source \n"
053: + "WHERE name = ? \n" + "AND owner = ? \n"
054: + "AND type = ? \n" + "ORDER BY line";
055:
056: StringBuilder result = new StringBuilder(1000);
057: PreparedStatement stmt = null;
058: ResultSet rs = null;
059:
060: String nl = Settings.getInstance()
061: .getInternalEditorLineEnding();
062: DelimiterDefinition delimiter = Settings.getInstance()
063: .getAlternateDelimiter(connection);
064:
065: try {
066: int lineCount = 0;
067: String lastline = null;
068:
069: synchronized (connection) {
070: stmt = this .connection.getSqlConnection()
071: .prepareStatement(sql);
072: stmt.setString(1, packageName);
073: stmt.setString(2, owner);
074: stmt.setString(3, "PACKAGE");
075: rs = stmt.executeQuery();
076: while (rs.next()) {
077: String line = rs.getString(1);
078: if (line != null) {
079: lineCount++;
080: if (lineCount == 1) {
081: result.append("CREATE OR REPLACE ");
082: }
083: result.append(line);
084: }
085: }
086: result.append(nl);
087: result.append(delimiter.getDelimiter());
088: result.append(nl);
089: result.append(nl);
090: lineCount = 0;
091:
092: stmt.clearParameters();
093: stmt.setString(1, packageName);
094: stmt.setString(2, owner);
095: stmt.setString(3, "PACKAGE BODY");
096: rs = stmt.executeQuery();
097: while (rs.next()) {
098: String line = rs.getString(1);
099: if (line != null) {
100: lineCount++;
101: if (lineCount == 1) {
102: result.append("CREATE OR REPLACE ");
103: }
104: result.append(line);
105: }
106: lastline = line;
107: }
108: }
109: result.append(nl);
110: } catch (Exception e) {
111: e.printStackTrace();
112: } finally {
113: SqlUtil.closeAll(rs, stmt);
114: }
115: return result;
116: }
117:
118: public void readProcedureSource(ProcedureDefinition def)
119: throws NoConfigException {
120:
121: if (def.getCatalog() != null) {
122: CharSequence source = getPackageSource(def.getSchema(), def
123: .getCatalog());
124: def.setSource(source);
125: def.setOraclePackage(true);
126: } else if (def.isOraclePackage()) {
127: CharSequence source = getPackageSource(def.getSchema(), def
128: .getProcedureName());
129: def.setSource(source);
130: } else {
131: super.readProcedureSource(def);
132: }
133: }
134: }
|