Oracle APEX installation with ORDS

Oracle APEX installation with ORDS

在開始之前，先介紹下Oracle Application Express（APEX）。
Oracle Application Express—APEX for short, or by its former name HTML DB — is a declarative, Web-based RAD (Rapid Application development) tool. It is used to develop native Web-based, data centric applications. It requires no software to install on the client.
自2004年開始，Oracle已經開始這一款產品的開發，現在已升級到5.0。細心的人估計以前注意到，Oracle11g中自帶了APEX開頭的賬戶，它們是APEX內置schema, 並且已經綁定到Oracle安裝中。也就是說，在你買了Oracle數據庫情況下，不要額外的License,就可以放心使用APEX。
出了免費外，它的優勢很明顯，
1，一切基於web, 架構在Oracle 數據庫上， 獨立於軟硬件環境
2，快速開發，極大縮短開發周期，支持html,css, javascript
2，相比OBIEE，SAPBO這樣重量級BI報表工具，它更加靈活，可以作為輕量級的BI解決方案
至於我為甚用APEX，一句話領導不給力，不想買主流的 還讓我們自己找免費的開發工具做報表，還要專業。擦，想讓馬兒跑，還不給喂草><||
還好，在以前的公司，接觸過著APEX，當時還是版本3。雖然當時只是略懂皮毛，這回在選工具方面給了極大的幫助。 接下來，幾個月，開始了死磕APEX的苦逼日子。
說了那麼多廢話，開始干正事，准備安裝！
1，建立數據庫環境， 創建兩個表空間， 用於安裝APEX， 以便日後的管理。


CREATE TABLESPACE APEX datafile‘/u01/app/oracle/oradata/APEX/apex.dbf’
SIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLESPACE APEX_FILES datafile‘/u01/app/oracle/oradata/APEX/apex_files.dbf’
SIZE 500M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;


2，下載解壓APEX 安裝包， 具體下載地址
http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html
3， 解壓完後， 切換到解壓後的目錄，並以sys登陸數據庫。 這一步目錄切換很重要，在APEX安裝過程中， 路徑用的是相對路徑，如果開始沒切換目錄的話， 過程中， 會報文件找不到的錯誤
4， 安裝APEX, 命令如下


Full development environment. Run apexins.sql passingthe following four arguments in the order shown:
@apexins.sqltablespace_apex tablespace_files tablespace_tempimages
Where:
tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
tablespace_files is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp is the name of the temporary tablespace or tablespace group.
images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.

我采用的是開發環境的安裝， 當然，在產品環境，可以選擇Runtime environment 模式安裝， sql 腳本換成 @apxrtins.sql

之前創建的兩個表空間將tablespace_apex, tablespace_files 替換如下：

 

@apexins.sqlAPEX APEX_FILES TEMP /i/

這一步會花一段時間安裝完如果一切安裝順利的話， 就會出現和截圖一樣

 

當安裝完後， 一下三個schema 會被創建，有空的話，可以查看驗證下

■ APEX_050000 - The account that owns the Oracle Application Express schema and

metadata.

■ FLOWS_FILES - The account that owns the Oracle Application Express uploaded

files.

■ APEX_PUBLIC_USER - The minimally privileged account is used for Oracle

Application Express configuration with Oracle REST Data Services or Oracle

HTTP Server and mod_plsql.

5， 運行apxchpwd.sql, 它的作用如下， 就是設置APEX的超級管理員。 運行完後， 重啟數據庫。

Running the apxchpwd.sql script enables your to create or update your Instance

Administrator account. You must run the apxchpwd.sql script in the following

scenarios:

■ New Oracle Application Express installations - Run apxchpwd.sql to create an

Instance Administrator account and password.

■ Converting of a runtime environment to a development environment - Run

apxchpwd.sql to change the Instance Administrator account password.

■ Changing Your Instance Administrator Password -Run apxchpwd.sql to change

the password for an existing Instance Administrator account.

■ Unlocking Your Instance Administrator Account - Run apxchpwd.sql to unlock

an existing Instance Administrator account.

6， 設置APEX_PUBLIC_USER, 為了下面配置ORDS 用

解鎖

SQL>ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK

重置密碼

SQL>alter user apex_public_user identified by apex_public_user

7， 安裝配置Oracle Rest Data Services(ORDS)

下載地址：http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/

index.html

下載完後， 解壓指定好的安裝目錄。 解壓完後， 你可以發現ORDS這個就是個war包

7.1 Disabling Oracle XML DB Protocol Server

 

SQL>EXEC DBMS_XDB.SETHTTPPORT(0);

SQL>

7.2 configure RESTful Services in Oracle Application Express

SQL>@apex_rest_config.sql

 

When configuring RESTful Services in OracleApplication Express, it creates two

new database accounts.

■ APEX_LISTENER - The account used to query RESTfulServices definitions

stored in Oracle Application Express.

■ APEX_REST_PUBLIC_USER - The account used whencalling RESTful Services

definitions stored in Oracle Application Express.

 

 

SQL>@apex_rest_config.sql

 

 

Entera password for the APEX_LISTENER user []

Entera password for the APEX_REST_PUBLIC_USER user []

 

...createAPEX_LISTENER user

...createAPEX_REST_PUBLIC_USER user

SQL>

SQL>

 

7.3 Granting Connect Privileges， 這一步是賦連接的權限給web 主機， 使它能能夠與ORDS進行連接

 

SQL>

SQL>DECLARE

2 ACL_PATH VARCHAR2(4000);

3 BEGIN

4 --Look for the ACL currently assigned to '*' and give APEX_050000

5 --the "connect" privilege if APEX_050000 does not have the privilegeyet.

6 SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS

7 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

8 IFDBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050000',

9 'connect') IS NULL THEN

10 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,

