Oct 02 2013

Install APEX’s Sample Packaged Application by Importing its SQL Script

Category: APEXittichai @ 3:11 pm

If for some reason, you could not install the APEX sample packaged application via Application Builder > Packaged Applications interface, you have an option of installing it by importing the SQL script (fxxxx.sql).

In my case, when installing via the Packaged Applications interface, I got the following error:

Screen Shot 2013-10-02 at 12.06.48 PM

While working with DBA and Oracle support to resolve the root cause of this issue, I’ve found that the installation script (like export file) of the packaged applications comes with the APEX installation files under apex/core/packaged_apps

ICHAMMA1:packaged_apps$ pwd

ICHAMMA1:packaged_apps$ grep "prompt  APPLICATION" *.sql
f7000.sql:prompt  APPLICATION 7000 - Online Marketing Campaign Calendar
f7010.sql:prompt  APPLICATION 7010 - Decision Manager***
f7020.sql:prompt  APPLICATION 7020 - Asset Manager*
f7050.sql:prompt  APPLICATION 7050 - Opportunity Tracker ***
f7060.sql:prompt  APPLICATION 7060 - Bug Tracking***
f7090.sql:prompt  APPLICATION 7090 - Group Calendar ***
f7100.sql:prompt  APPLICATION 7100 - Artwork Catalog***
f7120.sql:prompt  APPLICATION 7120 - Expertise Tracker***
f7130.sql:prompt  APPLICATION 7130 - Community Requests ***
f7140.sql:prompt  APPLICATION 7140 - Incident Tracking***
f7150.sql:prompt  APPLICATION 7150 - Systems Catalog***
f7170.sql:prompt  APPLICATION 7170 - Customer Tracker***
f7190.sql:prompt  APPLICATION 7190 - Issue Tracker***
f7220.sql:prompt  APPLICATION 7220 - P-Track***
f7230.sql:prompt  APPLICATION 7230 - Data Model Repository Viewer*
f7240.sql:prompt  APPLICATION 7240 - Checklist Manager***
f7250.sql:prompt  APPLICATION 7250 - Data Reporter***
f7270.sql:prompt  APPLICATION 7270 - APEX Application Archive***
f7280.sql:prompt  APPLICATION 7280 - Survey Builder ***
f7290.sql:prompt  APPLICATION 7290 - Meeting Minutes***
f7300.sql:prompt  APPLICATION 7300 - Use Case Status***
f7600.sql:prompt  APPLICATION 7600 - Sample Access Control*
f7610.sql:prompt  APPLICATION 7610 - Sample Build Options*
f7650.sql:prompt  APPLICATION 7650 - Go Live Check List***
f7800.sql:prompt  APPLICATION 7800 - Brookstrut Sample Application ***
f7810.sql:prompt  APPLICATION 7810 - Sample Reporting***
f7820.sql:prompt  APPLICATION 7820 - Sample Calendar***
f7830.sql:prompt  APPLICATION 7830 - Sample Charts***
f7840.sql:prompt  APPLICATION 7840 - Sample Dynamic Actions***
f7850.sql:prompt  APPLICATION 7850 - Sample Data Loading***
f7860.sql:prompt  APPLICATION 7860 - Sample Master Detail***
f7870.sql:prompt  APPLICATION 7870 - Sample Forms and Grid Layout***
f7880.sql:prompt  APPLICATION 7880 - Sample Search***
f7890.sql:prompt  APPLICATION 7890 - Feedback ***
f7900.sql:prompt  APPLICATION 7900 - Sample Dialog***
f7910.sql:prompt  APPLICATION 7910 - Sample Trees***
f7920.sql:prompt  APPLICATION 7920 - Sample Lists***
f7930.sql:prompt  APPLICATION 7930 - Sample Wizards***
f7940.sql:prompt  APPLICATION 7940 - Sample Collections***
f7950.sql:prompt  APPLICATION 7950 - Sample Time Zones*
f7960.sql:prompt  APPLICATION 7960 - Sample File Upload and Download***
f7980.sql:prompt  APPLICATION 7980 - Sample RESTful Services***
f8950.sql:prompt  APPLICATION 8950 - Sample Database Application

The installation is just simple as importing this script file (Application Builder > Import) or run from the SQL Plus (with proper security setup).

Tags: , , ,

May 10 2011

The Developer Toolbar in APEX Application Disappeared

Category: APEXittichai @ 7:57 am

If the Developer Toolbar (shown below) in your Oracle APEX application is not displayed, you can control its display by changing the Status attribute on the application’s Edit Definition page.

I’ve learned about this when somehow one of our applications suddenly did not display the Developer Toolbar. We found out later that during environment refresh, the deployment team switched the Application’s Availability Status to Unavailable before the refresh and then back to Available after it’s done – not realizing that it was initially Available with Edit Links.

No harm done – just temporary inconvenience from Developer’s standpoint. :-)

Related Topic:

Tags: , , , , , ,

Mar 16 2011

File Browser in APEX 4 with BLOB column specified in item source attribute

