Tag: Oracle

Oracle – LISTAGG

I needed to collapse multiple rows into a single row — the circuits within a diversity set are stored within the ds_dvrsty_set_circuit table as individual rows & the ds_dvrsty_set_id links the multiple rows. What I wanted was a set ID, set name, and the list of circuits within the set.

To accomplish this, I found LISTAGG which is a little bit like STUFF in MSSQL. This query produces a single row for each diversity set that contains the set ID, the set name, and a comma delimited list of set members.

SELECT
     ds_dvrsty_set_circuit.ds_dvrsty_set_id,
     (select ds_dvrsty_set.ds_dvrsty_set_nm from ds_dvrsty_set where ds_dvrsty_set_id = ds_dvrsty_set_circuit.ds_dvrsty_set_id) as set_name,
     LISTAGG(ds_dvrsty_set_circuit.circuit_design_id,  ',') WITHIN GROUP(ORDER BY ds_dvrsty_set_circuit.ds_dvrsty_set_id) AS member_circuits
FROM
     ds_dvrsty_set_circuit
     left outer join ds_dvrsty_set on ds_dvrsty_set.ds_dvrsty_set_id = ds_dvrsty_set_circuit.DS_DVRSTY_SET_ID
WHERE
     ds_dvrsty_set_circuit.ds_dvrsty_set_id in (select distinct ds_dvrsty_set_id from ds_dvrsty_set_circuit where circuit_design_id in (14445678, 5078901) )
AND
     ds_dvrsty_set.ds_dvrsty_set_nm like '43%'
GROUP BY
     ds_dvrsty_set_circuit.ds_dvrsty_set_id
ORDER BY
     ds_dvrsty_set_circuit.ds_dvrsty_set_id;

Voila — exactly what I needed. If the searched circuit design IDs appear in more than one set, there is a new row for each set ID.

Oracle – Select Top

I discovered the “rownum” trick early in my usage of Oracle databases — especially useful for sampling data to see what’s in there, something like “select * from dataTable where rownum < 6” gets you the first five records. But that’s not suitable if you want to sort the records. In this particular case, I have a series of names. I want to find the highest number value in the series so I can name my object with the next sequential name.

Enter “fetch first”  … this appears to be available since 12c (so older database installations may still require a more convoluted solution):

SELECT set_name from set_data
WHERE set_name LIKE 'Something-With-A-Series-%'
ORDER BY set_name DESC
fetch first 1 row only;

Which returns the last name in the series.

Oracle – Group By Having

I needed a query to find records where duplicate name values exist. I know how to group by and count, but the table has millions of records. I don’t want the 99% of the data where no duplication occurs. By using “having” in conjunction with “group by”, I am able to restrict the output to the groups that match my criterion.

select display_name, count(display_name) from circuit
group by display_name
having count(display_name) > 1;

My result set is the display name & occurrence count for that display name without all of the ‘good’ records where there’s a unique display name. (Yes, I know uniqueness could be enforced. The real scenario isn’t this straight-forward. There are times where the display name should be the same and I’ve got additional filters that drop out those cases).

 

Oracle – Adding Fixed String To Column Data

I had a request to drop data from a SQL query into an Excel spreadsheet — the initial request had a column that included different text depending on row data. Since Box::Spout deals well with huge volumes of data (I write millions of rows in some reports), I use it pretty exclusively. Writing data to each column individually means I’ve got to retain a list of column names, and I would rather not do that. I can dump the column names into a header row then dump the entire database row into the next spreadsheet row. This works since a 1:1 correlation between database rows and Excel rows. I’ll go through after the fact and update a specific cell based on data in other cells, but I don’t want to insert a column.

Luckily, there’s an easy way to add a placeholder column to my database output:

SELECT DISTINCT MAX(DLR1.ISSUE_NBR) MAX_ISSUE_NBR, DLR.ISSUE_NBR, ‘MatchIndicator’ as “MatchIndicator”, DLR.EQUIPMENT_ID, EQ.EQUIPMENT_ID, C.CIRCUIT_DESIGN_ID, C.EXCHANGE_CARRIER_CIRCUIT_ID, C.STATUS, C.RATE_CODE, C.SERVICE_TYPE_CATEGORY, C.SERVICE_TYPE_CODE, NL.LOCATION_ID, NL.CLLI_CODE, DLR.LOCATION, DLR.BLOCK_IND

Oracle Password Expiry – Sandbox Server

Oracle 11g seems to ship with password expiry enabled — which is a very good thing for production systems. I’ve even written some code to maintain our system account password (scripts are grabbing the password from a not-clear-text storage facility anyway, so it wasn’t a big deal to add an n-1 password and move the current stashed password into the n-1 column, change the account password, and stash the updated password in the current password location … now my system ID password is updated by a monthly cron job, no one actually knows the password {although anyone could find it, so I would run the password cycle script when individuals leave the group}). But I’m a lot lazier about this stuff in my sandbox. Proof of concept code has clear text passwords. But the server is bound to localhost & there’s no real data in, well, anything.

I started seeing lines in my error log indicating the password would expire. Aaaand that’s how I learned that password expiry was enabled by default now.

[Sat Apr 18 07:42:59 2020] [error] [client 127.0.0.1] PHP Warning: oci_connect(): OCI_SUCCESS_WITH_INFO: ORA-28002: the password will expire within 7 days in /var/www/vhtml/…/file.php on line 191, referer: …

I’m going to disable password expiry because it’s a sandbox. For a real system, obviously, this may not be a stellar idea.

select USERNAME, ACCOUNT_STATUS, PROFILE from dba_users where USERNAME = 'SampleUser';

 

USERNAME ACCOUNT_STATUS PROFILE
SampleUser EXPIRED(GRACE) DEFAULT

 

Note the account status “EXPIRED(GRACE)” — that’s why I am getting the error shown above. Grab the profile name — it’s a sandbox, so 99% sure it’s going to be ‘DEFAULT’ and alter that profile with an unlimited password expiration:

alter profile <profile_name> limit password_life_time UNLIMITED;

Except that didn’t actually stop the error. Turns out you’ve still got to change the password once the account has been flagged as expired (or let the password expire and then unlock the account … but I was looking at the log because I’m debugging something, and I wanted the error to stop *right now*).

alter user SampleUser identified by N3W_P@s5_w0rD;

 

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

SELECT JSON_OBJECT
(KEY 'CDID" VALUE C1.circuit_design_id,
KEY 'NODE_STATUS' VALUE circuit_position.CIRCUIT_NODE_STATUS, KEY "CDID3' VALUE 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 –