11 'APEX_050000', TRUE, 'connect');

12 ENDIF;

13 EXCEPTION

14 --When no ACL has been assigned to '*'.

15 WHENNO_DATA_FOUND THEN

16 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',

17 'ACLthat lets power users to connect to everywhere',

18 'APEX_050000', TRUE, 'connect');

19 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');

20 END;

21 /

COMMIT;

SQL>SQL>

7.3 Configuring Oracle Application Express Images

 

Copy the contents ofthe /images folder to /webapps/i/.

Where:

– is the directory location of the Oracle Application Expressdistribution.

– is the folder where Apache Tomcat is installed.

7.4 Configuring Oracle REST Data Services

a, copyords.war from ordspackage to tomcat webapps directory.

b, configure REST DATA Services

cd ..//webapps

 

drwxrwxrwx 14 oracleoinstall 4096 Mar 27 14:04 docs

drwxrwxrwx 7 oracle oinstall 4096 Mar 27 14:04 examples

drwxrwxrwx 5 oracle oinstall 4096 Mar 27 14:04 host-manager

drwxrwxrwx 33 oracleoinstall 40960 May 11 02:56 i

drwxrwxrwx 5 oracle oinstall 4096 Mar 27 14:04 manager

-rw-r--r-- 1 oracle oinstall 28491706 May 11 20:17ords.war

drwxrwxrwx 3 oracle oinstall 4096 Mar 27 14:04 ROOT

7.5 配置ORDS連接

　　　

 

[oracle@localhostwebapps]$ java -jar ords.war setup

Jun 09, 201512:26:03 AM oracle.dbtools.common.config.file.ConfigurationFolderlogConfigFolder

INFO: Usingconfiguration folder: /tmp/apex

Enter the name ofthe database server [localhost]:

Enter the databaselisten port [1521]:

Enter 1 to specifythe database service name, or 2 to specify the database SID [1]:2

Enter the databaseSID [xe]:yang01

Enter the databaseuser name [APEX_PUBLIC_USER]:apex_public_user

Enter the databasepassword for apex_public_user:

Confirm password:

Enter 1 to enterpasswords for the RESTful Services database users(APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used forapex_public_user or, 3 to skip this step [1]:

Enter the databasepassword for APEX_LISTENER:

Confirm password:

Enter the databasepassword for APEX_REST_PUBLIC_USER:

Confirm password:

Jun 09, 201512:27:19 AM oracle.dbtools.common.config.file.ConfigurationFiles update

INFO: Updatedconfigurations: defaults, apex, apex_al, apex_rt

[oracle@localhostwebapps]$

 

啟用Tomcat，訪問報錯，已經檢查過，apex那3個數據賬號並沒有問題。

查了好久，注意到了頁面這一行字，此實例使用……, 與上面配置默認給的路徑

INFO: Using configuration folder: /tmp/apex，並不是一個路徑。

 

既然這樣，只能指定一個路徑專門放配置信息

[oracle@localhostwebapps]$ java -jar ords.war configdir

Jun 09, 201512:55:30 AM oracle.dbtools.common.config.cmds.ConfigDir execute

INFO: The config.dirsetting is not set

[oracle@localhostwebapps]$ java -jar ords.war configdir/u01/app/apache-tomcat-7.0.61/webapps/ordsconfig

Jun 09, 2015 1:01:57AM oracle.dbtools.common.config.cmds.ConfigDir execute

INFO: Set config.dirto /u01/app/apache-tomcat-7.0.61/webapps/ordsconfig in:/u01/app/apache-tomcat-7.0.61/webapps/ords.war

 

配置完路徑後，重新配置ORDS

 

 

[oracle@localhostwebapps]$ java -jar ords.war setup

Jun 09, 2015 1:03:05AM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder

INFO: Usingconfiguration folder: /u01/app/apache-tomcat-7.0.61/webapps/ordsconfig/ords

Enter the name ofthe database server [localhost]:

Enter the databaselisten port [1521]:

Enter 1 to specifythe database service name, or 2 to specify the database SID [1]:2

Enter the databaseSID [xe]:yang01

Enter the databaseuser name [APEX_PUBLIC_USER]:apex_public_user

Enter the databasepassword for apex_public_user:

Confirm password:

Enter 1 to enterpasswords for the RESTful Services database users(APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used forapex_public_user or, 3 to skip this step [1]:

Enter the databasepassword for APEX_LISTENER:

Confirm password:

Enter the databasepassword for APEX_REST_PUBLIC_USER:

Confirm password:

Jun 09, 2015 1:03:59AM oracle.dbtools.common.config.file.ConfigurationFiles update

INFO: Updatedconfigurations: defaults, apex, apex_al, apex_rt

[oracle@localhostwebapps]$ ll

total 27924

drwxrwxrwx 14 oracleoinstall 4096 Mar 27 14:04 docs

drwxrwxrwx 7 oracle oinstall 4096 Mar 27 14:04 examples

drwxrwxrwx 5 oracle oinstall 4096 Mar 27 14:04 host-manager

drwxrwxrwx 33 oracleoinstall 40960 May 11 02:56 i

drwxrwxrwx 5 oracle oinstall 4096 Mar 27 14:04 manager

drwxr-xr-x 6 oracle oinstall 4096 Jun 9 01:02 ords

drwxr-xr-x 3 oracle oinstall 4096 Jun 9 01:02 ordsconfig

-rw-r--r-- 1 oracle oinstall 28491732 Jun 9 01:02 ords.war

drwxrwxrwx 3 oracle oinstall 4096 Mar 27 14:04 ROOT

 

重啟tomcat，大功告成！

倒騰了這麼久，　給大家展示下界面，以及它的一個示例應用。　真心推薦大家研究並使用這個工具，很不錯～