Archive for the ‘Apex’ Category
Listener for APEX
Unless dbca lets us build the listener.ora file, we often leave off some component. For example, running listener control program the following status indicates an incorrectly configured listener.ora file.
lsnrctl status |
It returns the following, which displays an endpoint for the XDB Server (I’m using Oracle Database 11g XE because it’s pre-containerized and has a small testing footprint):
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-MAR-2023 00:59:06 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 21-MAR-2023 21:17:37 Uptime 2 days 3 hr. 41 min. 29 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "XE" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "XEXDB" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... The command completed successfully |
The listener is missing the second SID_LIST_LISTENER value of CLRExtProc value. A complete listener.ora file should be as follows for the Oracle Database XE:
# listener.ora Network Configuration FILE: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe) (PROGRAM = extproc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) ) ) DEFAULT_SERVICE_LISTENER = (XE) |
With this listener.ora file, the Oracle listener control utility will return the following correct status, which hides the XDB Server’s endpoint:
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-MAR-2023 02:38:57 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 24-MAR-2023 02:38:15 Uptime 0 days 0 hr. 0 min. 42 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora Listener Log File /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
It seems a number of examples on the web left the SID_LIST_LISTENER value of CLRExtProc value out of the listener.ora file. As always, I hope this helps those looking for a complete solution rather than generic instructions without a concrete example.
PL/SQL Inheritance Failure
PL/SQL is a great programming language as far as it goes but it lacks true type inheritance for its collections. While you can create an object type and subtype, you can’t work with collections of those types the same way. PL/SQL object type inheritance, unlike the Java class hierarchy and parallel array class hierarchy, only supports a class hierarchy. Effectively, that means:
- You can pass a subtype as a call parameter, or argument, to a parent data type in a function, procedure, or method signature, but
- You can’t pass a collection of a subtype as a call parameter, or argument, to a collection of parent type in a function, procedure, or method signature.
The limitation occurs because collections have their own data type, which is fixed when you create them. Worse yet, because Oracle has never seen fit to fix their two underlying code trees (23 years and counting since Oracle 8i), you have two types of collections using two distinct C/C++ libraries. You define collections of Attribute Data Types (ATDs) when you create a collection of a standard scalar data type, like NUMBER, VARCHAR2, or DATE. You define collection of User-Defined Data Types (UTDs) when you create a collection of a SQL UDT or PL/SQL-only RECORD data type. The former uses one C/C++ library and the latter another.
Now, Oracle even make the differences between Java and PL/SQL more complex because it treats collections known as tables, really lists in most programming languages, differently than varrays, or arrays. You create a TABLE collection, or list, when you create a table of a scalar or UDT data type. There are two options when you create these object types, and they are:
- You create an empty collection with a no element constructor, which means you’ll need to allocate memory before assigning element values later in your program.
- You create a populated collection with a comma-delimited list of elements.
Both approaches give you a list of elements with a densely populated index. A “densely populated index” is Oracle’s jargon for how they characterize a 1-based sequence of integers without any gaps (e.g., 1, 2, 3, …). The initial construction works the same way whether you create a TABLE or VARRAY collection type. Unfortunately, after you’ve built the collection behaviors change. If you use Oracle’s Collection API to delete one or more items from a TABLE collection type, you create gaps in the index’s sequence of values. That means you must use special logic to navigate across a TABLE collection type to ensure it doesn’t fail when encountering a gap in the numeric sequence.
For example, here’s a FOR-LOOP without the logic to vouchsafe a uninterrupted set of sequence values incrementing by a counter of 1 element at a time:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE /* Create a local table collection. */ TYPE list IS TABLE OF VARCHAR2(10); /* Statically allocate memory and assign values to for elements. */ lv_list LIST := list('Moe','Shemp','Larry','Curly'); BEGIN /* Remove the second element, Shemp, from the collection of variable length strings. */ lv_list.DELETE(2); /* Loop through the target with a for loop, which depends on densely populated index values. */ FOR i IN 1..lv_list.COUNT LOOP dbms_output.put_line('['||lv_list(i)||']'); END LOOP; END; / |
The program fails when it tries to read the second element of the table collection, which was previously removed. It raises the following error message after print the first element of the table collection:
[Moe] DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 16 |
Conveniently, Oracle’s Collection API provides an EXISTS method that we can use to check for the presence of an index’s value. Modifying line 16 by wrapping it in an IF-statement fixes one problem but identifies another:
15 16 17 18 19 | FOR i IN 1..lv_list.COUNT LOOP IF lv_list.EXISTS(i) THEN dbms_output.put_line('['||lv_list(i)||']'); END IF; END LOOP; |
The program no longer fails on a missing index value, or index gap, but it returns fewer lines of output than you might expect.
That’s because the Oracle Collection API’s COUNT method returns the number of elements currently allocated in memory not the number of original elements. We learn that when we deleted the second element, Oracle deleted the memory allocated for it as well. This is the type of behavior you might expect for a singly linked list. It prints:
[Moe] [Larry] |
One more change is required to count past and to the highest index value. One line 15, change the COUNT method call to the LAST method call, which returns the highest index value.
15 16 17 18 19 | FOR i IN 1..lv_list.LAST LOOP IF lv_list.EXISTS(i) THEN dbms_output.put_line('['||lv_list(i)||']'); END IF; END LOOP; |
It now prints the three stooges we would expect to see:
[Moe] [Larry] [Curly] |
Realistically, a FOR-LOOP is not the best control structure for a collection. You should use a WHILE-LOOP and treat the incrementing value as an iterator rather than sequence index value. An iterator doesn’t worry about gaps in the sequence, it simply moves to the next element in the singly linked list. Here’s an example that uses the iterator approach with a WHILE-LOOP:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | DECLARE /* Create a local table collection. */ TYPE list IS TABLE OF VARCHAR2(10); /* Statically allocate memory and assign values to for elements. */ lv_list LIST := list('Moe','Shemp','Larry','Curly'); /* Declare a current index variable. */ CURRENT NUMBER; BEGIN /* Remove the second element, Shemp, from the collection of variable length strings. */ lv_list.DELETE(2); /* Loop through the target with a while loop, which doesn't depend on densely populated index values by setting the starting index value and increment as if with an iterator. */ CURRENT := lv_list.FIRST; WHILE NOT (CURRENT > lv_list.LAST) LOOP dbms_output.put_line('['||lv_list(CURRENT)||']'); CURRENT := lv_list.NEXT(CURRENT); END LOOP; END; / |
The iterator approach prints the elements as:
[Moe] [Larry] [Curly] |
You can reverse the process with the following changes to lines 20-24:
20 21 22 23 24 | CURRENT := lv_list.LAST; WHILE NOT (CURRENT < lv_list.FIRST) LOOP dbms_output.put_line('['||lv_list(CURRENT)||']'); CURRENT := lv_list.PRIOR(CURRENT); END LOOP; |
It prints the list backwards:
[Curly] [Larry] [Moe] |
After covering the issues with sparsely populated, those with gaps in the sequence of indexes values, table collections, let’s examine how you must work around PL/SQL’s lack of a parallel array class hierarchy. The solution lies in combining two programming concepts:
- A function to pack the sparsely populated table collection into a densely populated one, and
- A package with overloaded functions that pack different table collections.
To develop the test case, let’s use an ADT collection because it’s the simplest to work with. The following creates a table collection of a thirty character long scalar string:
1 2 3 | CREATE OR REPLACE TYPE list IS TABLE OF VARCHAR2(30); / |
The following pack function takes a table collection of the thirty character long scalar string, evaluates the string for missing elements, and packs the existing elements into a densely populated list:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE OR REPLACE FUNCTION pack ( pv_list LIST ) RETURN list IS /* Declare a new list. */ lv_new LIST := list(); BEGIN /* Read, check, and pack an old list into a new one. */ FOR i IN 1..pv_list.LAST LOOP IF pv_list.EXISTS(i) THEN lv_new.EXTEND; lv_new(lv_new.COUNT) := pv_list(i); END IF; END LOOP; RETURN lv_new; END; / |
This anonymous block tests the pack function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE /* Declare a list value. */ lv_test LIST := list('Moe','Shemp','Larry','Curly'); BEGIN /* Remove one element in the middle. */ lv_test.DELETE(2); /* Pack the list of elements into a sequence of values. */ lv_test := pack(lv_test); /* Print the list of elements from the packed list. */ FOR i IN 1..lv_test.COUNT LOOP dbms_output.put_line('['||lv_test(i)||']'); END LOOP; END; / |
It prints the expected three string values:
[Moe] [Larry] [Curly] |
Now, let’s expand the example to build an overloaded package. The first step requires building a base_t object type and a table collection of the object type, like:
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE TYPE base_t IS OBJECT ( oid NUMBER ) INSTANTIABLE NOT FINAL; / CREATE OR REPLACE TYPE base_list IS TABLE OF base_t; / |
Next, you create a book_t subtype of the base_t object type and a book_list table collection of the book_t subtype, like:
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE TYPE book_t UNDER base_t ( title VARCHAR2(30) , COST NUMBER); / CREATE OR REPLACE TYPE book_list IS TABLE OF book_t; / |
We can test the base_t and book_t default constructors with the following SQL*Plus formatting and SQL query:
COL oid FORMAT 999 COL title FORMAT A20 COL COST FORMAT 99.99 SELECT * FROM TABLE(book_list(book_t(1,'Neuromancer',15.30) ,book_t(2,'Count Zero',7.99) ,book_t(3,'Mona Lisa Overdrive',7.99) ,book_t(4,'Burning Chrome',8.89))); |
It prints the following output:
OID TITLE COST ---- -------------------- ------ 1 Neuromancer 15.30 2 Count Zero 7.99 3 Mona Lisa Overdrive 7.99 4 Burning Chrome 8.89 |
The following is an overloaded package specification:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE OR REPLACE PACKAGE packer IS /* A simple ADT list of strings. */ FUNCTION pack ( pv_list LIST ) RETURN list; /* A UDT list of base objects. */ FUNCTION pack ( pv_list BASE_LIST ) RETURN base_list; /* A UDT list of subtype objects. */ FUNCTION pack ( pv_list BOOK_LIST ) RETURN book_list; END; / |
After you create the package specification, you need to provide the implementation. This is typical in any programming language that supports Interface Description Language (IDL). A package body provides the implementation for the package specification. The package body follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | CREATE OR REPLACE PACKAGE BODY packer IS /* A simple ADT list of strings. */ FUNCTION pack ( pv_list LIST ) RETURN list IS /* Declare a new list. */ lv_new LIST := list(); BEGIN /* Read, check, and pack an old list into a new one. */ FOR i IN 1..pv_list.LAST LOOP IF pv_list.EXISTS(i) THEN lv_new.EXTEND; lv_new(lv_new.COUNT) := pv_list(i); END IF; END LOOP; RETURN lv_new; END pack; /* A simple ADT list of strings. */ FUNCTION pack ( pv_list BASE_LIST ) RETURN base_list IS /* Declare a new list. */ lv_new BASE_LIST := base_list(); BEGIN /* Read, check, and pack an old list into a new one. */ FOR i IN 1..pv_list.LAST LOOP IF pv_list.EXISTS(i) THEN lv_new.EXTEND; lv_new(lv_new.COUNT) := pv_list(i); END IF; END LOOP; RETURN lv_new; END pack; /* A simple ADT list of strings. */ FUNCTION pack ( pv_list BOOK_LIST ) RETURN book_list IS /* Declare a new list. */ lv_new BOOK_LIST := book_list(); BEGIN /* Read, check, and pack an old list into a new one. */ FOR i IN 1..pv_list.LAST LOOP IF pv_list.EXISTS(i) THEN lv_new.EXTEND; lv_new(lv_new.COUNT) := pv_list(i); END IF; END LOOP; RETURN lv_new; END pack; END packer; / |
The test case for the base_list object type is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE lv_test BASE_LIST := base_list(base_t(1),base_t(2) ,base_t(3),base_t(4)); BEGIN /* Remove one element in the middle. */ lv_test.DELETE(2); /* Pack the list of elements into a sequence of values. */ lv_test := packer.pack(lv_test); /* Print the list of elements from the packed list. */ FOR i IN 1..lv_test.LAST LOOP dbms_output.put_line('['||lv_test(i).oid||']'); END LOOP; END; / |
It prints the following output:
[1] [3] [4] |
The test case for the book_list object type is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DECLARE lv_test BOOK_LIST := book_list(book_t(1,'Neuromancer',15.30) ,book_t(2,'Count Zero',7.99) ,book_t(3,'Mona Lisa Overdrive',7.99) ,book_t(4,'Burning Chrome',8.89)); BEGIN /* Remove one element in the middle. */ lv_test.DELETE(2); /* Pack the list of elements into a sequence of values. */ lv_test := packer.pack(lv_test); /* Print the list of elements from the packed list. */ FOR i IN 1..lv_test.LAST LOOP dbms_output.put_line( '['||lv_test(i).oid||']' ||'['||lv_test(i).title||']' ||'['||lv_test(i).COST||']'); END LOOP; END; / |
It prints the following output:
[1][Neuromancer][15.3] [3][Mona Lisa Overdrive][7.99] [4][Burning Chrome][8.89] |
In conclusion, you would not have to write overloaded methods for every list if PL/SQL supported class hierarchy and parallel array class hierarchy like Java. Unfortunately, it doesn’t and likely won’t in the future. You can pack table collections as a safety measure when they’re passed as parameters to other functions, procedures, or methods with the code above.
As always, I hope this helps those looking for a solution.
APEX New Workspace
After you install APEX or upgrade a base APEX, you need to create workspaces. These instructions show you how to create a workspace in APEX 18. You have two options, you can use the base url while specifying the INTERNAL workspace.
- You start the process by accessing the Oracle APEX through the standard form by entering the following URL:
http://localhost:8080/apex
- Workspace:
INTERNAL - Username:
ADMIN - Password:
installation_system_password
- Workspace:
- The better approach is to use the APEX administrator login:
http://localhost:8080/apex/apex_admin
- Username:
ADMIN - Password:
installation_system_password
- Username:
- After logging into the Oracle Application Express (APEX) Administration console, you see the Administration home page.
- You click the Create Workspace button to start creating a work space.
- You enter a workspace name, ID number (greater than 100,000), and description and click the Next button to move to the next step.
- You choose whether to reuse an existing schema, which gives you more control. You then choose a schema from the list of available schemas. You do not use a password or schema size when you reuse a schema. You enter a password that has a capital letter, number, and special character that is not a
%when you do not reuse a schema. You also need to choose a size. The default value is 100 megabytes. Click the Next button to move to the next step.
- This dialog identifies the workspace administrator. Click the Next button to move to the next step.
- This dialog confirms what you have done in the workflow. Click the Next button to move to the next step.
- This dialog tells you that you have successfully provisioned a workspace. Click the Done button to complete the workflow.
As always, I hope this helps those trying to figure out how to do something that should not be and is not actually hard to do.
Upgrade APEX 4 to 5.1
This blog post shows you how to upgrade APEX Version 4.0.2 on a default Oracle Database 11g XE instance to APEX 5.1.2. Oracle’s APEX t upgrade document was just a bit too short and missed some details. It divided into five parts. The first part confirms your starting point. The second part downloads and positions the extracted software. The third part installs APEX 5. The fourth part configures APEX 5. The fifth part shows you how to access and get to the APEX 5 home page.
Confirm APEX 4 Installation
- Verify the database version by connecting as the
systemuser through SQL*Plus and running the following query:SELECT banner FROM v$version WHERE banner LIKE 'Oracle Database%';
It should return the following when you’re upgrading the Oracle Database 11g XE:
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
- Verify the APEX version by connecting as the
systemuser through SQL*Plus and running the following query:COLUMN version_no FORMAT A16 HEADING "Version Number" COLUMN api_compatibility FORMAT A16 HEADING "API|Compatibility" COLUMN patch_applied FORMAT A14 HEADING "Patch Applied" SELECT * FROM apex_release;
It should return the following when you start with the base Oracle Database 11g XE:
API Version Number Compatibility Patch Applied ---------------- ---------------- -------------- 4.0.2.00.09 2010.05.13
- Verify the XML Database version by connecting as the
systemuser through SQL*Plus and running the following query:COLUMN comp_name FORMAT A20 COLUMN version FORMAT A12 COLUMN status FORMAT A8 SELECT comp_name , version , status FROM dba_registry WHERE comp_id = 'XDB';
It should return the following when you’re upgrading the Oracle Database 11g XE:
COMP_NAME VERSION STATUS -------------------- ------------ -------- Oracle XML Database 11.2.0.2.0 VALID
- Verify the
memory_targetof the instance by connecting as the system user through SQL*Plus and running the following query. It should be no smaller than 300 MB.show parameter memory_target
It should return the following when you’re upgrading the Oracle Database 11g XE:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 1G
- Oracle’s instructions qualify that APEX 5 will install into the
APEX_050000schema. Oracle creates the newAPEX_050000schema with a default of thesysauxandtemptable space. You can verify these as the system user through SQL*Plus by running the following two queries. The first one checks for the tablespaces and the second for available space and auto extensibility.COLUMN default_tablespace FORMAT A22 COLUMN temporary_tablespace FORMAT A22 SELECT default_tablespace , temporary_tablespace FROM dba_users WHERE username = 'APEX_040000';
It should return the following when you’re upgrading the Oracle Database 11g XE:
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ---------------------- ---------------------- SYSAUX TEMP
COLUMN tablespace_name FORMAT A10 HEADING "Tablespace Name" COLUMN file_name FORMAT A38 HEADING "File Name" COLUMN available_space FORMAT 999,999.00 HEADING "Available|Space MB" COLUMN autoextensible FORMAT A10 HEADING "Auto|Extensible" SELECT tablespace_name , file_name , ((maxbytes - bytes) / 1024) / 1024 AS available_space , autoextensible FROM dba_data_files WHERE tablespace_name IN ('SYSAUX','SYSTEM');
It should return the following when you’re upgrading the Oracle Database 11g XE:
Tablespace Available Auto Name File Name Space MB Extensible ---------- -------------------------------------- ----------- ---------- SYSAUX /u01/app/oracle/oradata/XE/sysaux.dbf 32,027.98 YES SYSTEM /u01/app/oracle/oradata/XE/system.dbf 200.00 YES
Download APEX 5
- Download the APEX software from the Oracle web site. Assuming you download the software as the
studentuser, you can save it in yourDownloadsdirectory.You should open a Terminal session and connect as the
oracleuser. If you’ve setup your instance correctly, you will need to first become therootuser and then theoracleuser. As theoracleuser, you source the Oracle environment and copy theapex_x.x.x.zipfile from the~student/Downloadsdirectory to the/u01/app/oracledirectory.
- You copy the file from the
studentuser’sDownloadsdirectory with the following command:cp /home/student/Downloads/apex_x.x.x.zip /u01/app/oracle
- You unzip the copied
apex_x.x.x.zipfile (version 5.1.2 in this example) with the following command, and it will create a newapexdirectory as a subdirectory of the/u01/app/oracledirectory.unzip apex_5.1.2.zip
Install APEX 5
- You should query the
dba_usersview to check the status of theapex_public_userandanonymoususer accounts, like this:COLUMN username FORMAT A18 HEADING "User Name" COLUMN account_status FORMAT A10 HEADING "Account|Status" SELECT username , account_status FROM dba_users WHERE username IN ('APEX_PUBLIC_USER','ANONYMOUS');
It should return the following when you’re upgrading the Oracle Database 11g XE but the anonymous user name may be open if you’ve previously unlocked it:
Account User Name Status ------------------ ---------- APEX_PUBLIC_USER LOCKED ANONYMOUS LOCKED
- You can unlock the
apex_public_userandanonymousaccounts with the following statements:ALTER USER apex_public_user ACCOUNT UNLOCK; ALTER USER anonymous ACCOUNT UNLOCK;
- The installation uses the
flows_filesschema, which should be installed. You can verify the default and temporary tablespaces with the following query:COLUMN default_tablespace FORMAT A22 COLUMN temporary_tablespace FORMAT A22 SELECT default_tablespace , temporary_tablespace FROM dba_users WHERE username = 'FLOWS_FILES';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ---------------------- ---------------------- SYSAUX TEMP
- Open a Terminal session, connect as the
oracleuser, source the Oracle environment file, and change your active directory to the/u01/app/oracle/apexdirectory, and open a SQL*Plus connection as thesysuser. You need superuser privileges, which means you need to connect to the Oracle database with the “sys as sysdba” syntax.sqlplus sys as sysdbaYou can now install APEX 5.x.x by calling the following script with four parameters:
@apexins.sql SYSAUX SYSAUX TEMP /i/
It will take a couple minutes for the installation script to succeed. You will know that it is completed when you see the following message:
Thank you for installing Oracle Application Express 5.1.2.00.09 Oracle Application Express is installed in the APEX_050100 schema. The structure of the link to the Application Express administration services is as follows: http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql) http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway) http://host:port/apex/apex_admin (Oracle REST Data Services) The structure of the link to the Application Express development interface is as follows: http://host:port/pls/apex (Oracle HTTP Server with mod_plsql) http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway) http://host:port/apex (Oracle REST Data Services) PL/SQL procedure successfully completed. Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
- After installing APEX 5, you can re-verify the APEX version by connecting as the
systemuser through SQL*Plus and running the following query:COLUMN version_no FORMAT A16 HEADING "Version Number" COLUMN api_compatibility FORMAT A16 HEADING "API|Compatibility" COLUMN patch_applied FORMAT A14 HEADING "Patch Applied" SELECT * FROM apex_release;
It should return the following after upgrading with APEX 5:
API Version Number Compatibility Patch Applied ---------------- ---------------- -------------- 5.1.2.00.09 2016.08.24 APPLIED
Configure APEX 5
- At this point, you need to set the internal password, which you can do by navigating to the
/u01/app/oracle/apexdirectory. In that directory, you should open a SQL*Plus session as thesysuser with the “sys as sysdba” privilege. Run the following script to set the APEX Administrator’s credentials:@apxchpwd.SQLIt will prompt you for parameters, like so:
================================================================================ 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] ADMIN User "ADMIN" exists. Enter ADMIN's email [ADMIN] mclaughlinm@byui.edu Enter ADMIN's password [] Changed password of instance administrator ADMIN.
- The next task requires you to run the
apex_epg_config.sqlscript with one directory parameter. You should be connect to thesysuser with the “sys as sysdba” privilege:@apex_epg_config.SQL /u01/app/oracle
It will take a couple minutes to complete this script, and when it is complete it displays:
. Loading images directory: /u01/app/oracle/apex/images
- While the default port for XML DB is 8080, you should confirm it with this query:
SELECT dbms_xdb.gethttpport FROM dual;
It should return the following:
GETHTTPPORT ----------- 8080
Connect to and use APEX 5
- You can type the following URL into your local browser to get to the APEX 5 Administration page:
http://localhost:8080/apex/apex_admin
It should display the following login. The password is the one you entered when you ran the
apxchpwd.sqlscript in the configuration section of this post.
After you enter proper credentials, click the Sign in to Administration button to proceed. It should display the following APEX 5 home page.
You can now work in APEX 5 Administration and setup a individual workspaces.
Cleanup APEX 4
Migrating functionality to APEX 5 is possible but reworking the existing design in the context of new features is better. After you have migrated your applications and upgraded your production instance, you can drop the APEX_040000 user/schema and remove any APEX 4 workspaces. This segment of shows you how to remove an APEX 4 workspace and drop the APEX_040000 user/schema.
- The following anonymous PL/SQL block will remove an APEX 4 workspace from a user schema. It’s designed for you to run it inside the target schema but you can change it to run it as the system user against multiple schemas.
DECLARE /* Cursor for all APEX 4 tables and sequences. */ CURSOR c IS SELECT uo.object_type , uo.object_name FROM user_objects uo WHERE uo.object_name IN ('DEPT' ,'APEX$_WS_WEBPG_SECTION_HISTORY' ,'APEX$_ACL' ,'APEX$_WS_WEBPG_SECTIONS' ,'APEX$_WS_ROWS' ,'EMP' ,'APEX$_WS_FILES' ,'APEX$_WS_TAGS' ,'APEX$_WS_LINKS' ,'APEX$_WS_NOTES' ,'DEMO_USERS' ,'DEMO_CUSTOMERS' ,'DEMO_ORDERS' ,'DEMO_PRODUCT_INFO' ,'DEMO_ORDER_ITEMS' ,'DEMO_STATES' ,'APEX$_WS_HISTORY' ,'DEMO_USERS_SEQ' ,'DEMO_PROD_SEQ' ,'DEMO_ORD_SEQ' ,'DEMO_ORDER_ITEMS_SEQ' ,'DEMO_CUST_SEQ' ,'CUSTOM_HASH' ,'CUSTOM_AUTH') ORDER BY uo.object_type DESC; BEGIN FOR i IN c LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; /
- You can then connect as the
sysuser with the “sys as sysdba” privilege and drop the APEX_040000 user/schema, like this:DROP USER apex_040000 CASCADE;
Using a Sparse Index
My vacation from my blog is officially over. The question that I’m answering today is: How can you pass a set of non-sequential ID values to a function and return a result set? You can solve the problem by passing an ADT (Attribute Data Type) or UDT (User Defined Type) variable into a subquery of a cursor. The subquery leverages the TABLE function to translate the ADT or UDT into SQL result set, which is equivalent to a comma-delimited list of values.
You can also solve this problem with Native Dynamic SQL (NDS). However, the person who posed the question didn’t want to use NDS to build out a variable length list of comma-delimited numbers.
You need to create three object types for this example. They are:
- a list of numbers
- a record structure, declared as an object type without methods
- a list of the record structure
These are the SQL commands to create the required data types:
CREATE OR REPLACE TYPE list_ids IS TABLE OF NUMBER; / |
CREATE OR REPLACE TYPE item_struct IS OBJECT ( item_id NUMBER , item_title VARCHAR2(80) , release_date DATE ); / |
CREATE OR REPLACE TYPE item_struct_list IS TABLE OF item_struct; / |
Next, you create a nonsynchronous function. It takes a sparsely populated list of values that map to the surrogate key of the column, which is typically the table’s primary key column. It returns a collection of the item_struct object type. This type of function is an object-table function.
The code follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | CREATE OR REPLACE FUNCTION nonsynchronous ( pv_list_ids LIST_IDS ) RETURN item_struct_list IS /* Declare a record data structure list. */ lv_struct_list ITEM_STRUCT_LIST := item_struct_list(); /* Declare a sparsely indexed list of film items. */ CURSOR get_items ( cv_list_ids LIST_IDS ) IS SELECT item_id AS item_id , item_title || CASE WHEN item_subtitle IS NOT NULL THEN ': '|| item_subtitle END AS item_title , release_date AS release_date FROM item WHERE item_id IN (SELECT * FROM TABLE(cv_list_ids)) ORDER BY item_id; BEGIN /* Lood through the sparsely populated list of numbers. */ FOR i IN get_items(pv_list_ids) LOOP lv_struct_list.EXTEND; lv_struct_list(lv_struct_list.COUNT) := item_struct( item_id => i.item_id , item_title => i.item_title , release_date => i.release_date ); END LOOP; /* Return the record structure list. */ RETURN lv_struct_list; END; / |
The foregoing nonsynchronous function uses a nested query that transforms to a result set on lines 18 and 19. In the execution block of the program, it uses a call to the item_struct structure to capture and assign row values to an element of the lv_struct_list variable.
You can now test the nonsynchronous function with the following query:
COL item_id FORMAT 9999 HEADING "Item|ID #" COL item_title FORMAT A40 HEADING "Item Title" COL release_date FORMAT A11 HEADING "Release|Date" SELECT * FROM TABLE(nonsynchronous(list_ids(1002, 1013, 1007))); |
The query returns the record set as an ordered list in the result set, like:
Item Release ID # Item Title Date ----- ---------------------------------------- ----------- 1002 Star Wars I: Phantom Menace 04-MAY-99 1007 RoboCop 24-JUL-03 1013 The DaVinci Code 19-MAY-06 |
I hope this answers the question about how to get results sets with sparsely populated ID values.
Oracle Cleanup a Schema
Back in January 2014, I wrote a script to cleanup an Oracle student schema. It worked well until I started using APEX 4 in my student schema. You create the following 75 objects when you create an APEX 4 schema.
OBJECT TYPE TOTAL ------------ ------- TABLE 17 INDEX 28 SEQUENCE 5 TRIGGER 14 LOB 9 FUNCTION 2 |
Here’s the modified script that ignores the objects created automatically by Oracle APEX when you create a student workspace:
BEGIN FOR i IN (SELECT object_name , object_type , last_ddl_time FROM user_objects WHERE object_name NOT IN ('APEX$_WS_WEBPG_SECTION_HISTORY','APEX$_WS_WEBPG_SECTIONS_T1' ,'APEX$_WS_WEBPG_SECTIONS_PK','APEX$_WS_WEBPG_SECTIONS' ,'APEX$_WS_WEBPG_SECHIST_IDX1','APEX$_WS_TAGS_T1' ,'APEX$_WS_TAGS_PK','APEX$_WS_TAGS_IDX2','APEX$_WS_TAGS_IDX1' ,'APEX$_WS_TAGS','APEX$_WS_ROWS_T1','APEX$_WS_ROWS_PK' ,'APEX$_WS_ROWS_IDX','APEX$_WS_ROWS','APEX$_WS_NOTES_T1' ,'APEX$_WS_NOTES_PK','APEX$_WS_NOTES_IDX2','APEX$_WS_NOTES_IDX1' ,'APEX$_WS_NOTES','APEX$_WS_LINKS_T1','APEX$_WS_LINKS_PK' ,'APEX$_WS_LINKS_IDX2','APEX$_WS_LINKS_IDX1','APEX$_WS_LINKS' ,'APEX$_WS_HISTORY_IDX','APEX$_WS_HISTORY','APEX$_WS_FILES_T1' ,'APEX$_WS_FILES_PK','APEX$_WS_FILES_IDX2','APEX$_WS_FILES_IDX1' ,'APEX$_WS_FILES','APEX$_ACL_T1','APEX$_ACL_PK','APEX$_ACL_IDX1' ,'APEX$_ACL','CUSTOM_AUTH','CUSTOM_HASH','DEPT','EMP' ,'UPDATE_ORDER_TOTAL') AND NOT ((object_name LIKE 'DEMO%' OR object_name LIKE 'INSERT_DEMO%' OR object_name LIKE 'BI_DEMO%') AND object_type IN ('TABLE','INDEX','SEQUENCE','TRIGGER')) AND NOT (object_name LIKE 'SYS_LOB%' AND object_type = 'LOB') AND NOT (object_name LIKE 'SYS_C%' AND object_type = 'INDEX') ORDER BY object_type DESC) LOOP /* Drop types in descending order. */ IF i.object_type = 'TYPE' THEN /* Drop type and force operation because dependencies may exist. Oracle 12c also fails to remove object types with dependents in pluggable databases (at least in release 12.1). Type evolution works in container database schemas. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' FORCE'; /* Drop table tables in descending order. */ ELSIF i.object_type = 'TABLE' THEN /* Drop table with cascading constraints to ensure foreign key constraints don't prevent the action. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS PURGE'; /* Oracle 12c ONLY: Purge the recyclebin to dispose of system-generated sequence values because dropping the table doesn't automatically remove them from the active session. CRITICAL: Remark out the following when working in Oracle Database 11g. */ EXECUTE IMMEDIATE 'PURGE RECYCLEBIN'; ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN /* A system generated LOB column or INDEX will cause a failure in a generic drop of a table because it is listed in the cursor but removed by the drop of its table. This NULL block ensures there is no attempt to drop an implicit LOB data type or index because the dropping the table takes care of it. */ NULL; ELSE dbms_output.put_line('DROP '||i.object_type||' '||i.object_name||';'); /* Drop any other objects, like sequences, functions, procedures, and packages. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; / |
As always, I hope this helps others.
APEX Create Table
The following walks you through how you sign on to a STUDENT Workspace with Oracle’s APEX product. It shows you how to create a new table with the Object Browser tool.
You can find instructions on how to create your own STUDENT Workspace in this blog post. Overall, Oracle APEX is a valuable tool to learn and master.
- You start the process by accessing the Oracle Database 11g APEX, which you can access at
http://localhost:8080/apexby default on the server. If you’ve got a static IP address for your instance, you can replacelocalhostwith the IP address orhostnamefor the IP address.- Workspace:
STUDENT - Username:
ADMIN - Password:
STUDENT
- Workspace:
- After you login to the
STUDENTworkspace, you have four options. They are the: Application Builder, SQL Workshop, Team Development, and Administration. You start the process by accessing the Oracle Database 11g APEX, which you can access athttp://localhost:8080/apexby default on the server. If you’ve got a static IP address for your instance, you can replacelocalhostwith the IP address orhostnamefor the IP address. Click on the Object Browser icon to proceed.
- Clicking the SQL Workshop icon takes you to the second level menu. You click the Object Browser icon to create a database object.
- After clicking the Object Browser icon, you see the screen at the left. Click the Create button to create a table.
- After clicking the Create button, you see the screen at the left. Click the type of database object that you want to create. In our case, we click the Table hypertext to start the create table workflow.
- After clicking the Table hyperlink, you see the Create Table screen at the left. Enter the column names, choose their data types and set the scale and precision. You should also check the Not Null checkbox when you want a column to be mandatory. Click the Next button to continue the create table workflow.
- After entering the column names, you should choose the data types, enter the scale and precision, and check the
NOT NULLcheckbox to make appropriate columns mandatory by applyingNOT NULLdatabase constraints. If you run out of entry rows, you can click the Add Column button to add new rows. Click the Next button to continue the create table workflow when you’ve defined the columns.
- After defining the column names, you should choose whether the primary key will use a new sequence or an existing sequence. You also have the ability to not assign a primary key value or simply leave it unpopulated when inserting new rows. The example creates an
IMAGE_PKprimary key constraint on theIMAGE_IDcolumn, and declares anIMAGE_SEQsequence value. Click the Next button to continue the create table workflow when you’ve defined the primary key constraint and any new sequence value for the primary key column.
- After defining the primary key constraint, you can define foreign key column constraints. You enter a foreign key constraint name, choose between a Disallow Delete, Cascade Delete, or Set Null on Delete rule, select the foreign key column, the foreign key’s referenced table and column. Click the Add button to continue the create table workflow.
- After defining a foreign key constraint, you can see the constraint that you created. Then, you can define another foreign key column constraints. You repeat the steps from the prior steps to add another foreign key constraint. Click the Add button to create a second foreign key constraint and complete the create table workflow.
- After defining a second foreign key constraint, you see the following two foreign key constraints. Click the Next button to complete the create table workflow.
- After defining all the foreign key constraints, you can create check and unique constraints. You check a radio button for a check or unique constraint, and then you select the columns for the constraint’s key. Click the /Add button to create any check or unique constraints as part of the create table workflow.
- After defining all check and unique key constraints, you can see them in the Constraints box. Click the Next button to complete the create table workflow.
- After defining all items about the table, you can see the SQL to create the IMAGE table and its constraints. You can copy the SQL into a file for later use when writing a re-runnable script. Click the Create button to complete the create table workflow and create the table.
The following are the contents of the script for the actions you’ve defined:
CREATE table "IMAGE" ( "IMAGE_ID" NUMBER NOT NULL, "FILE_NAME" VARCHAR2(60) NOT NULL, "MIME_TYPE" NUMBER NOT NULL, "ITEM_IMAGE" BLOB, "CREATED_BY" NUMBER NOT NULL, "CREATION_DATE" DATE NOT NULL, "LAST_UPDATED_BY" NUMBER NOT NULL, "LAST_UPDATE_DATE" DATE NOT NULL, constraint "IMAGE_PK" primary key ("IMAGE_ID") ) / CREATE sequence "IMAGE_SEQ" / CREATE trigger "BI_IMAGE" before insert on "IMAGE" for each row begin if :NEW."IMAGE_ID" is null then select "IMAGE_SEQ".nextval into :NEW."IMAGE_ID" from dual; end if; end; / ALTER TABLE "IMAGE" ADD CONSTRAINT "IMAGE_FK1" FOREIGN KEY ("CREATED_BY") REFERENCES "SYSTEM_USER" ("SYSTEM_USER_ID") / ALTER TABLE "IMAGE" ADD CONSTRAINT "IMAGE_FK2" FOREIGN KEY ("LAST_UPDATED_BY") REFERENCES "SYSTEM_USER" ("SYSTEM_USER_ID") / alter table "IMAGE" add constraint "IMAGE_UK1" unique ("FILE_NAME","MIME_TYPE") /
- After creating the table, trigger, sequence, and constraints, you can see the table definition. You also have the ability to modify the table. At this point, you can create another structure or you can click the Home or SQL Workshop menu choice.
As always, I hope this helps those looking to learn new things and approaches.
APEX SQL Query
The following walks through how you sign on to a STUDENT Workspace with Oracle’s APEX product and write and run free-form SQL statements. You can find instructions on how to create your own STUDENT Workspace.
While this blog introduces several concepts and features of Oracle APEX, it only focuses on how to write and run free-form SQL statements. Overall, Oracle APEX is a valuable tool to learn and master.
- You start the process by accessing the Oracle Database 11g APEX, which you can access at
http://localhost:8080/apexby default on the server. If you’ve got a static IP address for your instance, you can replacelocalhostwith the IP address orhostnamefor the IP address.- Workspace:
STUDENT - Username:
ADMIN - Password:
STUDENT
- Workspace:
- After you login to the
STUDENTworkspace, you have four options. They are the: Application Builder, SQL Workshop, Team Development, and Administration. You start the process by accessing the Oracle Database 11g APEX, which you can access athttp://localhost:8080/apexby default on the server. If you’ve got a static IP address for your instance, you can replacelocalhostwith the IP address orhostnamefor the IP address. Click on the SQL Workshop icon to proceed.- Application Builder: Let’s you build custom APEX applications.
- SQL Workshop: Let’s you work with custom SQL, and APEX provides you with the following utilities:
- Object Browser: Lets you create tables, views, and other objects.
- SQL Commands: Lets you run individual SQL statements inside a browser window and returns results in the bottom pane.
- SQL Scripts: Lets you create, upload, delete, and run scripts from the browser.
- Query Builder: Lets you create free form queries that include joins between tables, but limits you to primary to foreign key table relationships. That means you can’t write range joins with a cross join and the
BETWEENoperator and you can’t write self-joins. - Utilities: Lets you work with the Data Workshop (imports and exports data), Object Reports (a SQL report writer tool), Generate DDL (a tool that creates structures in the database), User Interface Defaults (coordinate data dictionary), Schema Comparison (a tool to compare similarities between schemas, About Database (the ability to connect as the database administrator), and Recycle Bin (dropped and purged structures).
- Team Development: A project management tool.
- Administration: Lets you manage database services, users and groups, monitor activities, and dashboards. You should note that the SQL query doesn’t have a semicolon like it would in a SQL*Plus environment. The Run button acts as the execution operator and effectively replaces the role of the semicolon, which traditionally executes a statement.
- Clicking the SQL Workshop icon takes you to the second level menu. You click the SQL Commands icon to enter a free-form SQL statement. Click on the SQL Commands icon to proceed.
- The first text panel lets you enter free-form queries. The Autocommit checkbox is enabled, which means the result of
INSERTandUPDATEstatements are immediate and don’t require aCOMMITstatement. The second text panel displays results from a query or acknowledgment of statement completion.
- This screen shot shows a query in the first panel and the results of the query in the second panel.
As always, I hope this helps those looking to learn new things and approaches.
APEX Create Workspace
In a prior post, I showed you how to access Oracle Database 11g XE APEX. This post shows you how to create a basic workspace against a student database (or, what Oracle lables a schema, which is synonymous with a database).
- You start the process by accessing the Oracle Database 11g APEX, which you can access at
http://localhost:8080/apexby default on the server. If you’ve got a static IP address for your instance, you can replacelocalhostwith the IP address orhostnamefor the IP address.- Workspace:
INTERNAL - Username:
ADMIN - Password:
installation_system_password
- Workspace:
- After logging into the Oracle Application Express (APEX) system, you see the Home page at the left. Click the Manage Workspace button on the Home page.
- Manage Workspace Dialog: After clicking the Manage Workspace button on the Home page, you see four major options to manage workspaces. They are the Workspace Actions, Workspace Reports, Export Import, and Manage Applications. You want to click on the Create Workspace to create a new workspace.
- Identify Workspace Diaglog: Enter a Workspace Name and Workspace Description. Then, click on the Next button move forward in the workflow.
- Create Workspace Dialog: You create a workspace, APEX presumes you want to create a new schema. That’s why the Re-use existing schema drop down chooses
Noby default. You enter the Schema Name asSTUDENT, the Password for theSTUDENTschema, and an initial Space Quota (MB) of100. Then, click the Next button to continue.
- Identify Schema Dialog: If the schema you chose exists, you get the correction dialog. You need to change the Re-use existing schema drop down from
NotoYes. Then, click the Next button to continue.
- Identify Administrator Dialog: Here you enter an Administrator Username, Password, First Name, Last Name, and email address. Then, click the Next button to continue.
- Confirm Request Dialog: Here you review your entries and click the Confirm Request button to continue.
- Success Confirmation Dialog: Here you click the Done Request button to continue.
As always, I hope this helps you learn how to create a workspace.
Oracle 11g XE APEX
The question for most new Oracle users is what’s Apex? They have a different question When they discover how to connect to the Oracle Database 11g XE default instance with this URL:
http://localhost:8080/apex |
You’ll see the following web site, and wonder what do I enter for the Workspace, the Username, and the Password values?
The answers are:
- Default Workspace: INTERNAL
- Default User: ADMIN
- Default Password:
SYSorSYSTEMPassword from Install
Enter those values within the initial password time interval and you’ll arrive at the next screen where you can manage the Oracle Database 11g XE instance. If you wait too long, you’ll be redirected to enter the original SYS or SYSTEM password from install and a new password twice. The rules for a new password are:
- Password must contain at least 6 characters.
- New password must differ from old password by at least 2 characters.
- Password must contain at least one numeric character (0123456789).
- Password must contain at least one punctuation character (!”#$%&()“*+,-/:;<=>?_).
- Password must contain at least one upper-case alphabetic character.
- Password must not contain username.
Whether you go directly to the next screen or have to enter your a new password, you should see the following screen:
You can find the default configuration for the installation with the following anonymous PL/SQL block:
DECLARE /* Declare variables. */ lv_endpoint NUMBER := 1; lv_host VARCHAR2(80); lv_port NUMBER; lv_protocol NUMBER; BEGIN /* Check for current XDB settings. */ dbms_xdb.getlistenerendpoint( lv_endpoint , lv_host , lv_port , lv_protocol ); /* Print the values. */ dbms_output.put_line('Endpoint: ['||lv_endpoint||']'||CHR(10)|| 'Host: ['||lv_host||']'||CHR(10)|| 'Port: ['||lv_port||']'||CHR(10)|| 'Protocol: ['||lv_protocol||']'); END; / |
It should print the following:
Endpoint: [1] Host: [localhost] Port: [8080] Protocol: [1] |
This is a standalone configuration and you can’t connect to the XDB server from another machine. You can only connect from the local machine.
I hope this helps those trying to use the default Apex 4 installation provided as part of the Oracle Database 11g XE instance. You can read an older post of mine that shows you how to set up a basic Workspace, but after reflection I’ll write more about creating and managing workspaces.
Subscribe via RSS