Skip to main content
  1. Posts/

When you get bit by precision & scale in PL/SQL…

··250 words·2 mins·
Programming Oracle

I got a bug assigned to me where my application was returning an incorrect value. Some background: The function was an age check function which takes in date of birth & start date and returns the age.

In this specific case, the date of birth was 05.01.1951 and start date was 01.01.2016 - the expected age that was to be returned(after round off to 4) was 64.9892. The application, however was returning 65.  I went through the log, the dates are proper. I went through the source, the round off is indeed to 4 places but still it was returning 65. I ran the bit of code responsible for fetching and calculating outside in couple of editors, including Toad & SQL*Plus, and both cases it was returning 64.9892 - and now I was flummoxed - where and why the heck was it rounding off?

I thought for a bit, asked my colleagues and we were at a loss. All set to ask on Stack Overflow, I suddenly had a revelation and looked at the declaration section. And indeed, it problem was there:

n_age NUMBER(8);

The variable holding the age had a precision applied. The documentation mentions:

You can also specify a precision (total number of digits) [..]. If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero..


With no scale mentioned, the number would get a ‘free’ rounding off and store an incorrect value. The small things that bite..

Sathyajith Bhat
Sathyajith Bhat
Author, AWS Container Hero and DevOps Specialist.


Fixing FRM-41336: Populate_List: invalid column type for column 2 error in Oracle Forms
··178 words·1 min
Programming Oracle
I spent the past hour and a half trying to fix this stupid error. Here’s the situation: I have a Poplist(aka dropdown list/ list item). The poplist is populated by a query defined in a Record Group. The query is on the lines of select id, name from employees The column types are correctly defined. And yet I kept running into FRM-41336: Populate_List: invalid column type for column error message. Looking at the description for the message:
Troubleshooting Oracle’s ORA-01017 errors
··221 words·2 mins
Programming Oracle
Was writing this for Stack Overflow‘s tag wiki on ORA-01017, thought I’ll post it here as well. ORA-01017 is the error code shown my Oracle when a login attempt to the database was not successful. The full text of the error code is: ORA-01017: invalid username/password; logon denied Cause: An invalid username or password was entered in an attempt to log on to Oracle. The username and password must be the same as was
Download your Spotify My Music songs as MP3
··208 words·1 min
Programming Spotify
I wanted a way to download my Spotify songs for offline listening. Ideally I’d subscribe to Spotify to make use of Offline feature, but they haven’t officially launched here so that’s out of the question. A while back, I’d used spotify_to_mp3 which made use of Grooveshark to download, but Grooveshark shut down, leaving the script defunct. This gave me an idea to build something similar. With this as the motivation, I built Spotify-dl to scratch an itch as well as to improve my Python skills.