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: , , ,

Apr 15 2013

Oracle Database Gateways 11g R2 (11.2) Installation and Configuration for heterogeneous connection from Oracle to Microsoft SQL database

Category: 11g,Databaseittichai @ 5:33 pm


Install the Oracle Database Gateways 11g R2 (11.2). See the screen snapshots of the installation here.


  • During the installation, the following+ default initialization parameter file is created:
dg4msql = Database gateway for Microsoft SQL Server. 
If you choose a different database option, use the appropriate path name.
  • Copy initdg4msql.ora to a new file init[MSSQL].ora.
[MSSQL] can be any meaningful name easier to refer to, for example, mssqlsale. 
The filename will be in the format of init[MSSQL].ora.
copy initdg4msql.ora initmssqlsale.ora
  • Modify newly created file initmssqlsale.ora and modify or add the MS SQL server & database name.
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
# HS init parameters
  • Modify the listener.ora file. This can be that of the existing listener or new listener. Add SID_DESC to the appropriate place in the listener.ora file.
[MSSQL] =  Name of the new configuration file excluding the init and .ora.
For example, if the file name is initmssqlsale.ora, the [MSSQL] will be only mssqlsale.
[ORACLE_DG_HOME] = Oracle Database Gateway Home. This is NOT listener home.
[DRIVER] = dg4msql for Microsoft SQL Server


        (ADDRESS = (PROTOCOL = TCP)(HOST = DGHOST)(PORT = 1521))

  • Restart or reload the listener.
  • Validate using lsnrctl status.
C:\>lsnrctl status
LSNRCTL for 64-bit Windows: Version - Production
Services Summary...
Service "mssqlsale" has 1 instance(s).
 Instance "mssqlsale", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
  • On different Oracle database where we’d like to connect to this MS SQL database, create a new database link. The new database link will point to the the host where the gateway is installed.
create database link dblink_mssqlsale connect to "username" identified by "password"
using '
DGHOST = Host where the Oracle Database Gateway is installed.
mssqlsale = SID name mentioned in the listener.ora.
  • Try to query for a table.
select count(*) from information_schema.tables@dblink_mssqlsale;

Add more MS SQL databases

  • Repeat the above steps starting with copying the sample file into a new init file, and updating it with an appropriate host and database name.
copy initdg4msql.ora to initmssqlhr.ora
  • Update the listener.ora.
  • Restart or reload listener. Check using lsnrctl status.

Jan 21 2013

How long did Oracle materialized view refresh run?

Category: Database,Materialized Viewsittichai @ 9:38 am

The LAST_REFRESH_DATE column of the DBA_MVIEWS or the LAST_REFRESH column of the DBA_MVIEW_REFRESH_TIMES indicates the start refresh time. But what if we’d like to find out how long the refresh of the materialized view really takes. Well, we can query the DBA_MVIEW_ANALYSIS.

For Complete Refresh, the refresh duration will be in the FULLREFRESHTIM column of the DBA_MVIEW_ANALYSIS. For Fast Refresh duration, it will be in the INCREFRESHTIM column.

Both values are in seconds.

SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim
FROM dba_mview_analysis
WHERE owner='JOHN';

------------------------ ---------------------- -------------- -------------
MV_CHANGE_HISTORY        07-JAN-13 04.36.58 PM               0            36
MV_ITEM_HISTORY          07-JAN-13 04.36.58 PM               0             9

This shows that the recent refresh of the MV_CHANGE_HISTORY and MV_ITEM_HISTORY are the fast refreshes for 36 and 9 seconds respectively.

Put in one query to calculate and display the end time.

   last_refresh_date "START_TIME",
      WHEN fullrefreshtim <> 0 THEN
         LAST_REFRESH_DATE + fullrefreshtim/60/60/24
      WHEN increfreshtim <> 0 THEN
         LAST_REFRESH_DATE + increfreshtim/60/60/24
FROM all_mview_analysis
WHERE owner='JOHN';

----------------------- ---------------------- ---------------------- -------------- -------------
MV_CHANGE_HISTORY       07-JAN-13 04.36.58 PM  07-JAN-13 04.37.34 PM               0            36
MV_ITEM_HISTORY         07-JAN-13 04.36.58 PM  07-JAN-13 04.37.07 PM               0             9

Reference: How To Calculate MVIEW Refresh Duration? What Does DBA_MVIEWS.LAST_REFRESH_DATE and DBA_MVIEW_REFRESH_TIMES.LAST_REFRESH Indicate? [ID 1513554.1]

Tags: , , , ,

Aug 10 2012

Create Google Tasks by sending email to Google GMail Address

Category: Programming,Toolittichai @ 7:59 am

I use Google Tasks for a quick to-do list. It has clean interface and is easy to use. On desktop or laptop machine, it is built-in to Google Mail for a quick access. On smartphones, many to-do apps including Tasks N Todos sync with Google tasks.

The neat thing is that in the Google Mail, you can add Gmail messages into the task list very easily by selecting the messages and then using More Actions > Add to Tasks.

What if you want to add email messages from other mails like that from work, or Yahoo Mail, etc., it doesn’t seem that there is a straightforward way to do so.

I found this web log on the automated email-to-task with Google Apps Script by DJ Adams. The Google Apps Script is able to parse the email with a specific filtered label and create a task automatically. Let’s give it a try.

The overall process is as follows:

  • Two new Gmail labels need to be created – newtask and newtaskdone. When a new email is arrived, the filter will label it with newtask. Once the script processes this email, it will be re-labeled to newtaskdone so it won’t be processed again.
  • To make sure that only specified emails – not all – are processed, one of the hidden features of Gmail will be used. The filter will look for only +task@gmail.com (such as ittichai+task@gmail.com) in the TO address to apply new label. Read this on how to use “+” (plus ) or “.” (dot) in your Gmail address.
  • The Apps Script is from the Google Spreadsheet. The original post is to use only the email’s subject for the task’s title but I modified codes a bit to include the email’s body to be the task’s body as well.
  • One of the important things is to integrate the script with Google API so it will allow to use the Google Tasks’ API service and content.
  • Schedule it to run with a needed interval. I’m doing it every 30 minutes. Note that there is a courtesy limit of 5,000 requests per day. But this should be more than enough for a normal use.

Courtesy Limit of Tasks API

  • Now just simply forward all emails to+task@gmail.com if you want to add them into the task list. It should show up in the Google Tasks within your specified interval.

All step-by-step instructions can be found at my wiki site.

Tags: , , , , , ,

Next Page »