Amazing world about data

Developer, ThoughtWorker, Data Architect and Data Specialist

Oracle

2 minutes read

In all new development and sometimes during legacy codebase modernization, developers tend to add code quality checks and static analysis of codebase such as style checks, bug finders, cyclomatic complexity checking etc. into the CI/CD pipeline. When we inherit a codebase that has much PL/SQL and there is a desire to put the PL/SQL code base through the same types of code analysis, what options does a developer/dba have?

Usage of mixed case database object names is dangerous

Do not used mixed case database object names

2 minutes read

Some versions back, Oracle would not allow to create database object names with mixed cases, even if we tried to create them, we could not. In newer versions of Oracle we can create tables, columns, indexes etc using mixed case or lower case, when the names are put inside double quotes. For example

1 minute read

We have been doing some data moving lately using Ruby and Ruby-OCI. We started with Ruby OCI 1.0 and did use prepared statements with bind variables (since we are using oracle database and pulling data from an oracle database and pushing data to an oracle database). Later we found this really cool feature in Ruby-OCI8 2.0 where you can bind a whole array and just make one database trip for many database operations.

Materialized views and database links in oracle.

Using materialized views as an interface oobject

2 minutes read

Recently one of my colleague Jeff Norris had a weird error. He was trying to build a materialized view over some tables in his local database and some tables in his remote database using database links the sql to create the view ran fine and provided the results as expected, but when put inside a materialized view statement complained with ORA-00942 errors.

Explicitly rollback when you encounter a deadlock.

Cleaning up database resources after a deadlock

2 minutes read

Dead lock is caused in the database when you have resources (connections) waiting for other connections to release locks on the rows that are needed by the session, resulting in all session being blocked. Oracle automatically detects deadlocks are resolves the deadlock by rolling back the statement in the transaction that detected the deadlock. Thing to remember is that last statement is rolled back and not the whole transaction, which means that if you had other modifications, those rows are…

1 minute read

Ever since I moved to the Mac, I had to run some other OS inside a VM so that I could run Oracle and use it, since Oracle was not available for the the Mac. Now that is no longer the case. Oracle 10gR2 (10.2.0.4) is now available for Mac here

2 minutes read

Oracle has metadata about all its objects in various tables/views. One such view is the USER_OBJECTS or ALL_OBJECTS, this view has a column named as STATUS which shows you if the given object is VALID or INVALID. The status applies to DB Code (Stored Procedures, Functions, Triggers etc).

2 minutes read

We came across a need to save just the Time in the database, the requirement is to store time of the day, like say the user likes to have Breakfast at 8.15AM and Lunch at 12.32PM etc. Off course oracle does not have a time only data type. So we ended up using DATE as the data type and just setting the time. for example:

Recent posts

Post

See more

Categories

About