Mostly data; on Oracle, Hadoop, Spark, and whatever I find interesting.

I needed a place where I can publish solutions to problems I’ve met in different areas without worrying about much else.

Import Large Excel Files with SQL Developer

When Excel is the only tool they have

If you try to import large Excel files to SQL Developer, it may hang for a long time. But let say you like SQL Developer very much, or you are too lazy to find another tool, here is a way to load large Excel files. By converting the Excel files to CSV SQL Developer can load it much faster. The tool requires Python, and this week I’m into creating virtual environments, so I will do here. [Read More]

Converting Epoch Time to Date with a Virtual Column

Epoch time is common in some situations because it is easier to do date calculations than with normal dates. The most popular is UNIX Epoch time which is the number of seconds since the start of January 1, 1970. If you have your data in Oracle you don’t have to worry about date arithmetic, since Oracle handles that for you. So in case you have such a table with epoch time and want to query it with functions that expect the DATE datatype, you can add a virtual column that returns the epoch time converted to DATE like this: [Read More]

Oracle 12c and SSL

How to waste a Saturday afternoon

I was supposed to do something else that involved using the package UTL_HTTP from the database accessing an URL over HTTPS. Instead I lost several hours troubleshooting ORA-29024: Certificate validation failure and ORA-28750: unknown error (Nice error the last one). This is not the first time I’ve been through this. It is getting quite common that sites insist on using HTTPS, even if you access them using HTTP, the client will typically be redirected to HTTPS (port 443). [Read More]

ODC Appreciation Day: SQL

#ThanksODC

Oracle Developer Community Most of my work in the Oracle world has been DBA-oriented. But for the last years, I have rediscovered the joy of development and data analysis. When I haven’t been writing the code myself, I have often helped other developers when they connect to the Oracle database, and their program does not perform as expected. Developers coding in Java, Python, or most other programming languages spend time on figuring out how to get the work done. [Read More]

ORA-01861

I’m writing this post so it can be found by googlers struggling with ORA-01861. Usually, ORA-01861 means that you missed something when using a date conversion function. But, here the other day I had created a table using some code generation tool, and I had accidentally declared a column with datatype DATE instead of NUMBER. When you are using the wrong datatype in SQL, Oracle tries to convert it for you. [Read More]

Docker for Mac - Increase Swap

Building Docker image with Oracle XE

In an attempt to compare how long it will take to build a Docker image of Oracle XE (11.2) vs EE (12.2) on my Mac I ran into a problem. The building of XE failed, it complained that only 1023 MB swap space was available. I thought that adding swap during build with this workaround would do, but it turns out that with Docker things are different. I found suggestions here, but since I was not reading carefully enough, I missed the point that you add swap to the hypervisor Docker for Mac is running on. [Read More]

Slow SQL From Monitoring Software

Queries from monitoring software like BMC are not supposed to be among Top SQL, but that was happening in many databases at one site. This may happen if the dictionary statistics are not updated, leading Oracle to choose the wrong plan. Document with ID 1328789.1 on MOS shows an example of this. So the SQL that came from BMC sometimes took up to a minute and was executed every 5 minutes, easily making it to the Top SQL list in Enterprise Manager / Cloud Control. [Read More]

Flashback Tables

Help your developers to run more tests

It is probably the developers’ responsibility to run tests, but they may not be aware of features in the database that makes the testing easier. Here the other day an ETL developer asked for help with resetting tables after ETL-testing. But there was no need to create complicated scripts to do so. I showed him flashback tables so he could easily fix it himself and run the tests over and over again without any pain. [Read More]