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.
Turning customers into rockstars, with Oracle Database, SQL and Oracle APEX.
Tuesday, July 29, 2008
Thursday, July 17, 2008
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:
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:
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.
Monday, July 14, 2008
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.