Using Jaspersoft’s free iReport Designer tool and the open source PL-JRXML2PDF project, you can generate PDF reports from Oracle Apex for free, without having to install the JasperReports and JasperReports Server components. Watch my video tutorial to find out how.
Query an XML File Like An External Table
Oracle’s external table feature allows you to treat structured files on the database server as tables for query and DML purposes. To use an example from the linked documentation, a tab-delimited file like this:
56november, 15, 1980 baker mary alice 09/01/2004 87december, 20, 1970 roper lisa marie 01/01/1999
…could be queried like this:
SELECT employee_number, employee_first_name, substr(employee_middle_name, 1, 1), employee_last_name, employee_hire_date, to_date(employee_dob,'month, dd, yyyy') FROM employees;
After defining the external table like this:
CREATE TABLE employees (employee_number CHAR(5), employee_dob CHAR(20), employee_last_name CHAR(20), employee_first_name CHAR(15), employee_middle_name CHAR(15), employee_hire_date DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY def_dir1 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS (employee_number CHAR(2), employee_dob CHAR(20), employee_last_name CHAR(18), employee_first_name CHAR(11), employee_middle_name CHAR(11), employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy" ) ) LOCATION ('info.dat') );
Assuming of course that the file’s name is
info.dat, and its path on the filesystem has already been created as a directory object
DEF_DIR1 in the database.
Suppose you have a simple XML file (elements, but no attributes; no complex column types - it could work for the preceding, but I’m not sure) like so.
<Employees> <Employee> <id>1234</id> <lastname>Jetson</lastname> <firstname>George</firstname> <department>10</department> <salary>50000.00</salary> </Employee> <Employee> <id>1235</id> <lastname>Crackorn</lastname> <firstname>James</firstname> <department>10</department> <salary>40000.00</salary> </Employee> <Employee> <id>1236</id> <lastname>Hoffman</lastname> <firstname>Burl</firstname> <department>20</department> <salary>75000.00</salary> </Employee> </Employees>
Place it on the database server in a readable directory, e.g.
/home/oracle. Name it
Now execute the following in SQL*Plus:
SQL> CREATE OR REPLACE DIRECTORY XML_DIR AS '/home/oracle'; SQL> CREATE OR REPLACE VIEW employees_v AS SELECT * FROM XMLTable('/Employees/Employee' passing xmltype( bfilename('XML_DIR','employees.xml') , nls_charset_id('AL32UTF8') ) columns id number path 'id' , lastname varchar2(30) path 'lastname' , firstname varchar2(30) path 'firstname' , department number path 'department' , salary number path 'salary' ); SQL> SELECT * FROM employees_v; ID LASTNAME FIRSTNAME DEPARTMENT SALARY ---- -------- --------- ---------- ------ 1234 Jetson George 10 50000 1235 Crackorn James 10 40000 1236 Hoffman Burl 20 75000
You can modify construction of the view to read from multiple files by using a simple
UNION operator, or you can make the source filename dynamic so you can switch datasets at runtime. Here’s how you might do something like that:
SQL> CREATE OR REPLACE VIEW employees_v AS SELECT * FROM XMLTable('/Employees/Employee' passing xmltype( bfilename('XML_DIR', userenv('CLIENT_INFO')) , nls_charset_id('AL32UTF8') ) columns id number path 'id' , lastname varchar2(30) path 'lastname' , firstname varchar2(30) path 'firstname' , department number path 'department' , salary number path 'salary' );
Then at runtime, specify the file you want to use like this:
SQL> exec dbms_application_info.set_client_info('employees_01.xml'); SQL> SELECT * FROM employees_v; ID LASTNAME FIRSTNAME DEPARTMENT SALARY ---- -------- --------- ---------- ------ 1234 Jetson George 10 50000 1235 Crackorn James 10 40000 1236 Hoffman Burl 20 75000 SQL> exec dbms_application_info.set_client_info('employees_02.xml'); SQL> SELECT * FROM employees_v; ID LASTNAME FIRSTNAME DEPARTMENT SALARY ---- -------- --------- ---------- ------ 1234 Ringwald Malory 30 63000 1235 Dulles Bob 10 100000 1236 Stein Frank 50 55000
Why might this be useful? Suppose you have an arbitrary number of identically structured XML files in a directory that you want to import into a relational database table. Using a shell script you can, for each file in the directory,
- log in to SQL*Plus
- set the
CLIENT_INFOvariable to the name of the current file
- perform a simple insert into your table reading all records from the view
You might even create a cron job to monitor a particular directory for new XML files, processing them into tables and then deleting or archiving the files when done.
Configure Oracle BI Publisher to Authenticate With Active Directory
- Log in to BI Publisher as an administrator.
- Click on the Admin tab.
- Click on Security Configuration.
- For Security Model, choose LDAP and enter the following parameters:
Your company’s LDAP server and port.
|Administrator Username||CN=ADMIN,OU=Users,OU=Shared Services,DC=mycompany,DC=com
The fully qualified DN of the account you use to sign in to LDAP.
The password for the above account.
|Distinguished Name for Users||DC=mycompany,DC=com
The “root” level at which to search for users.
|Distinguished Name for Groups||OU=Global Groups,DC=mycompany,DC=com
The “root” level at which to search from groups.
|Group Search Filter||(&(objectClass=group)(|(CN=XMLP)(CN=DEPT_GROUP_)))
LDAP query string defines which groups are relevant to BI Publisher.
|Group Attribute Name||cn|
|Group Member Attribute Name||member|
|Member Of Group Attribute Name|
|Group Description Attribute Name||description|
|JNDI Context Factory Class||com.sun.jndi.ldap.LdapCtxFactory|
|Group Retrieval Page Size|
|attribute used for RDN||sAMAccountName
Allows users to log in to BI Publisher using their corporate AD account name.
|Automatically clear LDAP cache||Checked|
|Ldap Cache Interval||1|
|Ldap Cache Interval Unit||Hour|
|Default User Group Name|
|Attribute Names for Data Query Bind Variables|
- Bounce BI Publisher.
- Try logging in with your corporate AD credentials. Remember, you need to be assigned to one of the following special groups in Active Directory if you want developer or admin privileges in BI Publisher:
You can read more about those roles and what they do in the Oracle Documentation.
Easy Fix for High CPU Usage in SQL Developer on OS X
I’ve been fighting for a couple of years now with an annoying performance bug in SQL Developer for OS X. After using the application for a while CPU usage would spike to around 100%, even when idle. This is a known issue associated with the “Look and Feel” preference.
Run Oracle Forms 11g Applications on OS X Lion
Need to run your company’s Oracle Forms application from your Mac, and can’t? Annoyed with having to crank up a virtual machine for just that one task? Even with the latest published Java version for OS X (1.6.0_33, as of today), you may find that some of the newer Forms applications just won’t run. Well, fret no longer. A solution may already be here, at least for Safari and Firefox.
Although it’s not a strictly certified configuration, you may have better luck by running the JRE 1.7 Java plug-in, which was recently made available as a pre-release download. This works for OS X Lion 10.7.3 and above. Download the plug-in from here and follow the simple instructions on the page to install it. Now give your application another try, and hopefully you’ll get the results I did!
OS X Software for Oracle Developers
One of the great things about working for Enkitec is that it’s a very Mac-friendly shop. When I arrived there in 2007 there were a handful of us running Macs and the attitude at the top seemed to be respectful, but skeptical. They allowed me to use my own laptop, which at the time was a 15-inch Intel Core2 Duo Macbook Pro, with the understanding that I could request a standard-issue Dell laptop at any time should the need arise. It never did. Since that time a large number of my coworkers have made the switch, abandoning their bulky, slow Dell Latitude laptops for 13-inch Macbook Pros, as have both owners of the company.
DML Error Logging
Introduced with Oracle 10gR2, DML Error Logging allows you to make standard SQL
MERGE statements behave more like the SQL*Loader utility, logging errors to a table rather than failing and rolling back the first time an error condition is encountered.
Oracle’s App Server Offerings: A High-Level Comparison
Ran across this post from the Dutch software and services company Yenlo that compares and contrasts Oracle’s major application server offerings. It would serve as a good starting point for deciding whether you truly need to pay the enormous licensing costs associated with a professional-grade application server platform, or if you were just wondering how to reconcile the continued existence of Oracle Application Server with WebLogic.
Table Functions: 9i Feature Worth Revisiting (Part 2)
In part I of this series I showed a simple example of how table functions can be used to treat PL/SQL collection types as tables in a SQL query. We used a nested table collection type to which we manually added a few entries. In this example, I intend to show a more practical use of table functions using all three PL/SQL collection types: associative arrays, nested tables, and VARRAYs.
Table Functions: 9i Feature Worth Revisiting (Part 1)
Yes, they’ve been around for a while, but if you haven’t made use of table functions lately you might want to reacquaint yourself with this old gem. Table functions allow you to query the contents of PL/SQL collection types using SQL’s TABLE operator. Here is a simple example.