Joel Kallman
One more blog about Oracle Application Express.Joel R. Kallmanhttp://www.blogger.com/profile/01915290758512999160noreply@blogger.comBlogger28125
Updated: 55 min 27 sec ago
Application Express 3.1.2 Released
This afternoon we released Application Express 3.1.2. As for every patch set for Application Express, we released this in the form of a patch set on MetaLink (Patch Number 7313609) as well as a full release which can be downloaded from OTN. Thus:
The Application Express 3.1.2 patch set was applied to http://apex.oracle.com on Wednesday, 27-AUG-2008.
- If you have Application Express 3.1 or 3.1.1 installed, you'll want to download the APEX 3.1.2 patch set and apply it.
- If you have Application Express 3.0 or earlier installed (all the way back to HTML DB 1.5), you'll want to download and install the entire APEX 3.1.2 release from OTN.
- If you don't have Application Express installed, you'll want to download and install the entire APEX 3.1.2 release.
The Application Express 3.1.2 patch set was applied to http://apex.oracle.com on Wednesday, 27-AUG-2008.
Applicaiton Express 3.1.2 Released
This afternoon we released Application Express 3.1.2. As for every patch set for Application Express, we released this in the form of a patch set on MetaLink (Patch Number 7313609) as well as a full release which can be downloaded from OTN. Thus:
The Application Express 3.1.2 patch set was applied to http://apex.oracle.com on Wednesday, 27-AUG-2008.
- If you have Application Express 3.1 or 3.1.1 installed, you'll want to download the APEX 3.1.2 patch set and apply it.
- If you have Application Express 3.0 or earlier installed (all the way back to HTML DB 1.5), you'll want to download and install the entire APEX 3.1.2 release from OTN.
- If you don't have Application Express installed, you'll want to download and install the entire APEX 3.1.2 release.
The Application Express 3.1.2 patch set was applied to http://apex.oracle.com on Wednesday, 27-AUG-2008.
Oracle Application Express and Oracle MetaLink
When customers have questions about the scalability of Oracle Application Express or Oracle’s commitment to Application Express, the use of Application Express in Oracle MetaLink is often cited by other customers.
Recently, an e-mail from the Oracle MetaLink team went out to MetaLink users, inviting them to try out the new Oracle MetaLink and get their feedback. The new MetaLink is not written with Oracle Application Express, prompting some customers to write an e-mail to me and ask me what’s going on. There was also a recent discussion on one of the ODTUG mailing lists about this very topic. Here are some statements which will undoubtedly be inferred from this change:
Let me say that both of these statements are false.
Oracle acquired many companies and products over the past few years. Included in these acquisitions was software to help manage the customer relationship. This really became a business decision of either continuing to maintain and extend custom-written software in Oracle Application Express and PL/SQL, or use the off-the-shelf software that Oracle sells. As Tom Kyte often references the “Buy versus Build” decision, this one was even simpler – “Buy versus Own”.
That’s the decision in a nutshell. Anything else inferred from this change would be factually incorrect.
Recently, an e-mail from the Oracle MetaLink team went out to MetaLink users, inviting them to try out the new Oracle MetaLink and get their feedback. The new MetaLink is not written with Oracle Application Express, prompting some customers to write an e-mail to me and ask me what’s going on. There was also a recent discussion on one of the ODTUG mailing lists about this very topic. Here are some statements which will undoubtedly be inferred from this change:
- Oracle is no longer committed to Oracle Application Express
- Oracle Application Express couldn’t handled the scalability needs of Oracle MetaLink
Let me say that both of these statements are false.
Oracle acquired many companies and products over the past few years. Included in these acquisitions was software to help manage the customer relationship. This really became a business decision of either continuing to maintain and extend custom-written software in Oracle Application Express and PL/SQL, or use the off-the-shelf software that Oracle sells. As Tom Kyte often references the “Buy versus Build” decision, this one was even simpler – “Buy versus Own”.
That’s the decision in a nutshell. Anything else inferred from this change would be factually incorrect.
Using Oracle Application Express and the Oracle eBusiness Suite?
Are you using Oracle Application Express in conjunction with the Oracle eBusiness Suite? If so, then we'd like to hear from you!
David Peake recently blogged about this, and is collecting information from the Oracle Application Express community. The purpose of this is to gather evidence with the eventual goal of formally legitimizing the use of Oracle Application Express with the Oracle eBusiness Suite.
If you are currently using Oracle Application Express with the Oracle eBusiness Suite (or other Oracle Applications, for that matter), I encourage you to read his blog and take his one page survey. You can provide as much or as little information as you wish, and you have my personal assurances - no sales or marketing representative will call.
David Peake recently blogged about this, and is collecting information from the Oracle Application Express community. The purpose of this is to gather evidence with the eventual goal of formally legitimizing the use of Oracle Application Express with the Oracle eBusiness Suite.
If you are currently using Oracle Application Express with the Oracle eBusiness Suite (or other Oracle Applications, for that matter), I encourage you to read his blog and take his one page survey. You can provide as much or as little information as you wish, and you have my personal assurances - no sales or marketing representative will call.
Export data from Oracle Application Express and import via Oracle SQL*Loader
The other day, Josie from Oracle asked me:
"When I export the data, both as csv and xml the date is exported like this: 2005-08-29T00:00:00. sqlldr has a fit about that!"
What she was saying, in rather abbreviated form, was that she was having difficulty using the Oracle utility SQL*Loader to import a data file which was exported from Application Express -> Utilities -> Data Load/Unload. In particular, Josie was having difficulty loading the data of datatype DATE.
If you Unload to Text the EMP table, you'll get something that looks like:
"7839","KING","PRESIDENT","","1981-11-17T00:00:00","5000","","10"
"7698","BLAKE","MANAGER","7839","1981-05-01T00:00:00","2850","","30"
"7782","CLARK","MANAGER","7839","1981-06-09T00:00:00","2450","","10"
"7566","JONES","MANAGER","7839","1981-04-02T00:00:00","2975","","20"
"7788","SCOTT","ANALYST","7566","1982-12-09T00:00:00","3000","","20"
"7902","FORD","ANALYST","7566","1981-12-03T00:00:00","3000","","20"
"7369","SMITH","CLERK","7902","1980-12-17T00:00:00","800","","20"
"7499","ALLEN","SALESMAN","7698","1981-02-20T00:00:00","1600","300","30"
"7521","WARD","SALESMAN","7698","1981-02-22T00:00:00","1250","500","30"
"7654","MARTIN","SALESMAN","7698","1981-09-28T00:00:00","1250","1400","30"
"7844","TURNER","SALESMAN","7698","1981-09-08T00:00:00","1500","0","30"
"7876","ADAMS","CLERK","7788","1983-01-12T00:00:00","1100","","20"
"7900","JAMES","CLERK","7698","1981-12-03T00:00:00","950","","30"
"7934","MILLER","CLERK","7782","1982-01-23T00:00:00","1300","","10"
What should stand out to you is the value for the EMP.HIREDATE column. Why is it formatted this way?
To explain it simply, users of Application Express span all possible countries, territories and languages. A date format that works in one language may not work in another. A good example is a date value that contains an actual month name or an abbreviation of a month name. Today's date in English in Oracle date format DD-FMMonth-RRRR would be 17-July-2008. But change your language to German and you'll get 17-Juli-2008. If your data contains '17-July-2008' and you try to import it into a system with German language settings, it will fail - 'July' is not a valid month name in German.
For the export of DATE type data from Application Express, we needed to use something that works across all languages. We could have devised our own canonical date format. But instead, we decided to employ the international representation of date and time, namely, ISO 8601. So for those who scratch their head and wonder where that odd "T" comes from in the date value, here is your answer.
With this understanding in place of why this value is in this odd-looking format, let's get back to Josie's original question - how do I import this using SQL*Loader? Using a SQL *Loader SQL Operators and escape characters, it's quite easy. Here is a SQL*Loader control file which can be used to load the EMP data from above:
load data
infile "/tmp/emp.txt"
append into table emp
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(EMPNO, ENAME, JOB, MGR, HIREDATE "to_date(:HIREDATE,'rrrr-MM-dd\"T\"HH24:mi:ss')", SAL, COMM, DEPTNO)
The critical element in the field list, of course, is:
HIREDATE "to_date(:HIREDATE,'rrrr-MM-dd\"T\"HH24:mi:ss')"
which is simply applying a TO_DATE SQL operator to the HIREDATE field of the data file. Additionally, the data value will be represented as a string in a specific format. The double-quotes before and after the 'T' must be escaped, so SQL*Loader doesn't try to interpret that as the end of the expression.
Happy loading.
"When I export the data, both as csv and xml the date is exported like this: 2005-08-29T00:00:00. sqlldr has a fit about that!"
What she was saying, in rather abbreviated form, was that she was having difficulty using the Oracle utility SQL*Loader to import a data file which was exported from Application Express -> Utilities -> Data Load/Unload. In particular, Josie was having difficulty loading the data of datatype DATE.
If you Unload to Text the EMP table, you'll get something that looks like:
"7839","KING","PRESIDENT","","1981-11-17T00:00:00","5000","","10"
"7698","BLAKE","MANAGER","7839","1981-05-01T00:00:00","2850","","30"
"7782","CLARK","MANAGER","7839","1981-06-09T00:00:00","2450","","10"
"7566","JONES","MANAGER","7839","1981-04-02T00:00:00","2975","","20"
"7788","SCOTT","ANALYST","7566","1982-12-09T00:00:00","3000","","20"
"7902","FORD","ANALYST","7566","1981-12-03T00:00:00","3000","","20"
"7369","SMITH","CLERK","7902","1980-12-17T00:00:00","800","","20"
"7499","ALLEN","SALESMAN","7698","1981-02-20T00:00:00","1600","300","30"
"7521","WARD","SALESMAN","7698","1981-02-22T00:00:00","1250","500","30"
"7654","MARTIN","SALESMAN","7698","1981-09-28T00:00:00","1250","1400","30"
"7844","TURNER","SALESMAN","7698","1981-09-08T00:00:00","1500","0","30"
"7876","ADAMS","CLERK","7788","1983-01-12T00:00:00","1100","","20"
"7900","JAMES","CLERK","7698","1981-12-03T00:00:00","950","","30"
"7934","MILLER","CLERK","7782","1982-01-23T00:00:00","1300","","10"
What should stand out to you is the value for the EMP.HIREDATE column. Why is it formatted this way?
To explain it simply, users of Application Express span all possible countries, territories and languages. A date format that works in one language may not work in another. A good example is a date value that contains an actual month name or an abbreviation of a month name. Today's date in English in Oracle date format DD-FMMonth-RRRR would be 17-July-2008. But change your language to German and you'll get 17-Juli-2008. If your data contains '17-July-2008' and you try to import it into a system with German language settings, it will fail - 'July' is not a valid month name in German.
For the export of DATE type data from Application Express, we needed to use something that works across all languages. We could have devised our own canonical date format. But instead, we decided to employ the international representation of date and time, namely, ISO 8601. So for those who scratch their head and wonder where that odd "T" comes from in the date value, here is your answer.
With this understanding in place of why this value is in this odd-looking format, let's get back to Josie's original question - how do I import this using SQL*Loader? Using a SQL *Loader SQL Operators and escape characters, it's quite easy. Here is a SQL*Loader control file which can be used to load the EMP data from above:
load data
infile "/tmp/emp.txt"
append into table emp
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(EMPNO, ENAME, JOB, MGR, HIREDATE "to_date(:HIREDATE,'rrrr-MM-dd\"T\"HH24:mi:ss')", SAL, COMM, DEPTNO)
The critical element in the field list, of course, is:
HIREDATE "to_date(:HIREDATE,'rrrr-MM-dd\"T\"HH24:mi:ss')"
which is simply applying a TO_DATE SQL operator to the HIREDATE field of the data file. Additionally, the data value will be represented as a string in a specific format. The double-quotes before and after the 'T' must be escaped, so SQL*Loader doesn't try to interpret that as the end of the expression.
Happy loading.
Oracle eBusiness Suite and mod_plsql
There have been a fair number of questions and concerns about the use of mod_plsql and the Oracle eBusiness Suite. And unfortunately, this has created some confusion about what is and is not supported by Oracle. I know a fair number of customers, both large and small, who are successfully using Oracle Application Express and the Oracle eBusiness Suite just fine.
Steven Chan, who is a Senior Director in the Oracle Applications group, has recently blogged about the availability of a new whitepaper on MetaLink. The whitepaper entitled mod_plsql and Oracle E-Business Suite Release 12 (MetaLink Note 726711.1) is intended to discuss many of the issues around mod_plsql and the Oracle eBusiness Suite.
Steven Chan, who is a Senior Director in the Oracle Applications group, has recently blogged about the availability of a new whitepaper on MetaLink. The whitepaper entitled mod_plsql and Oracle E-Business Suite Release 12 (MetaLink Note 726711.1) is intended to discuss many of the issues around mod_plsql and the Oracle eBusiness Suite.