apex 自动导出application

可以采用以下命令行自动导出或者备份apex应用程序：

java -classpath ojdbc14.jar:$ORACLE_HOME/apex/utilities oracle.apex.APEXExport -db localhost:1521:orcl -user scott -password 123456 -applicationid 100

Note:

1. APEXExport 是存在于$ORACLE_HOME/apex/utilities目录下的APEX自带的文件，用于export应用程序。也可以使用apex解压目录下的utilities目录下的相同文件

2. APEXExport 用到了oracle.jdbc.driver.OracleCallableStatement需要使用jdbc驱动程序ojdbc14.jar，ojdbc5.jar或者ojdbc6.jar均不可用。

APEXExport 源码如下：

package oracle.apex;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.sql.CLOB;

public class APEXExport
{
  Connection gConn;
  private final String gStmt = "select application_id, application_name from apex_applications order by application_id";
  private final String gStmtInstance = "select id, name from wwv_flows where security_group_id <> 10 order by id";
  private final String gStmtSetSGID = "begin wwv_flow_api.set_security_group_id(p_security_group_id=>?); end;";
  private final String gStmtGetSGID = "select v('FLOW_SECURITY_GROUP_ID') from dual";
  public boolean debug = false;
  public boolean skipDate = false;
  public boolean instance = false;
  public boolean savedReports = false;

  private void ExportFiles(BigDecimal paramBigDecimal1, BigDecimal paramBigDecimal2, String paramString)
    throws SQLException, IOException
  {
    Object localObject1;
    Object localObject2;
    Object localObject3;
    BigDecimal localBigDecimal;
    Date localDate;
    if (this.instance)
    {
      localObject1 = null;
      localObject2 = this.gConn.prepareStatement("select id, name from wwv_flows where security_group_id <> 10 order by id");

      localObject3 = ((PreparedStatement)localObject2).executeQuery();

      while (((ResultSet)localObject3).next()) {
        localBigDecimal = ((ResultSet)localObject3).getBigDecimal(1);
        localObject1 = ((ResultSet)localObject3).getString(2);

        System.out.println("Exporting Application " + localBigDecimal + ":'" + (String)localObject1 + "' ");

        if (this.debug) {
          localDate = new Date();
          System.out.println(" Start " + localDate);
        }

        ExportFile(localBigDecimal);
        localDate = new Date();
        System.out.println("  Completed at " + localDate);
      }

      ((ResultSet)localObject3).close();
      ((PreparedStatement)localObject2).close();
    }
    else if ((paramBigDecimal2 != null) && (paramBigDecimal2.longValue() != 0L))
    {
      localObject1 = new BigDecimal(0);
      localObject2 = null;
      localObject3 = this.gConn.prepareStatement("begin wwv_flow_api.set_security_group_id(p_security_group_id=>?); end;");
      ((PreparedStatement)localObject3).setBigDecimal(1, paramBigDecimal2);

      ((PreparedStatement)localObject3).executeUpdate();
      ((PreparedStatement)localObject3).close();

      localObject3 = this.gConn.prepareStatement("select v('FLOW_SECURITY_GROUP_ID') from dual");
      ResultSet localResultSet = ((PreparedStatement)localObject3).executeQuery();
      while (localResultSet.next()) {
        localObject1 = localResultSet.getBigDecimal(1);
      }
      localResultSet.close();
      ((PreparedStatement)localObject3).close();

      if (!((BigDecimal)localObject1).equals(paramBigDecimal2)) {
        System.out.println("Invalid Workspace ID '" + paramBigDecimal2 + "' for User '" + paramString + "'");
        System.exit(1);
      }

      localObject3 = this.gConn.prepareStatement("select application_id, application_name from apex_applications order by application_id");

      localResultSet = ((PreparedStatement)localObject3).executeQuery();

      while (localResultSet.next()) {
        localBigDecimal = localResultSet.getBigDecimal(1);
        localObject2 = localResultSet.getString(2);

        System.out.println("Exporting Application " + localBigDecimal + ":'" + (String)localObject2 + "' ");

        if (this.debug) {
          localDate = new Date();
          System.out.println(" Start " + localDate);
        }

        ExportFile(localBigDecimal);
        localDate = new Date();
        System.out.println("  Completed at " + localDate);
      }

      localResultSet.close();
      ((PreparedStatement)localObject3).close();
    }
    else {
      System.out.println("Exporting application " + paramBigDecimal1);

      if (this.debug) {
        localDate = new Date();
        System.out.println(" Start " + localDate);
      }

      ExportFile(paramBigDecimal1);
      localDate = new Date();
      System.out.println("  Completed at " + localDate);
    }
  }

