Connecting the dots

While doing research for the next release of Hermes, Premiertec’s flagship project management tool, I came across a task where I had to parse a string like

apps.xxpt_chain_controller_pkg.execute

into procedure name, package name and optional schema name. To achieve this I had to count the number of dots in the string.

Being naturally lazy, I decided to give Internet a spin and find a ready answer. To my dismay, all the ready-to-go answers were PL/SQL procedures of questionable quality. The ingenuity of developers did not go beyond reading the string symbol by symbol and, literally, counting dots.

If you are interviewing a PL/SQL developer, please, use this example as an interview question with the proper answer as

select length(p_string) – length(replace(p_string, ‘.’)) from dual