Monthly Archives: November 2014

SQL worked fine on pre 12c but failed with “ORA-01843: not a valid month” after upgrade.

Recently on OTN, OP posted below scenario. When he ran below SQL, it failed with “ORA-01843” in oracle 12c version. select FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent from fiscal_system_periods where calendrical_equivalent > 0 and to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ‘, ‘ || 2014,’Month, YYYY’) >= to_date(‘November, 2014’,’Month, … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , | Leave a comment

How to replace multiple replace functions?

Recently in OTN, some one posted below question. using 11.2.0.4 EE. How can I replace the following: “replace(replace(replace(config_item,’COLLATERAL’,’COLLAT’), ‘AGREEMENT’,’AGREE’),’VALUE’,’VAL’);” with a single regular expression or something simpler ? The reason is I need something simpler since more strings will be … Continue reading

Rate this:

Posted in Helper SQL, Oracle Internal | Tagged , , , , , | Leave a comment

INDEX Access Path and NULL Data in Oracle.

When started my learning with oracle, had lots of confusion with Btree index and NULL data correlation. Someone says index does not store NULL.. But says composite index store NULL.. Someone says when you are fetching only indexed column, it … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , , | 1 Comment

NOT IN Condition and NULL Data

Before getting into discussions, lets start with a SQL example. Trying to fetch all employees who are not manager! Employee table Structure Name Null Type ——– ——– ———— EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , , | 2 Comments

Add Not NULL constraint on existing table column having null values

Recently in OTN someone posted a question with scenario as below. “Add Not NULL constraint in the existing table that has null values” At first it seems to be simple, Just an AlTER Command should do. But think what would … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , , , , , , , | Leave a comment

Rownum condition and Explain plan

Recently posted an observation on OTN pertaining to Rownum and Explain plan for > and < where condition. Though functionality scenarios were incorrect, but as was keen to understand oracle internals wanted to know expert feedback\comments on same. Below are … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , , , | Leave a comment

Putting special character in middle of string.

Recently in OTN someone posted below SQL requirement. “In one of my column having value of string like ‘12345678’. Now i want not all of these value would be visible. So i would put * on some characters. After updating … Continue reading

Rate this:

Posted in Helper SQL | Tagged , , , | Leave a comment

Get Difference between two date, as Year Month Day Correlated.

Recently in a forum someone posted below problem statement. How to calculate years with months and day between two dates. Eg. 17-6-2013 – sysdate Expected Output: 1 Year 5 Month 0 Day Note: Sysdate: = 17 Nov 2014. So Year, … Continue reading

Rate this:

Posted in Helper SQL | Tagged , , | Leave a comment

Does Distinct operation in oracle return sort data?

When started my learning with Oracle on Pre 10.2 version, I personally use to rely on distinct to do ascending sorting.At that point was not aware of internal concepts, implicit sorting and distinct correlate as per an underscore/internal parameter (post … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , , , , , | 3 Comments

Explain plan Command : Why we should not rely for exact runtime plan.

For getting SQL plan we have lot of method in oracle, one of them being EXPLAIN PLAN FOR Command.We use EXPLAIN PLAN Command to get estimated Plan without running the SQL. Oracle perform parsing on SQL and store estimated plan … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , , , | 5 Comments