oracledatabasemetadata.java

数据库连接包括连接池。很有用的资料

    throws SQLException
  {
    connection.trace ("getMaxUserNameLength");
    return 30;
  }

  //----------------------------------------------------------------------

  /**
   * What's the database's default transaction isolation level?  The
   * values are defined in java.sql.Connection.
   *
   * @return the default isolation level 
   * @see Connection
   */
  public int getDefaultTransactionIsolation() 
    throws SQLException
  {
    connection.trace ("getDefaultTransactionIsolation");
    return Connection.TRANSACTION_READ_COMMITTED;
  }

  /**
   * Are transactions supported? If not, commit is a noop and the
   * isolation level is TRANSACTION_NONE.
   *
   * @return true if transactions are supported 
   */
  public boolean supportsTransactions() 
    throws SQLException
  {
    connection.trace ("supportsTransactions");
    return true;
  }

  /**
   * Does the database support the given transaction isolation level?
   *
   * @param level the values are defined in java.sql.Connection
   * @return true if so 
   * @see Connection
   */
  public boolean supportsTransactionIsolationLevel (int level)
       
    throws SQLException
  {
    connection.trace ("supportsTransactionIsolationLevel");
    return ((level == Connection.TRANSACTION_READ_COMMITTED) ||
				(level == Connection.TRANSACTION_SERIALIZABLE));
  }
  
  /**
   * Are both data definition and data manipulation statements
   * within a transaction supported?
   *
   * @return true if so 
   */
  public boolean supportsDataDefinitionAndDataManipulationTransactions ()
       
    throws SQLException
  {
    connection.trace ("supportsDataDefinitionAndDataManipulationTransactions");
    return true;
  }

  /**
   * Are only data manipulation statements within a transaction
   * supported?
   *
   * @return true if so
   */
  public boolean supportsDataManipulationTransactionsOnly()
       
    throws SQLException
  {
    connection.trace ("supportsDataManipulationTransactionsOnly");
    return true;
  }
  /**
   * Does a data definition statement within a transaction force the
   * transaction to commit?
   *
   * @return true if so 
   */
  public boolean dataDefinitionCausesTransactionCommit()
       
    throws SQLException
  {
    connection.trace ("dataDefinitionCausesTransactionCommit");
    return true;
  }
  /**
   * Is a data definition statement within a transaction ignored?
   *
   * @return true if so 
   */
  public boolean dataDefinitionIgnoredInTransactions()
       
    throws SQLException
  {
    connection.trace ("dataDefinitionIgnoredInTransactions");
    return false;
  }


  /**
   * Get a description of stored procedures available in a
   * catalog.
   *
   * <P>Only procedure descriptions matching the schema and
   * procedure name criteria are returned.  They are ordered by
   * PROCEDURE_SCHEM, and PROCEDURE_NAME.
   *
   * <P>Each procedure description has the the following columns:
   *  <OL>
   *	<LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
   *	<LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
   *	<LI><B>PROCEDURE_NAME</B> String => procedure name
   *  <LI> reserved for future use
   *  <LI> reserved for future use
   *  <LI> reserved for future use
   *	<LI><B>REMARKS</B> String => explanatory comment on the procedure
   *	<LI><B>PROCEDURE_TYPE</B> short => kind of procedure:
   *      <UL>
   *      <LI> procedureResultUnknown - May return a result
   *      <LI> procedureNoResult - Does not return a result
   *      <LI> procedureReturnsResult - Returns a result
   *      </UL>
   *  </OL>
   *
   * @param catalog a catalog name; "" retrieves those without a
   * catalog; null means drop catalog name from the selection criteria
   * @param schemaPattern a schema name pattern; "" retrieves those
   * without a schema
   * @param procedureNamePattern a procedure name pattern 
   * @return ResultSet each row is a procedure description 
   * @see #getSearchStringEscape 
   */
  public synchronized
    ResultSet getProcedures(String catalog, String schemaPattern,
			    String procedureNamePattern) 
    throws SQLException
  {
    /*
     * Fixes for bugs 739937 and 773128:
     * As a result of these two bugs, getProcedures now does the following,
     * that it didn't do before:
     * -  Assemble the query from separate parts so that we get information
     *    about both packaged and standalone subprograms.
     * -  Treats the catalog parameter as the package name, both on input and
     *    output.
     * -  Take into account "" and null for all parameters.
     * -  Use UNION ALL instead of UNION, which insures that we get information
     *    about overloaded subprograms.
     */

    // Returns information for standalone procedures and functions     
    String standaloneQuery = 
      "SELECT\n" +
      "  -- Standalone procedures and functions\n" +
      "  NULL AS procedure_cat,\n" +
      "  owner AS procedure_schem,\n" +
      "  object_name AS procedure_name,\n" +
      "  NULL,\n" +
      "  NULL,\n" +
      "  NULL,\n" +
      "  'Standalone procedure or function' AS remarks,\n" +
      "  DECODE(object_type, 'PROCEDURE', 1,\n" +
      "                      'FUNCTION', 2,\n" +
      "                      0) AS procedure_type\n" +
      "FROM all_objects\n" +
      "WHERE (object_type = 'PROCEDURE' OR object_type = 'FUNCTION')\n" +
      "  AND owner LIKE ? ESCAPE '/'\n" +
      "  AND object_name LIKE ? ESCAPE '/'\n";

    // Returns information for packaged procedures with no arguments
    String packagedProcsNoArgsSelect = 
      "SELECT\n" +
      "  -- Packaged procedures with no arguments\n" +
      "  package_name AS procedure_cat,\n" +
      "  owner AS procedure_schem,\n" +
      "  object_name AS procedure_name,\n" +
      "  NULL,\n" +
      "  NULL,\n" +
      "  NULL,\n" +
      "  'Packaged procedure' AS remarks,\n" +
      "  1 AS procedure_type\n" +
      "FROM all_arguments\n" +
      "WHERE argument_name IS NULL\n" +
      "  AND data_type IS NULL\n" +
      "  AND ";

    // Returns information for packaged procedures with arguments
    String packagedProcsArgsSelect =
      "SELECT\n" +
      "  -- Packaged procedures with arguments\n" +
      "  package_name AS procedure_cat,\n" +
      "  owner AS procedure_schem,\n" +
      "  object_name AS procedure_name,\n" +
      "  NULL,\n" +
      "  NULL,\n" +
      "  NULL,\n" +
      "  'Packaged procedure' AS remarks,\n" +
      "  1 AS procedure_type\n" +
      "FROM all_arguments\n" +
      "WHERE argument_name IS NOT NULL\n" +
      "  AND position = 1\n" +
      "  AND position = sequence\n" +
      "  AND ";

    // Returns information for packaged functions
    String packagedFunctionsSelect =
      "SELECT\n" +
      "  -- Packaged functions\n" +
      "  package_name AS procedure_cat,\n" +
      "  owner AS procedure_schem,\n" +
      "  object_name AS procedure_name,\n" +
      "  NULL,\n" +
      "  NULL,\n" +
      "  NULL,\n" +
      "  'Packaged function' AS remarks,\n" +
      "  2 AS procedure_type\n" +
      "FROM all_arguments\n" +
      "WHERE argument_name IS NULL\n" +
      "  AND in_out = 'OUT'\n" +
      "  AND ";

    // Different WHERE clauses
    String catalogSpecifiedWhere =
      "package_name LIKE ? ESCAPE '/'\n" +
      "  AND owner LIKE ? ESCAPE '/'\n" +
      "  AND object_name LIKE ? ESCAPE '/'\n";
    String catalogNotSpecifiedWhere =
      "package_name IS NOT NULL\n" +
      "  AND owner LIKE ? ESCAPE '/'\n" +
      "  AND object_name LIKE ? ESCAPE '/'\n";

    // Final ORDER BY clause
    String orderBy = "ORDER BY procedure_schem, procedure_name\n";

    PreparedStatement s = null;
    String finalQuery = null;

    // Bind values to be used for schema and procedure name
    String schemaBind = schemaPattern;
    if (schemaPattern == null)
      schemaBind = "%";
    else if (schemaPattern.equals(""))
      schemaBind = getUserName().toUpperCase();

    String procedureNameBind = procedureNamePattern;
    if (procedureNamePattern == null)
      procedureNameBind = "%";
    else if (procedureNamePattern.equals(""))
      DBError.throwSqlException(DBError.EOJ_INVALID_NAME_PATTERN);

    if (catalog == null)
    { 
      // null catalog.  This means return information about all objects, 
      // whether passed in or not.
      finalQuery = standaloneQuery +
	  "UNION ALL " + packagedProcsNoArgsSelect + catalogNotSpecifiedWhere +
	  "UNION ALL " + packagedProcsArgsSelect + catalogNotSpecifiedWhere +
          "UNION ALL " + packagedFunctionsSelect + catalogNotSpecifiedWhere +
                     orderBy;

      connection.trace("getProcedures Final SQL statement:\n" + finalQuery);

      s = connection.prepareStatement(finalQuery);
      s.setString(1, schemaBind);
      s.setString(2, procedureNameBind);
      s.setString(3, schemaBind);
      s.setString(4, procedureNameBind);
      s.setString(5, schemaBind);
      s.setString(6, procedureNameBind);
      s.setString(7, schemaBind);
      s.setString(8, procedureNameBind);
    }
    else if (catalog.equals(""))
    {
      // Empty string passed in for catalog.  This means return information
      // only for standalone objects.
      finalQuery = standaloneQuery;
      
      connection.trace("getProcedures Final SQL statement:\n" + finalQuery);

      s = connection.prepareStatement(finalQuery);
      s.setString(1, schemaBind);
      s.setString(2, procedureNameBind);
    }
    else
    {
      // Pattern passed in for catalog.  Use it.
      finalQuery = packagedProcsNoArgsSelect + catalogSpecifiedWhere +
	  "UNION ALL " + packagedProcsArgsSelect + catalogSpecifiedWhere +
	  "UNION ALL " + packagedFunctionsSelect + catalogSpecifiedWhere +
	             orderBy;

      connection.trace("getProcedures Final SQL statement:\n" + finalQuery);

      s = connection.prepareStatement(finalQuery);
      s.setString(1, catalog);
      s.setString(2, schemaBind);
      s.setString(3, procedureNameBind);
      s.setString(4, catalog);
      s.setString(5, schemaBind);
      s.setString(6, procedureNameBind);
      s.setString(7, catalog);
      s.setString(8, schemaBind);
      s.setString(9, procedureNameBind);
    }

    // Execute the query and return the ResultSet.
    OracleResultSetImpl rs = (OracleResultSetImpl)s.executeQuery ();
    rs.close_statement_on_close = true;
    return rs;
  }

  /**
   * PROCEDURE_TYPE - May return a result.
   */
  int procedureResultUnknown	= 0;

  /**
   * PROCEDURE_TYPE - Does not return a result.
   */
  int procedureNoResult		= 1;

  /**
   * PROCEDURE_TYPE - Returns a result.
   */
  int procedureReturnsResult	= 2;

  /**
   * Get a description of a catalog's stored procedure parameters
   * and result columns.
   *
   * <P>Only descriptions matching the schema, procedure and
   * parameter name criteria are returned.  They are ordered by
   * PROCEDURE_SCHEM and PROCEDURE_NAME. Within this, the return value,
   * if any, is first. Next are the parameter descriptions in call
   * order. The column descriptions follow in column number order.
   *
   * <P>Each row in the ResultSet is a parameter desription or
   * column description with the following fields:
   *  <OL>
   *	<LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
   *	<LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
   *	<LI><B>PROCEDURE_NAME</B> String => procedure name
   *	<LI><B>COLUMN_NAME</B> String => column/parameter name 
   *	<LI><B>COLUMN_TYPE</B> Short => kind of column/parameter:
   *      <UL>
   *      <LI> procedureColumnUnknown - nobody knows
   *      <LI> procedureColumnIn - IN parameter
   *      <LI> procedureColumnInOut - INOUT parameter
   *      <LI> procedureColumnOut - OUT parameter
   *      <LI> procedureColumnReturn - procedure return value
   *      <LI> procedureColumnResult - result column in ResultSet
   *      </UL>
   *  <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
   *	<LI><B>TYPE_NAME</B> String => SQL type name
   *	<LI><B>PRECISION</B> int => precision
   *	<LI><B>LENGTH</B> int => length in bytes of data
   *	<LI><B>SCALE</B> short => scale
   *	<LI><B>RADIX</B> short => radix
   *	<LI><B>NULLABLE</B> short => can it contain NULL?
   *      <UL>
   *      <LI> procedureNoNulls - does not allow NULL values
   *      <LI> procedureNullable - allows NULL values
   *      <LI> procedureNullableUnknown - nullability unknown
   *      </UL>
   *	<LI><B>REMARKS</B> String => comment describing parameter/column
   *  </OL>
   *
   * <P><B>Note:</B> Some databases may not return the column
   * descriptions for a procedure. Additional columns beyond
   * REMARKS can be defined by the database.
   *
   * @param catalog a catalog name; "" retrieves those without a
   * catalog; null means drop catalog name from the selection criteria
   * @param schemaPattern a schema name pattern; "" retrieves those
   * without a schema 
   * @param procedureNamePattern a procedure name pattern 
   * @param columnNamePattern a column name pattern 
   * @return ResultSet each row is a stored procedure parameter or 
   *      column description 
   * @see #getSearchStringEscape 
   */
  public synchronized
    ResultSet getProcedureColumns(String catalog,
				  String schemaPattern,
				  String procedureNamePattern, 
				  String columnNamePattern) 
    throws SQLException
  {
    /*
     * Fixes for bugs 739937 and 773128:
     * As a result of these two bugs, getProcedureColumns now does the
     * following, that it didn't do before:
     * -  Assemble the query from separate parts so that we get correct
     *    information.
     * -  Treats the catalog parameter as the package name, both on input and
     *    output.
     * -  Extra DECODE for column_type, so that return values are reported
     *    properly.
     * -  Take into account "" and null for all parameters.
     */

    String baseQuery =
      "SELECT package_name AS procedure_cat,\n" +
      "       owner AS procedure_schem,\n" +
      "       object_name AS procedure_name,\n" +
      "       argument_name AS column_name,\n" +
      "       DECODE(position, 0, 5,\n" +
      "                        DECODE(in_out, 'IN', 1,\n" +
      "                                       'OUT', 4,\n" +
      "                                       'IN/OUT', 2,\n" +
      "                                       0)) AS column_type,\n" +
      "       DECODE (data_type, 'CHAR', 1,\n" +
      "                          'VARCHAR2', 12,\n" +
      "                          'NUMBER', 3,\n" +
      "                          'LONG', -1,\n" +
      "                          'DATE', 93,\n" +
      "                          'RAW', -3,\n" +
      "                          'LONG RAW', -4,\n" +
      "                          1111) AS data_type,\n" +
      "       data_type AS type_name,\n" +
      "       DECODE (data_precision, NULL, data_length,\n" +
      "                               data_precision) AS precision,\n" +
      "       data_length AS length,\n" +
      "       data_scale AS scale,\n" +
      "       10 AS radix,\n" +
      "       1 AS nullable,\n" +
      "       NULL AS remarks,\n" +
      "       sequence,\n" +
      "       overload,\n" +
      "       default_value\n" +
      " FROM all_arguments\n" +
      "WHERE owner LIKE ? ESCAPE '/'\n" +
      "  AND object_name LIKE ? ESCAPE '/'\n";

    String catalogSpecifiedWhere = 
      "  AND package_name LIKE ? ESCAPE '/'\n";
    String catalogEmptyWhere =
      "  AND package_name IS NULL\n";

    String columnSpecifiedWhere =
      "  AND argument_name LIKE ? ESCAPE '/'\n";
    String columnNotSpecifiedWhere =
      "  AND (argument_name LIKE ? ESCAPE '/'\n" +
      "       OR (argument_name IS NULL\n" +    // get return values, but not
      "           AND data_type IS NOT NULL))\n"; // pkgd. procs with no args

      
    String orderBy =
      "ORDER BY procedure_schem, procedure_name, overload, sequence\n";

    String finalQuery = null;
    PreparedStatement s = null;
    String columnWhere = null;

    // Bind values to be used for schema, procedure and argument name
    String schemaBind = schemaPattern;
    if (schemaPattern == null)
      schemaBind = "%";
    else if (schemaPattern.equals(""))
      schemaBind = getUserName().toUpperCase();

    String procedureNameBind = procedureNamePattern;
    if (procedureNamePattern == null)
      procedureNameBind = "%";
    else if (procedureNamePattern.equals(""))
      DBError.throwSqlException(DBError.EOJ_INVALID_NAME_PATTERN);

    String columnNameBind = columnNamePattern;
    if ((columnNamePattern == null) || (columnNamePattern.equals("%")))
    {
      columnNameBind = "%";
      columnWhere = columnNotSpecifiedWhere;
    }
    else if (columnNamePattern.equals(""))
      DBError.throwSqlException(DBError.EOJ_INVALID_NAME_PATTERN);
    else
      columnWhere = columnSpecifiedWhere;

    if (catalog == null)
    {