import java.io.*;
import java.sql.*;
import java.text.*;

public class DescribeStoredProcedures {
  Connection conn;

  public DescribeStoredProcedures() {
    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(
       "jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }
  }

  public static void main(String[] args) 
   throws Exception, IOException {
    String storedProcedureName = null;
    String schemaName          = null;
    if (args.length > 0) 
      schemaName          = args[0];
    if (args.length > 1) 
      storedProcedureName = args[1];
    new DescribeStoredProcedures().process(schemaName, storedProcedureName);
  }

  public void process(
   String schemaName, 
   String storedProcedureName) 
   throws SQLException {

    int rows                    = 0;
    ResultSet rslt              = null;
    Statement stmt              = null;
    String previous             = "~";
    String schemaPattern        = "%";
    String procedureNamePattern = "%";

    try {
      if (schemaName != null && !schemaName.equals("")) 
        schemaPattern = schemaName;
      if (storedProcedureName != null && !storedProcedureName.equals("")) 
        procedureNamePattern = storedProcedureName;
      stmt = conn.createStatement();
      rslt = stmt.executeQuery(
       "select type||' '||owner||'.'||name, line, text " + 
       "from   sys.dba_source " + 
       "where  type = 'FUNCTION' " + 
       "and    owner like '" + schemaPattern + "' " +
       "and    name  like '" + procedureNamePattern + "' " + 
       "union all " +
       "select type||' '||owner||'.'||name, line, text " + 
       "from   sys.dba_source " + 
       "where  type = 'PROCEDURE' " + 
       "and    owner like '" + schemaPattern + "' " +
       "and    name  like '" + procedureNamePattern + "' " + 
       "union all " +
       "select type||' '||owner||'.'||name, line, text " + 
       "from   sys.dba_source " + 
       "where  type = 'PACKAGE' " + 
       "and    owner like '" + schemaPattern + "' " +
       "and    name  like '" + procedureNamePattern + "' " + 
       "union all " +
       "select type||' '||owner||'.'||name, line, text " + 
       "from   sys.dba_source " + 
       "where  type = 'TYPE' " + 
       "and    owner like '" + schemaPattern + "' " +
       "and    name  like '" + procedureNamePattern + "' " + 
       "order by 1, 2");
      while (rslt.next()) {
        rows++;
        if (!rslt.getString(1).equals(previous)) {
          if (!previous.equals("~"))
          System.out.println("");
          previous = rslt.getString(1);
        }
        System.out.print(rslt.getString(3));
      }
      if (!previous.equals("~"))
        System.out.println("");
    }
    catch (SQLException e) {
      System.err.println("SQL Error: " + e.getMessage());
    }
    finally {
      if (rslt != null)  
        try { rslt.close();  } catch (SQLException ignore) { }
      if (stmt != null)  
        try { stmt.close();  } catch (SQLException ignore) { }
    }
  }

  protected void finalize() 
   throws Throwable {
    if (conn != null) 
      try { conn.close(); } catch (SQLException ignore) { }
    super.finalize();
  }
}