Database

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.

Introducing Apache Hadoop

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.

Subscribe to RSS - Database