CodeSmith Community

    public string GetCommandText(string connectionString, CommandSchema command)
    {
      int period = command.Name.IndexOf(".");
      string name = command.Name;
      string procName = command.Name;
      string type = command.ExtendedProperties["CS_ObjectType"].Value.ToString();
     
      if (type == "PACKAGE") {
        type = "PACKAGE BODY";
        name = command.Name.Substring(0, period );
        procName = command.Name.Substring(period + 1, command.Name.Length - period - 1);
      }
     
        string sql = string.Format(
          @"SELECT
              TEXT
            FROM
              DBA_SOURCE
              , (
                SELECT
                  LINE
                FROM
                  DBA_SOURCE
                WHERE (TRIM(REPLACE(UPPER(TEXT), ' ', '')) LIKE ('PROCEDURE{1}%') OR TRIM(REPLACE(UPPER(TEXT), ' ', '')) LIKE ('FUNCTION{1}%'))
                  AND NAME = '{0}'
                  AND TYPE = '{2}') BEGINLINE
              , (
                  SELECT
                    LINE
                  FROM
                    DBA_SOURCE
                  WHERE (TRIM(REPLACE(UPPER(TEXT), ' ', '')) LIKE ('END{1};%') OR TRIM(REPLACE(UPPER(TEXT), ' ', '')) LIKE ('END;%'))
                    AND NAME = '{0}'
                    AND TYPE = '{2}'
                ) ENDLINE
            WHERE NAME = '{0}'
              AND TYPE = '{2}'
              AND DBA_SOURCE.LINE BETWEEN BEGINLINE.LINE AND ENDLINE.LINE
          "
        , name
        , procName
        , type
        );
       
        using (OracleConnection conn = new OracleConnection(connectionString)) {
          conn.Open();
          using (OracleCommand cmd = new OracleCommand(sql, conn)) {
            using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
              StringBuilder source = new StringBuilder();
              try{
                while (reader.Read()) {
                  source.Append(reader["TEXT"].ToString().Trim() + Environment.NewLine);
                }
              }catch (Exception e){
                return e.Message + Environment.NewLine + sql;
              }
              return source.ToString();
            }
          }
        }
      //throw new NotImplementedException("Retrieval of command text has not yet been implemented.");
    }