TJMOracle

Hello, and thanks for visiting my Tumblr! My name is Toby, and I am a consultant with Accenture Enkitec Group, an Oracle partner headquartered in Irving, Texas specializing in DBA services and Exadata implementations. I have worked as an Oracle developer-consultant for 17 years in the Dallas/Ft. Worth area. In this blog I hope to share some of my experiences, perspectives, and solutions to problems I have encountered at the various places I have worked.

[tjmoracle]

What to do when the Juniper Network Connect app on OS X does not connect

If you use Juniper's Network Connect app to connect to your office VPN over HTTPS, you might occasionally experience strange errors like "A software error caused the Network Connect tunneling service to terminate." Shortly after that, the application may either crash or prompt you for login credentials again.

The solution involves removing all of the Network Connect files and allowing your browser to download and install the app all over again, as it did the first time you tried to connect to your office VPN.

(Note: as a precaution you may want to simply move the Network Connect.app file from /Applications to ~/Desktop, just in case your browser is not able to download it as needed.)

To remove all files associated with the Network Connect application, execute the following commands in Terminal.app.

# sudo rm -rf /Applications/Network\ Connect.app

# sudo rm -rf /usr/local/juniper

# sudo rm -rf ~/Library/Application\ Support/Juniper\ Networks

# sudo rm ~/Library/Preferences/net.juniper.NetworkConnect.plist

Now try connecting to your office VPN again and the browser should prompt you to redownload the application. When I tried this I got an error the first time on Firefox saying the installation failed, but I was able to manually verify that the files were in place. My second attempt succeeded. Go figure.

[tjmoracle]

Linux, Unix: Use PS to List Top Memory, CPU Eating Processes

The ps command is a versatile and configurable way to look at the processes running on your system, though I've generally gotten by with a lazy ps -ef to find out whatever I needed to know. Recently, though, a client's server began running out of memory, and as a number of processes were running on the server, the culprit was not immediately clear. I wanted to provide them with a quick, simple diagnostic tool they could use the next time they received an alert.

If you look at the man page for ps you'll find a number of options available for sorting the list of processes returned as well as for filtering the columns displayed.

Some examples of ps options, from RHEL 5.6:

-e select all processes -a select all "child" processes -U <user> select all processes for a specified user -p <pid> select by process id -f full-format listing; adds columns and shows process arguments -o user-defined format  

Many others are available, of course, but in order to keep it simple for the client I wanted to show the top 10 processes in terms of memory utilization, with the pid, memory usage, and process name displayed. Here is what I came up with:

$ ps -eo pid,user,pmem,args O R | tail -n 10

And here is an explanation:

ps shows running processes       -e show all processes       -o pid,user,poem,args show the pid, %mem, and process columns       O R sort by memory usage (ascending) tail shows only the last lines       -n 10 show the last 10 lines  

I liked the concept so much that I decided to adapt it for OS X, where the ps command takes a slightly different set of arguments. To my .bash_profile file I added the following lines:

alias psmem="while :; do clear; ps -ecm -o %mem,rss,pid,user,state,args | head -n 10; sleep 5; done" alias pscpu="while :; do clear; ps -ecr -o %cpu,pid,user,state,args | head -n 10; sleep 2; done"

I have one alias for retrieving my top memory-utilizing processes and one for my top CPU-utilizing processes. The while loop in the alias implements a functionality similar to that of the Linux watch command, clearing the screen and running the process indefinitely until it is forcibly quit. For my own purposes I wanted to display some additional columns, like username and process state. I also changed the sort ordering to display the largest processes at the top of the list.

[tjmoracle]

A Texas 4th of July

[tjmoracle]

A nice solution for viewing man pages that eliminates the necessity of having to download 3rd party apps or Dashboard widgets

[tjmoracle]

Fix SQL Developer on a Retina Display Mac(book)

I just purchased a new Macbook Pro 13-inch with Retina Display and was horrified to see how fuzzy SQL Developer looked on the high-res screen. Thanks to Thunderforge on the Stack Overflow forums for this tip:

