Archive for the ‘Apex 4’ Category
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.
APEX 4 to 18 Upgrade
While preparing my new instance for class, which uses Oracle 11g XE and Fedora 27, I got caught by the Oracle instructions. I should have got caught but when you’re in a hurry sometimes you don’t slow down enough to read it properly. Actually, for me it was the uppercase APEX_HOME that threw me for a moment. It looks too much like an environment variable. Step 5 of the upgrading instructions says:
- Log back into SQL*Plus (as above) and configure the Embedded PL/SQL Gateway (EPG):
SQL> @apex_epg_config.SQL APEX_HOME
[Note:
APEX_HOMEis the directory you specified when unzipping the file. For example, with Windows'C:\'.]
Like an idiot, I typed it in literally without reading the note. That gave me this beautifully non-constructive error message:
DECLARE * ERROR AT line 1: ORA-22288: FILE OR LOB operation FILEOPEN failed No such FILE OR DIRECTORY ORA-06512: AT "SYS.XMLTYPE", line 296 ORA-06512: AT line 16 |
I tried to launch APEX for a more meaningful error message, and it displayed:
Then, I used Google to find a few very old and not very helpful solutions because I wasn’t slowing down to read them. However, clearly if there are only old solutions the problem must be what I typed. I checked my old APEX 4 to APEX 5 blog post and then I understood the APEX_HOME. The documentation should really use APEX_UPGRADE_UNZIP_PATH to avoid having to read the detailed note.
After changing the generic APEX_PATH parameter to the physical directory directory where I stored the unzipped file content /u01/app/oracle/apex, like this:
SQL> @apex_epg_config.SQL /u01/app/oracle/apex |
and, it worked as designed.
It important to note that the APEX upgrade works perfectly. Outstanding work by a well motivated and thorough development team. I can only quibble with making Step 5 simpler. As always, I hope this helps others.
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