Category: APEXittichai @ 12:45 pm

In APEX 4, we now have the option to place where the uploaded file will be stored within the development workspace interface. The Storage Type setting of the File Browser item has two options:

File Browser Settings

  • Table WWV_FLOW_FILES stores the uploaded file in the table wwv_flow_files (or APEX_APPLICATION_FILES).  This is the default way in the previous versions of APEX. Note that if you still decide to store the uploaded files this way, it is suggested to move record to another custom table after upload, and then clean it up in this table because this is the shared default area (table) where every application will upload files to.
  • BLOB column specified in item source attribute will store the uploaded file in a custom table identified in the Automatic Row Processing (DML) process and the column specified in the item source attribute. This column has to be of data type BLOB.

To configure the File Browser with the BLOB column specified in item source attribute.

1. Create a table for storing uploaded files. One of the columns must be BLOB data type.

CREATE TABLE tbl_attach_file
  attach_id          NUMBER PRIMARY KEY,
  attach_data        BLOB,
  attach_mimetype    VARCHAR2(255),
  attach_filename    VARCHAR2(255),
  attach_last_update DATE ,
  attach_charset     VARCHAR2(128),
  attach_user        VARCHAR(10)


   ON tbl_attach_file
     INTO :NEW.attach_id

2. Create a File Browser item with the following settings.

If this is just simply for file upload, select No on Display Download Link. Otherwise, it will require additional Automated Row Fetch (DML). See note and comment below.

File Browser Settings

3.  The item’s source points to the BLOB column.

File Browser Source

4. Create an Automatic Row Processing  (DML) process.


5. Create a button called Upload with Database Action – SQL Insert Action.

Initially I thought this should be sufficient, but when I ran the page, I got this error.

No DML Error

Apparently I got this error because I checked Yes to Display Download Link thus it requires a DML to fetch data. If this is set to No, it should be okay without the following Automated Row Fetch (DML). Thanks Patrick for pointing that out.

To workaround this, I created another DML but for Automated Row Fetch.

DML Fecth

For a purpose of blogging, we’re not going to run any row fetch, so the DML Fetch will have it disabled (its condition to Never). I’m still not sure why the Automated Row Fetch (DML) is needed (even being disabled) if we just want to upload files. But if it is not there, I got the ORA-2001: No Corresponding DML process shown in step 5.

A quick test shows that the file is uploaded to our custom table.

Upload Test

According to Oracle APEX document, the character set of the BLOB is not automatically set on upload. We will have to create an additional page item which is bound to the character set column. This will be where users can specify the character set for the file they are uploading.

Tags: , , , , ,

Nov 05 2010

Upgrade APEX to 4.0.1 encounters “ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists”

Category: APEX,Installationittichai @ 3:03 pm

Few weeks back, we’ve upgraded multiple APEX installations (development, test and production) to new version 4.0.1 from the default version in Oracle 11g R1. The upgrade (just running the apexins.sql) went well for all except one. We got a strange error during the last stage of installation.

Performing Application Express component validation - please wait...

Completing registration process. 23:02:26
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 18566
ORA-06512: at "SYS.DBMS_STATS", line 19015
ORA-06512: at "SYS.DBMS_STATS", line 21374
ORA-06512: at "SYS.DBMS_STATS", line 21417
ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 1067
ORA-06512: at "SYS.DBMS_REGISTRY", line 578
ORA-06512: at line 7

The status of the APEX component in the DBA_REGISTRY is set to LOADED.

SYS@DB11G AS SYSDBA> select comp_name, version, status from dba_registry
where comp_name like '%Application Express';

COMP_NAME                             VERSION              STATUS
-----------------------------------   -------------------- -----------
Oracle Application Express            LOADED

Many attempts to uninstall (using apxremov.sql) and reinstall still encountered the same error.

Initial thought was with the version conflict. I even cleaned up the version coming with database by following the instructions from the MOS note ID 558340.1 – How to Uninstall Oracle HTML DB / Application Express from the Database?  This includes drop all APEX* and FLOWS* schemas as well as public synonyms.

All did not help.

Tracing from the installation script – apexins.sql, the installation portion calls the coreins.sql. All scripts within it run fine until the last one – endins.sql, which according to the description note in the script is to perform the component validation at the end of the installation. The only main operation in the endins.sql is a call to the procedure validate_apex which is owned by SYS. This is just to confirm that actually all installations went fine but failed only when validation.

So I’ve decided to run just it. Surprisingly, it came out with no errors. And more importantly, the APEX registry is now VALID :-)

SYS@DB11G AS SYSDBA> exec sys.validate_apex;

PL/SQL procedure successfully completed.

SYS@DB11G AS SYSDBA> select comp_name, version, status from dba_registry
where comp_name like '%Application Express';

COMP_NAME                             VERSION STATUS
-----------------------------------   ------------------------------ -----------
Oracle Application Express                      VALID

I still don’t know why the validation failed during the installation. Interesting thing is that this issue happened only on one system.

Tags: , , , , , , , , , ,

Next Page »