Database articles

Search for a Schema

So now that we have a backend, we need to store something in our backend. For that we need to have a schema for our domain. What is our domain ? Excellent question, not sure yet. But a customer and perhaps products he orders seem to be a good enough choice to begin with. I am currently working for a telecommunication company, so am seeing a lot of customers and orders. Thereby this should be no surprise. But aside this point, pretty much every webapp has customers and orders in some form. So we should have some good learning opputunity here.

The Database Backend

The first thing that any webapp needs is a persistance storage. Generally speaking Oracle databases are generally a very safe choice. But for small and medium based businesses this can result in prohibitive costs. Mysql/MariaDB is a popular choice here and is running most of the web. But at this point I am deciding against the popular choice and going for Postgre. The main thinking and reason being that Postgre is considered very close to an Oracle DB. Hence later if I wish moving my schema over to an Oracle DB is generally considered do-able.

ORA-24247 ACL error in Oracle 12c

So the debugger was not connecting to debug a PLSQL procedure one day. I knew I had debugged this on Oracle 11g, so it had to work as well on 12c. But the debugger was not connecting and complaining something like below:

 Connecting to the database MYDB_12c.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP('X.X.X.X', '57908')
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database MYDB_12c.

 

Oracle SQL : Matching Prefix and Suffix string but only accepting whats in between

regex replace

A while ago I wrote an article about regex lookahead and lookbehind here. I needed the same thing now in SQL, specifically Oracle flavor. Turns out that Oracle supports the POSIX BRE syntax and POSIX ERE syntax plus PERL influenced extensions, but no look ahead look behind equivalent regex syntax.

So the problem we have to solve is the same. I have a String that is ";" seperated key value pairs that might look like below:

Generating random days in past or future for demo data generation via plsql / sql

So while generating demo data for one of my projects, I had to generate some days in the future and in the past. Thought I might blog about that for my future benefit as well was any one else who visits my blog. So here it goes.

Generating a date in the past (in days granularity in the range of a year)

The below statement will get you a day in the past year. Basically you generate a random number in the range of 0-365 and subtract that from the sysdate. Convert it to a timestamp and save to your timestamp field or use without the cast as a date field.

Introducing Apache Flume

So if you have been following my Big Data series till now, in my first article I introduced to a ready made VM image courtesy of Oracle that frees you up from the head-aches of setting things up and concentrate your time on real learning. Next article I took you on a ride with Hadoop and the primary learning point of the article HDFS (the Hadoop File system). Now we will talk about a next often used component of Big Data, Apache Flume.

How to get started with Oracle Big Data

Big Data

Well not everyone can afford one of Oracle's Big Data Machines, but what to do if you want to get on the Big Data band-wagon. Well for educational purposes Oracle releases a Virtual Machine image called Oracle Big Data Lite.

An enhanced Ref Cursor based VO with Cached RowSet

A while back I was doing some prototypes where we were looking to evaluate if we could use pl-sql to query our data in our VOs. I already knew that if was possible as I had glanced over the examples but never had taken a deeper look. Now was an excellent time to hack at it.

When Select count(*) is not the best option

A colleague of mine brought this fact to attention that count(*) is not a very good thing. Atleast not for the case when you are checking for the presence of just a single record. What popped to my mind immediately was, not if there is proper index, it shouldn't matter. But that was an assumption on my part which I had to check out. And the result turned out to be that rewriting such a query is always a good idea if all you want is an existence check. So lets do some cost analysis in this article for the same, so you can also understand the same.