July 2014

Find size of LOB securefiles

In 11g Oracle introduced SECUREFILES as the recommended way to store LOBs. I was going to post some scripts I use for getting the size allocated and size used for SecureFile LOBs, but found this link to an Oracle article covers everything nicely. Since Oracle links constantly suffer from link rot, here’s the relevant code […]

Read more →

Handling infinite loops in CONNECT BY

When using the CONNECT BY, if the data is in a loop you’ll get ORA-01436: CONNECT BY loop in user data. To find out where the loop occurs, you can use the CONNECT_BY_ISCYCLE pseudocolumn to find out which row is the problem. The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which […]

Read more →

Escaping double quote marks in Oracle

When quoting strings, one of the annoying tasks is switching the single quotes to double quotes: To work with the string “It’s the dog’s toy” requires turning the single quotes into double: ‘It”s the dog”s toy’. In 10g Oracle introduced Q-Quote delimiters: For literals where doubling the quotes is inconvenient or hard to read, you […]

Read more →