Java 7u40 Early Access now supports HiDPI (Retina) displays, as shown by this changelog. So retina support is now available on Java 6, Java 7u40 or higher, and Java 8.

I am currently running the Early Adopter release of SQL Developer 4.0 which requires JDK 1.7. I'm not sure what the state of Java 8 is on the Mac, so I opted to upgrade my JDK from 1.7.0_25 to 1.7.0_40. After that I had nice, sharp lines back in SQL Developer and all my other Java apps.

Thanks to Oracle's Java team for addressing this!

[tjmoracle]

Resetting Page Numbering Between Document Sections in BI Publisher's Template Builder for Word

Under normal circumstances, it is fairly easy in Microsoft Word to reset page numbering when starting a new section of your document. However, such a simple thing can be non-trivial when dealing with the BI Publisher Template Builder plugin for Microsoft Office (available from Oracle — see BI Publisher Desktop).

In short, doing this the normal way did not work for me, and in researching the issue I ran across a number of folks who were having the same problem. Page numbering was simply not being reset between sections, or worse, did not take into account a fluid document size based upon available data.

One suggestion I read said to wrap the page number cells in a BI Publisher repeating group using for-each@section, like so:

<?for-each@section:ID?> x/y <?end for-each?>

But this also didn't work for me. Finally I had to edit the RTF file directly.

The RTF specification was of some help to me in doing this, as well as this explanatory page from Microsoft.

RTF files can be a little confusing, but most of the "junk" in the files is just special codes for formatting the text. I was able to find my way around easily enough by searching for labels or keywords from the document. By searching for the first few words from the second section of the document (in this case, "Sender's Receipt"), I was able to spot where the section break should go.

Checking the RTF spec, I could see that the \page directive shown above was the page break between two sections of my document where I wanted to reset the page numbering. To create the section break, I inserted the following RTF directives at that point:

\sect \sectd \pgnstarts1\pgnrestart

Since the default behavior of the section break is to start on a new page, I found I had to remove the \page directive to avoid an extra page being inserted between the document sections.

Interestingly, resetting the page numbering also works when calculating total number of pages, whereby the total is from the section, not the entire document, even though the NumPages field is used instead of SectionPages! Go figure, but it worked for me.

[tjmoracle]

I don't think I've ever enjoyed a spring as much as this one.

[tjmoracle]

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.

[tjmoracle]

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.

[tjmoracle]

Fall is almost over, but it hardly feels like winter here in Dallas.

[tjmoracle]

Query an XML File Like An External Table

Oracle's [external table feature](http://docs.oracle.com/cd/B28359_01/server.111/b28319/et_concepts.htm) 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](http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/toc.htm) features. _HT to [odie_63](https://forums.oracle.com/forums/profile.jspa?userID=695787) 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. 1234 Jetson George 10 50000.00 1235 Crackorn James 10 40000.00 1236 Hoffman Burl 20 75000.00 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.

[tjmoracle]

Restricting Access to BI Publisher Reports

In [my last post](http://tjmoracle.tumblr.com/post/35571115921/configure-oracle-bi-publisher-to-authenticate-with) 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.](http://wiki.pentaho.com/display/ServerDoc2x/LDAP+Search+Filter+Syntax) 3. Modify this query string to include the group you just created. 4. Bounce BI Publisher. 5. Log in to BI Publisher as an administrator. Go to the Admin tab, and then to the Roles and Permissions page. 6. 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.

[tjmoracle]

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: Setting | Example Value ------------------------------------------------|------------------------------------------------------------- URL | ldap://ad.mycompany.com:389 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. Administrator Password | myxlplyx 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: 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](http://docs.oracle.com/cd/E10383_01/doc/bip.1013/b40017/T421739T475591.htm).

[tjmoracle]

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.

[tjmoracle]

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. 

[tjmoracle]

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.

[tjmoracle]

Easy Fix for High CPU Usage in SQL Developer on OS X