Toby's Tumblr

Jan 09

Button Hotkeys in SQL Developer on OS X

In Windows, most applications provide menu and button accelerator keys that allow you to control the application without having to use a mouse. Java Swing apps like SQL Developer allow you to do the same, ostensibly in a cross-platform compatible manner.

One place I use this frequently is the Enter Binds dialog window that pops up when running a query using bind variables.

In Java 6 on OS X, the hotkey for pressing the Help and Apply buttons was OPTION-H and OPTION-A, respectively.

I initially thought this capability had been broken in Java 7 on OS X, but I discovered today that it has been merely changed to CONTROL-OPTION-H and CONTROL-OPTION-A.

Does anyone know if this is a configurable property of the application or Java virtual machine? If possible, I’d like to set it back to the simpler 1.6 way.

Dec 17

[video]

Dec 05

[video]

Nov 13

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.

A similar technique exists for querying simple XML files, taking advantage of Oracle’s XML DB features. HT to odie_63 on the XML DB forums for introducing me to this method.

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 employees.xml.

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_INFO variable 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.

Nov 12

Restricting Access to BI Publisher Reports

In my last post I described how to configure BI Publisher to authenticate against an LDAP server, typically a corporate Active Directory server. Aside from authentication, you can also use LDAP group membership to determine who has access to a given report. Here’s how you do it.

1. Create a group in your LDAP/AD directory and assign your target users to that group.
2. Revisit your LDAP authentication settings. These are located on the Admin Tab, on the Security Configuration page. In particular, look at the value for Group Search Filter.

e.g.
(&(objectClass=group)(|(CN=XMLP*)(CN=DEPT_GROUP_*)))

In case you don’t recognize it, this is an LDAP query string. This query string determines which groups BI Publisher “sees”, or recognizes, when setting up report access. The groups returned by this query will determine the set from which you can choose when configuring which groups see which reports.

Here is a deconstruction of the query string:

(& Logical AND over the following conditions

(objectClass=group) Return LDAP groups, as opposed to users or computers, etc.

(| Logical OR over the following conditions

(CN=XMLP*) Return groups whose name starts with “XMLP”

(CN=DEPT_GROUP_*) Return groups whose name starts with “DEPT_GROUP_”

A quick primer on LDAP query syntax can be found here.

1. Modify this query string to include the group you just created.
2. Bounce BI Publisher.
3. Log in to BI Publisher as an administrator. Go to the Admin tab, and then to the Roles and Permissions page.
4. Click on the role/group you just created, and using the shuttle control on the next page, select which reports your new group should have access to. If necessary, you may need to revoke permissions on your report from other groups, or move your report to the top level of the report folder hierarchy in order to segregate access appropriately.

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:

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

XMLP_ADMIN
XMLP_DEVELOPER
XMLP_SCHEDULER
XMLP_ANALYZER_EXCEL
XMLP_ANALYZER_ONLINE
XMLP_TEMPLATE_DESIGNER

You can read more about those roles and what they do in the Oracle Documentation.

Oct 26

Use Firebug’s console.debug Command to Analyze Javascript, DOM Objects

Occasionally when debugging my Javascript code I need to be able to analyze the properties of an unknown object; for instance, an unexpected DOM object passed to an event handler. How do you analyze the properties of an object when you don’t know what the object is? I was looking for a way to list all the properties of a generic Javascript or DOM object when I stumbled across this blog post by Andrew Peters. In it, he very clearly outlines two ways of doing this, but the one I found most useful was Firebug’s console.debug command. You’ll need to use Firefox with the Firebug extension for this, but the results are impressive.

When you want to debug an object, simply pass it as a parameter to console.debug. Here is an example where I wanted to find out about the event trigger object passed to my event handler:

After triggering the event, I opened the Firebug window and navigated to the console. From there I could drill down on the displayed results to get information about my unknown DOM object.

Turns out in this case that the event handler was attached to the wrong object, due to the “id” value being placed one level above in the DOM hierarchy than I was expecting. Tricky, but I was able to spot the problem immediately using this method.

(Source: andrewmpeters.com)

Aug 27

BI Publisher: “Job scheduling failed because the user has no permission to access this report”

You can get this error if you are trying to schedule a report and your BI Publisher installation uses LDAP authentication.

To avoid it, make sure that the “Administrator” account you use to connect to your LDAP server is in the XMLP_ADMIN group. The LDAP settings are located on the Admin tab, under Security Settings / Security Configuration.

Jul 25

Fix for Page Load Issues in Safari 5.1.7 -

Lately I had been experiencing strange problems when clicking on links in Safari 5.1.7. Usually the progress indicator would appear for only a second with no obvious results, or else never appear at all. Pages would sometimes load, but after an unusual delay and with no indication that the browser was actually doing anything. I switched to Chrome as my default browser for a few days, but that was about all I could stand. A little research on the forums showed this fix, which appears to have worked for me.

Jul 20

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.

Read More