Instalcion APEX
Realizamos la descarga del programa apex_5.1.4_en.zip
mv -vfr apex_5.1.4_en.zip $ORACLE_HOME
-rw-r--r-- 1 oracle oinstall 92038978 oct 24 08:44 apex_5.1.4_en.zip
unzip apex_5.1.4_en.zip
se crea un directorio apex
ingresamos con: cd apex
creamos un tablespace llamado APEX
corremos el instalador
SQL>@apexins.sql tablespace_apex tablespace_files tablespace_temp images
SQL>@apexins.sql APEX APEX temp images
cuando termina la instalación se crea un usuario en la BD llamado APEX_050100
si existe una versión anterior se debe desistalar
SQL> @apxremov.sql
se elimina el usuario asociado de la BD APEX_040200
CONSULTAR VERSIÓN APEX INSTALADO
Para consultar la versión habilitada actual:
SQL> SELECT VERSION_NO FROM APEX_RELEASE;
Para consultar versiones anteriores instaladas de APEX:
SQL> SELECT VERSION_NO FROM APEX_040100.APEX_RELEASE;
SQL> select * from apex_release;
VERSION_NO
--------------------------------------------------------------------------------
API_COMPATIBILITY
--------------------------------------------------------------------------------
PATCH_APPLIED
--------------------------------------------------------------------------------
18.2.0.00.12
2018.05.24
APPLIED
SQL>
CONSULTAR APEX PARA REVISAR USUARIO
CONN / AS SYSDBA
SELECT username, account_status, TO_CHAR(lock_date, 'DD-MON-YYYY') AS lock_date, TO_CHAR(expiry_date, 'DD-MON-YYYY') AS expiry_date, default_tablespace, temporary_tablespace FROM dba_users WHERE username LIKE UPPER('%APEX%') ORDER BY username;
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESP TEMPORARY_TABLE
APEX_050100 EXPIRED & LOCKED 03-NOV-2011 03-NOV-2011 SYSAUX TEMP
APEX_PUBLIC_USER EXPIRED & LOCKED 03-NOV-2011 03-NOV-2011 USERS TEMP
3 rows selected.
SQL>
PRIMERO CONSULTAR ACLS
select * from DBA_NETWORK_ACLS;
##################################################
CREAR ACLS
CONN / AS SYSDBA
DECLARE
--l_principal VARCHAR2(20) := 'APEX_040200';
--l_principal VARCHAR2(20) := 'APEX_050000'; l_principal VARCHAR2(20) := 'APEX_050100';
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl=> 'oracle_base_acl.xml', description => 'An ACL for the oracle-base.com website',
principal => l_principal,
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'oracle_base_acl.xml',
host => 'oracle-base.com',
lower_port => 80,
upper_port => 80);
COMMIT;
END;
/
ELIMINAR ACL
BEGIN
--dbms_network_acl_admin.drop_acl('oracle_base_acl.xml');
END;
#####################################################
ASIGNAR PERMISOS AL ESQUEMA
GRANT EXECUTE ON SYS.UTL_HTTP TO ESQUEMA_USER;
#####################################################
CREAR LA FUNCION
CREATE OR REPLACE FUNCTION add_numbers (p_int_1 IN NUMBER, p_int_2 IN NUMBER)
RETURN NUMBER
AS
l_clob CLOB;
l_result VARCHAR2(32767);
BEGIN
-- Get the XML response from the web service.
l_clob := APEX_WEB_SERVICE.make_rest_request(
p_url => 'http://oracle-base.com/webservices/add-numbers.php',
p_http_method => 'GET',
p_parm_name => APEX_UTIL.string_to_table('p_int_1:p_int_2'),
p_parm_value => APEX_UTIL.string_to_table(p_int_1 || ':' || p_int_2)
);
-- Display the whole document returned.
DBMS_OUTPUT.put_line('l_clob=' || l_clob);
-- Pull out the specific value of interest.
l_result := APEX_WEB_SERVICE.parse_xml(
p_xml => XMLTYPE(l_clob),
p_xpath => '//answer/number/text()'
);
DBMS_OUTPUT.put_line('l_result=' || l_result);
RETURN TO_NUMBER(l_result);
END;
/
#####################################################
VALIDAMOS LA FUNCION QUE CONSUME EL WEBSERVICE
SELECT add_numbers(1, 5) FROM dual; ADD_NUMBERS(1,5) ---------------- 6 SQL>
#########################################################
Validación HTTPS
Creamos un directorio wallet para ingresar los certificados
[server]$ mkdir -p /u01/app/oracle/admin/BD11G/wallet [server]$ ll total 120 drwxr-x--- 2 oracle oinstall 106496 oct 31 08:26 adump drwxr-x--- 2 oracle oinstall 4096 feb 9 2018 dpdump drwxr-x--- 2 oracle oinstall 4096 feb 11 2015 pfile drwxr-xr-x 2 oracle oinstall 4096 oct 31 10:40 wallet
Creamos el nuevo directorio virtual con el login WalletPasswd123
[server]$ orapki wallet create -wallet /u01/app/oracle/admin/BD11G/wallet/ -pwd WalletPasswd123 -auto_login
Oracle PKI Tool: Versión 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle y/o sus subsidiarias. Todos los Derechos Reservados.
[server]$
De forma grafica utilizando un Browser Web exportamos o guardamos el certificado del sitio https://www.redhat.com
DigiCertHighAssuranceEVRootCA lo guardamos el el servidor de bases de datos /home
Agregamos el certificado
orapki wallet add -wallet /u01/app/oracle/admin/BD11G/wallet/ -trusted_cert -cert "/home/GoDaddyRootCertificateAuthority-G2"
-pwd WalletPasswd123
Revisamos la autenticación del sitio web https con la función
EXEC UTL_HTTP.set_wallet('file','passwd'); ------------> Fijamos las credenciales para el wallet
Creamos el procedimiento
CREATE OR REPLACE PROCEDURE SYS.show_html_from_url (p_url IN VARCHAR2) AS
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_text VARCHAR2(32767);
BEGIN
l_http_request := UTL_HTTP.begin_request(p_url);
l_http_response := UTL_HTTP.get_response(l_http_request);
BEGIN
LOOP
UTL_HTTP.read_text(l_http_response, l_text, 32766);
DBMS_OUTPUT.put_line (l_text);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response(l_http_response);
RAISE;
END show_html_from_url2;
/
EXEC show_html_from_url('https://www.redhat.com'); --------> Visualizamos el contenido del sitio web https
EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/BD11G/wallet', 'WalletPasswd123');
EXEC show_html_from_url('https://www.redhat.com'); EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/BD11G/wallet', NULL);
EXEC SYS.show_html_from_url('https://www.redhat.com');
https://oracle-base.com/articles/misc/apex_web_service-consuming-soap-and-rest-web-services#apex-installation
https://oracle-base.com/articles/misc/utl_http-and-ssl
______________________________________________________________________________
APEX: PARA DESARROLLAR PROTOTIPOS WEB DE FORMA SEGURA Y RAPIDA
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 24 08:38:58 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> SELECT COMP_NAME,VERSION, STATUS FROM DBA_REGISTRY WHERE COMP_ID='APEX';
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Application Express
5.1.4.00.08 VALID
SQL> SELECT * FROM APEX_RELEASE;
VERSION_NO
--------------------------------------------------------------------------------
API_COMPATIBILITY
--------------------------------------------------------------------------------
PATCH_APPLIED
--------------------------------------------------------------------------------
5.1.4.00.08
2016.08.24
APPLIED
SQL>
*********************************************************************
CREAR DIRECTORIO IMAGENES APEX
SQL> @apxldimg.sql /u01/app/oracle/product/11.2.0/dbhome_1/
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
. Loading images directory: /u01/app/oracle/product/11.2.0/dbhome_1//apex/images
Directory created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.
Directory dropped.
timing for: Load Images
Elapsed: 00:01:53.53
PL/SQL procedure successfully completed.
Commit complete.
*************************************************************************
CONFIGURAR USUARIOS APEX
SQL> @apxchpwd.sql
================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
User "ADMIN" exists.
Enter ADMIN's email [ADMIN]
Enter ADMIN's password []
Changed password of instance administrator ADMIN.
SQL>
***************************************************************************
CAMBIO PUERTO APEX DE 8080 a 8085
SQL> start /$ORACLE_HOME/apex/apxconf.sql
PORT
----------
8080
Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.
================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
User "ADMIN" exists.
Enter ADMIN's email [ADMIN]
Enter ADMIN's password []
Changed password of instance administrator ADMIN.
Enter a port for the XDB HTTP listener [ 8080] 8085
...changing HTTP Port
SQL>
*******************************************************************************
DESBLOQUEAR USER ANONYMOUS
SQL> alter user ANONYMOUS account unlock;
User altered.
SQL>
******************************************************************************
RECREAR APEX IMAGEN en apex/utilities
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 24 08:56:27 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> @reset_image_prefix.sql
Enter the Application Express image prefix [/i/]
...Changing Application Express image prefix
NEW
---
/i/
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
...Recreate APEX global
PL/SQL procedure successfully completed.
...Purge all cached region and page entries
PL/SQL procedure successfully completed.
...Recompiling the Application Express schemas
PL/SQL procedure successfully completed.
Image Prefix update complete
http://XX.XX.XX.XX:8085/apex/
MODO: INTERNAL
USER: ADMIN
PASS: ********