Tag: Oracle

Oracle Query Returns JSON

I’m using Oracle a lot in my new job; and, in the process, I am learning about a lot of neat Oracle database features. Today, I discovered json_array. Wrap the selected columns in json_array followed by a name for the result set

SELECT json_array(C1.circuit_design_id,

circuit_position.CIRCUIT_NODE_STATUS, circuit_position.circuit_design_id_3) jsonCircuitResults

FROM circuit C1

LEFT OUTER JOIN circuit_position on C1.circuit_design_id = circuit_position.circuit_design_id

WHERE C1.circuit_design_id = ‘54535525’ and circuit_position.circuit_design_id_3 is not null;

The response is JSON –

 

Upgrading Oracle SQL Developer

I’ve been using an Oracle database more in my new position … which means I’ve got the Oracle SQL Developer tool installed on my computer. My first upgrade was available yesterday … and it didn’t work. Not like threw an error, but like double click on the executable and nothing happens. It silently exits.

Turns out there’s something in appdata that needs to be cleared. I don’t run multiple versions of SQL Developer, so I could just blow away “%userprofile%\appdata\roaming\SQL Developer” and “%userprofile%\appdata\roaming\sqldeveloper” to clear whatever needs to be cleared. Click the icon and the program finally runs.

DSEE 6.3 To OUD 11g Transition

There’s no direct path to replicate data from DSEE6.3 to OUD11g. Not unreasonable since DSEE is the Sun product based on the Netscape Directory Server and OUD is the Oracle product based on OpenLDAP – they weren’t exactly designed to allow easy coexistence that would permit customers to switch from one to the other. Problem is, with Oracle’s acquisition of Sun & axing the DSEE product line … customers *need* to interoperate or do a flash cut.
Since our Identity Management (IDM) platform was not able to prep development work and implement their changes along with the directory replacement, a flash cut was right out. I’ve done flash cuts before — essentially ran two completely different directories in parallel with data fed from the Identity Management platform, tested against the new directory using quick modification to the OS hosts file, then reconfiguring the virtual IP on the load balancer to direct the existing VIP to the new service hosts. Quick/easy fail-back is to set the VIP to the old config and sort out whatever is wrong on the new hosts. A lot lower risk than a traditional ‘flash cut’ approach as long as you trust the IDM system to keep data in sync. But lacking an IDM system, flash cut is typically a non-starter anyway.
There is a migration path. Oracle put some development effort into the DSEE product line prior to discontinuing it. DSEE7 was the Sun distributed “next version”. It was not widely deployed prior to the Oracle acquisition. Oracle took over DSEE7 development but called it DSEE11 (to match the OUD version numbering, I guess?). Regardless of the rational, you’ll see the “next version” DSEE product referred to as both DSEE7 and DSEE11.
There’s not a direct replication between Oracle DSEE11 and Oracle OUD11. Oracle created a “replication gateway” that handles, among other things, schema name mapping (only Netscape would use attribute names like nsAccountLockout, and that nomenclature carried through to the Sun product). Oracle did a decent job of testing DSEE11<=>OUD11 Replication Gateway interoperability. I don’t know if they just assumed DSEE6 would work because DSEE11 did or if they assumed the installation base for DSEE6 was negligible (i.e. didn’t bother to test older revisions) but we found massive bugs in the replication gateway working with DSEE6. “You cannot import the data to initialize the OUD11 directory” type of bugs which I was willing to work around by manually editing the export file, but subsequent “updates do not get from point ‘A’ to point ‘B’ bugs too. The answer from Oracle was essentially “upgrade to DSEE11” … which, if i could flash-cut upgrade DSEE6 to DSEE11 (see: IDM platform couldn’t do that), I could just cut it to OUD11 and be done. Any non-trivial change was a non-starter, but Oracle wasn’t going to dump a bunch of development time into fixing replication for a dead product to their shiny new thing.
I worked out a path that used tested and working components — DSEE6 replicated just fine with DSEE11. DSEE11 replicated just fine with the OUD11g replication gateway, and the OUD11g replication gateway replicated fine with OUD11g. Instead of introducing additional expense and time setting up dedicated replication translation servers, I installed multiple components on the new servers. There is a DSEE11 directory on one of the new OUD servers, the replication gateway on another one of the new OUD servers, and (of course) the OUD11g directory that we actually intended to run on the new servers is on those new OUD servers.
This creates additional monitoring overhead – watching replication between three different directories and ensuring all of the services are running – but allows the IDM platform to continue writing changes to the DSEE6.3 directory until they are able to develop and test changes that allow them to use OUD11g directly.