  public void ExportFile(BigDecimal paramBigDecimal)
    throws SQLException, IOException
  {
    int i = 1;

    OracleCallableStatement localOracleCallableStatement = (OracleCallableStatement)this.gConn.prepareCall("begin ? := wwv_flow_utilities.export_application_to_clob(?, ?); end;");

    if (this.savedReports)
      localOracleCallableStatement.setString(3, "Y");
    else {
      localOracleCallableStatement.setString(3, "N");
    }

    localOracleCallableStatement.setBigDecimal(2, paramBigDecimal);
    localOracleCallableStatement.registerOutParameter(1, 2005);
    localOracleCallableStatement.execute();

    CLOB localCLOB = localOracleCallableStatement.getCLOB(1);
    Reader localReader = localCLOB.getCharacterStream();

    int j = localCLOB.getBufferSize();
    char[] arrayOfChar = new char[j];
    int k = -1;

    File localFile = new File("f" + paramBigDecimal + ".sql");
    localFile.delete();
    localFile.createNewFile();

    FileOutputStream localFileOutputStream = new FileOutputStream(localFile);

    OutputStreamWriter localOutputStreamWriter = new OutputStreamWriter(localFileOutputStream, "UTF-8");

    BufferedReader localBufferedReader = new BufferedReader(localReader);
    String str = null;

    while ((str = localBufferedReader.readLine()) != null)
    {
      str = str + "\n";

      if (i != 0) {
        if (str.indexOf("set define") == 0) {
          i = 0;
          localOutputStreamWriter.write(str, 0, str.length());
          continue;
        }

      }

      if ((!this.skipDate) || (str.indexOf("--   Date and Time:") != 0)) {
        localOutputStreamWriter.write(str, 0, str.length());
      }

    }

    localOutputStreamWriter.flush();

    if (this.debug) {
      System.out.println(" Wrote " + localFile.length() + " bytes to " + localFile.getAbsolutePath());
    }

    localReader.close();
    localOutputStreamWriter.close();
    localFileOutputStream.close();
    localOracleCallableStatement.close();
  }

  private static void usage()
  {
    System.out.println("Usage APEXExport -db  -user  -password  -applicationid  -workspaceid -instance -skipExportDate -expSavedReports -debug  ");
    System.out.println("    -db:              Database connect url in JDBC format ");
    System.out.println("    -user:            Database username");
    System.out.println("    -password :       Database password");
    System.out.println("    -applicationid :  ID for application to be exported");
    System.out.println("    -workspaceid :    Workspace ID for which all applications to be exported");
    System.out.println("    -instance :       Export all applications");
    System.out.println("    -skipExportDate : Exclude export date from application export files");
    System.out.println("    -expSavedReports: Export all user saved interactive reports");
    System.out.println("    ");
    System.out.println("    Application Example: ");
    System.out.println("       APEXExport -db candy.us.oracle.com:1521:ORCL -user scott -password tiger -applicationid 31500 ");
    System.out.println("    Workspace  Example: ");
    System.out.println("       APEXExport -db candy.us.oracle.com:1521:ORCL -user scott -password tiger -workspaceid 9999 ");
    System.out.println("    Instance Example:");
    System.out.println("       APEXExport -db candy.us.oracle.com:1521:ORCL -user flows_020200 -password apex -instance ");
    System.exit(1);
  }

  public static void main(String[] paramArrayOfString)
    throws Exception
  {
    String str1 = null;
    String str2 = null;
    String str3 = null;
    BigDecimal localBigDecimal1 = null;
    BigDecimal localBigDecimal2 = null;
    APEXExport localAPEXExport = new APEXExport();

    for (int i = 0; i < paramArrayOfString.length; i++) {
      if (localAPEXExport.debug) {
        System.out.println("Parameter:" + paramArrayOfString[i]);
      }
      if (paramArrayOfString[i].equalsIgnoreCase("-db")) {
        i++; str1 = paramArrayOfString[i];
      } else if (paramArrayOfString[i].equalsIgnoreCase("-user")) {
        i++; str2 = paramArrayOfString[i];
      } else if (paramArrayOfString[i].equalsIgnoreCase("-password")) {
        i++; str3 = paramArrayOfString[i];
      } else if (paramArrayOfString[i].equalsIgnoreCase("-workspaceid")) {
        i++; localBigDecimal2 = new BigDecimal(paramArrayOfString[i]);
      } else if (paramArrayOfString[i].equalsIgnoreCase("-applicationid")) {
        i++; localBigDecimal1 = new BigDecimal(paramArrayOfString[i]);
      } else if (paramArrayOfString[i].equalsIgnoreCase("-debug")) {
        localAPEXExport.debug = true;
      } else if (paramArrayOfString[i].equalsIgnoreCase("-skipExportDate")) {
        localAPEXExport.skipDate = true;
      } else if (paramArrayOfString[i].equalsIgnoreCase("-expSavedReports")) {
        localAPEXExport.savedReports = true;
      } else if (paramArrayOfString[i].equalsIgnoreCase("-instance")) {
        localAPEXExport.instance = true;
      }
      else {
        usage();
      }
    }
    if (localAPEXExport.debug) {
      System.out.println(str1);
      System.out.println(str2);
      System.out.println(str3);
      System.out.println(localBigDecimal2);
      System.out.println(localBigDecimal1);
      System.out.println(localAPEXExport.skipDate);
      System.out.println(localAPEXExport.savedReports);
      System.out.println(localAPEXExport.instance);
    }

    if ((str1 == null) || (str2 == null) || (str3 == null) || ((localBigDecimal1 != null) && (localBigDecimal1.longValue() == 0L) && (localBigDecimal2 != null) && (localBigDecimal2.longValue() == 0L)))
    {
      usage();
    }

    DriverManager.registerDriver(new OracleDriver());
    localAPEXExport.gConn = DriverManager.getConnection("jdbc:oracle:thin:@" + str1, str2, str3);

    localAPEXExport.gConn.setAutoCommit(true);

    localAPEXExport.ExportFiles(localBigDecimal1, localBigDecimal2, str2);

    localAPEXExport.gConn.close();
  }
}

参考文献：

【1】http://ruepprich.wordpress.com/2011/07/15/exporting-an-apex-application-via-command-line/
【2】http://myoracleworld.hobby-electronics.net/APEX-APEXexport.html