Today I came across this thread at Oracle Forum.
Where Question was as follows:
Consider Test table have Numb (Number datatype) field. table values are given below.
Table Name: Test
NUMB
1
2
4
7
8
9
12 … This table have more than thousand records like that…..
How to retrive the missing numbers. I’m expecting the output 3,5,6,10,11……..
Could you please give the SQL for this task. Thanks!
One of the member David Grimberg gave following solution.

select level numb from dual connect by level <= (select max(numb) from test)
minus
select numb from test;
I was very impress with this sql because I recognize the worth of this sql to myself and many situation in which […]

[Read more →]

Today when doing some research on “FAST DUAL”, I came across following error when I try to set autotrace on:
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>
Anyway I ignored the error and ran my sql.
The result was I could see the execution plan but not statistics as follows.
SQL> select sysdate from dual;
SYSDATE
———
17-DEC-07
Execution Plan
———————————————————-
Plan hash value: 1388734953
—————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————–
| 0 | SELECT STATEMENT | […]

[Read more →]

On 13th October 2007, I wrote about “Understanding Virtual Columns“.
Then Ray DeBruyn ask me a very good question, which I totally miss to ask myself when I was writing about it.
What happens if I use:
INSERT INTO myTable VALUES myTableRec
or
UPDATE myTable SET ROW = myTableRec
Assuming I declare myTableRec as myTable%ROWTYPE, is the new feature smart enough to ignore the virtual column?
To find out the answer, I did following:

SQL> create table virtual_col(
2 a number,
3 b number,
4 c number GENERATED ALWAYS AS (a+b) VIRTUAL,
5 d number);
Table created.
SQL> desc virtual_col;
Name […]

[Read more →]

On 12th july 2007, I wrote a post about new feature of 11g related to sequence.
In this post I mention that in 11g we don’t need to use dual table to fetch next value of a sequence and using simple PL/SQL expression the next value can be fetched.
Any way thats the old story and lot of people have wrote about it.
But recently I got to know from Asif blog, what exactly is happening in background.
I follows what he did and confirms that he 100% right!

Created the new sequence
SQL> create sequence new11g_seq;
Sequence created.

Give a name to the trace file that will […]

[Read more →]

I don’t why but I never came across this function, may be because I am so used to using MOD function to get the remainder of two number when one of them is divided by others, so never though of finding any other function doing same thing.
But yesterday I came across this function while surfing on the net and thought may be many of us may be missing it too.
So sharing for those person who have missed it like me…..
REMAINDER(n2,n1)
REMAINDER returns the remainder of n2 divided by n1 . This function takes as arguments any numeric datatype or any nonnumeric […]

[Read more →]

Scenario From Oracle Forum
I have a transaction table consists of duplicate records I success to write a sql
which get the duplicate records but unable to write the delete statement to delete
this record.
The sql query is as follows:
Select invoice_no, slip_no, drug_code
from transaction_detail
having count(*)>1
group by invoice_no, slip_no, drug_code
If anyone knows please let me know the delete statement. Thanks in advance.
Regards
This is what I think
DELETE transaction_detail td1
WHERE EXISTS ( select ‘x’ FROM
(Select MIN(invoice_no) invoice_no, MIN(slip_no) slip_no, MIN(drug_code) drug_code, MIN(rowid) t_rowid
from transaction_detail
having count(*)>1
group by invoice_no, slip_no, drug_code) td2
WHERE td1.invoice_no = td2.invoice_no
AND td1.slip_no = td2.slip_no
AND td1.drug_code = td2.drug_code
AND td1.rowid td2.t_rowid);
Check out following url for […]

[Read more →]

Check out following:
SQL> select * from test;
COL1
———-
1
1
1
1
1
SQL> alter table test add constraint test_idx primary key(col1) disable;
Table altered.
SQL> create index test_idx on test(col1);
Index created.
SQL> alter table test enable novalidate constraint test_idx;
Table altered.
SQL> insert into test values(1);
insert into test values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TEST_IDX) violated

Bookmark […]

[Read more →]

When working with virtual columns recently I came across the following error:
ORA-30553: The function is not deterministic
Cause: The function on which the index is defined is not deterministic
Action: If the function is deterministic, mark it DETERMINISTIC. If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. The values returned by a deterministic function should not change even when the function is rewritten or recompiled.
So I though of writing about DETERMINISTIC Functions as I find it very important piece of information when working with […]

[Read more →]

Just trying to brush up few basic concept!
What happens when Oracle processes an SQL Statement?

Step 1: Oracle Create a Cursor
For every SQL, first a cursor is created (cursor creation can occur implicitly, or explicitly by declaring a cursor).
Step 2: Oracle Parse SQL Statement
A SQL statement is parsed only if an identical SQL statement does not exist in the library cache (shared pool-System Global Area). In this case, a new shared SQL area is allocated and the statement is parsed (hard parse), otherwise existing stored information in library cache is used (soft parse).
Hard Parsing is the process of

  Translating a SQL […]

[Read more →]

Scenario From Oracle Forum
This function seems like a good idea for password validation, for example one number and 4-8 characters.
However, this does not work - any ideas?
select
1 from dual where
regexp_like(upper(’PA55WORD’),’^(?=.*\d).{4,8}$’)
Solution which I gave:
WITH data_Set AS
(
SELECT 1 row_no, ‘3434HERE’ text_col FROM dual
UNION ALL
SELECT 2 row_no, ‘EYRERY787′ text_col FROM dual
UNION ALL
SELECT 3 row_no, ‘DHFD67SDSD’ text_col FROM dual
UNION ALL
SELECT 4 row_no, ‘ERERT’ text_col FROM dual
UNION ALL
SELECT 4 row_no, ‘23232′ text_col FROM dual
)
SELECT row_no FROM data_set
WHERE REGEXP_LIKE(UPPER(text_col),’([[:alpha:]]+[[:digit:]]+[[:alpha:]]+)|([[:digit:]]+[[:alpha:]]+)|([[:alpha:]]+[[:digit:]]+)’)
/
Result as follows:
ROW_NO
———-
1
2
3

Bookmark this post

[…]

[Read more →]