Migrating to 10g? Check whether you have used -1401

Starting from Oracle 10g, Oracle have changed the error code 1401 to 12899. For applications migrating to Oracle 10g make sure that there is no direct reference to -1401 anywhere in code.

There can be either

PRAGMA EXCEPTION INIT(field_length_error, -1401);

or

if sqlcode = -1401 then

type of errors handled in the program.

Use the following query to (not fully) find out whether there are any references to the code or not.

select name,line from user_source where text like '%-1401%';

If there are any references, change them to -12899.

ORA-01401 has been discarded in Oracle 10g

A new error message ORA-12899 has been created instead of the dreaded ORA-01401 error message in Oracle 10g version.

What is the benefit of changing this error ?


Well while this new error message is displayed Oracle will also display the field name in which the NULL was not allowed.

"ORA-01401: Inserted value too large for column"

ORA-12899: value too large for column
"DB"."TABLE_NAME"."FIELD_NAME" (actual: 6, maximum:5)

This was a nice enhancement.

Ask Tom Article:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::
P11_QUESTION_ID:7143933880166

Null handling with Group functions

Group functions ignore NULL values by default. To force Oracle group functions not to ignore NULL values use nvl() function.



For example:

select avg(sal) from emp;

AVG(SAL)
----------------
29333.3333333333

select avg(nvl(sal,0)) from emp;

AVG(NLV(SAL,0))
--------------------
17600

In this example some of the employees where updated with salary of NULL.

Now try another example to understand the GROUP BY clause also ignores the NULL.

select sal, sum(sal) from emp group by sal;

SAL SUM(SAL)
------------------
25000 25000
27000 27000
36000 36000

Four rows selected.

Even though there where two NULL in the sal field, only one row was yielded by GROUP BY clause.

Inserting duplicate records in Primary key field

I had a mail from my friend describing the way to inserting duplicate value in a primary key field. I have reproduced it below:

First let us create a table:

create table test100
(
empno number(9) primary key,
ename varchar2(50)
);

Insert some valid records into the table:

insert into test100 values(1,'Sachin');

insert into test100 values(2,'Saurav');

commit;

EMPNO ENAME
1 Sachin
2 Saurav

Find out the constraint name for the primary key:
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE 'TEST100' ;

CONSTRAINT_NAME
SYS_C00249950

Disable the constraint:

ALTER TABLE TEST100 DISABLE CONSTRAINT SYS_C00249950;

Check whether the index for the field is enabled or not:
select index_name,index_type from user_indexes where table_name like 'TEST100';

It should return no rows returned. If otherwise returned drop the index.

Now insert the duplicate record:

INSERT INTO TEST100 VALUES (1,'Ganguly');

Now create a non-unique index on the field:
CREATE INDEX TEST100_INDEX ON TEST100(EMPNO);

And at final enable the primary key constraint:
ALTER TABLE TEST100 ENABLE NOVALIDATE CONSTRAINT SYS_C00249950;

Now give a select * from test100;

EMPNO ENAME
1 Sachin
2 Saurav
1 Ganguly

Now you have a enabled primary key constraint with a violated data:
SELECT CONSTRAINT_NAME,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST100';

CONSTRAINT_NAME STATUS
SYS_C00249950 ENABLED

Can anybody give suggestions as to why Oracle has given the NOVALIDATE clause while enabling the constraint?

Index Organized Tables - Introduction

Index Organized Tables or IOT where introduced in Oracle with the arrival of Oracle 8. These tables where introduced primarily for Internet applications that involve data access based on single column primary keys.

Storage organization has been always a key factor in faster access of data. With normal tables there are no indexes created at first. First you have to create a table, then create indexes for faster performance in data access.

Indexes have some drawbacks such as it stores data in two places, one in table and in index. If a query is issued (it is assumed that it uses the index), it checks the index and retrieves the address of data in table. Then the data is fetched from the tables to produce the query output.

But in case of an Index Organized Table, data is stored in the index itself with the rows placed in a key-sequenced order, thus eliminating the necessity of duplicate data being stored in index and table.

An Index Organized Table is created using the keyword ORGANIZATION INDEX at the end of the CREATE TABLE script.

Refer the following example for creation of a IOT:
CREATE TABLE temp1 (
slno NUMBER (3) NOT NULL,
CONSTRAINT PK_temp1
PRIMARY KEY ( slno))
ORGANIZATION INDEX;

Now we will check in what order the data is actually stored by inserting some rows into the table temp1.

insert into temp1 values(6);

insert into temp1 values(1);

insert into temp1 values(5);

insert into temp1 values(4);

insert into temp1 values(3);

insert into temp1 values(2);

insert into temp1 values(9);

insert into temp1 values(7);

insert into temp1 values(8);

insert into temp1 values(10);

Now after inserting these rows by issuing a select statement without any order clause will retrieve the rows in stored order. Let's check this by issuing the following statement:

select * from temp1;

The output is:
SLNO
1
2
3
4
5
6
7
8
9
10

Now from this exercise we are clear as to how Oracle stores the data in an Index Organized Table for a normal table the rows would have been selected in the inserted order.

What is IOT?
So what is an IOT? An IOT has entirely different logic of storage and indexing. In normal tables as soon as we create a row it is associated with a ROWID. This ROWID is permenant as long as the data is there. When an index is created, it stores the column data as well as the ROWID of the table data as it provides its physical location.

IOTs do not consider ROWID. This is because the data is actually stored in a B-Tree index that sorts the data with the leaves in the order of the primary key's data. As and when INSERTs or UPDATEs are fired against this IOT, the rows are re-arranged to store the data in sorted order of the primary key.

The access to the data is fast because as soon as the values are found in the B-Tree index, Oracle is ready to pump the output directly as it is not tied up with ROWIDs. Hence there are two benefits of using IOT:
1. Lookup to table from index is eliminated
2. Storage requirements are reduced as data is stored only in one place.

Courtesy: Oracle9i Index-Organized Tables Technical Whitepaper - Oracle Corporation

ORA-02429: Cannot drop index used for enforcement of unique/primary key

To simulate the error do the following:

create table temp_index as select object_name from user_objects where object_type='INDEX';

create table testing(testid number primary key, testchar varchar2(200));

Find out the index created for the primary key by the following statement:

select object_name from user_objects where object_type='INDEX'
minus
select object_name from temp_index;

Drop the index by issuing the following statement:
drop index SYS_C00249876;

You will get the error ORA-02429: Cannot drop index used for enforcement of unique/primary key

In Index organized tables this problem will be more. You will not be able to drop either the primary key or index.

Consider the example:
CREATE TABLE temp1 (
testid NUMBER (3) NOT NULL,
CONSTRAINT pk_temp1
PRIMARY KEY ( testid))
ORGANIZATION INDEX ;

alter table temp1 drop constraint pk_temp1;

You will get the following error:
ORA-25188: Cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster

drop index pk_temp1;
You will get the following error:
ORA-02429: Cannot drop index used for enforcement of unique/primary key

Creating views that enforce constraints

Tables that underlie views often have constraints that limit the data that can be added to those tables. Views cannot add data to the underlying table that would violate the table's constraints. However, you will be able to define a view to restrict the user's ability to change underlying table data even further, effectively placing a special constraint for data manipulation through the view.

This additional constraint says that insert or update statements issued against the view cannot create rows that the view cannot subsequently select. In other words, if after the change is made, the view will not be able to select the row that has been changed, the view will not allow to make the changes. This view ability constraint is configured when the view is defined by adding the with check option to the create view statement. Let's look at an example to clarify the point:

create or replace view emp_view as
(select empno, ename, job, deptno
from emp
where deptno = 10)
with check option constraint emp_view_constraint;

update emp_view set deptno = 20
where ename = 'KING';

Error comes as:
ORA-01402: view WITH CHECK OPTION where-clause violation

On some systems, you may not get the ORA-01402 error in this context. Instead, Oracle may simply state that zero rows were updated by the statement issued.

You will be also able to use constraints that use > or % operators.
create or replace view emp_view as
(select empno, ename, job, deptno
from emp
where deptno > 1
and deptname like 'A%')

with check option constraint emp_view_constraint;

By the above statement we should mean that all deptno greater than 1 and deptname starting with A are not to be updated.

So the fields used while creating the view cannot be modified. Also you can omit the constraint name from the clause to get the same effect:

create or replace view emp_view as
(select empno, ename, job, deptno
from emp
where deptno = 10)
with check option;

You will be also able to create a view that is read-only by using the following syntax:
create or replace view emp_view as
(select * from emp)
with read only;

Courtesy: OCP Introduction to Oracle 9i: SQL Exam Guide book by Oracle Press
ISBN: 0-07-047420-6

Parameters to Form Runtime

The following are the valid parameters to ifrun60.exe (Forms 6i):

Module - Form Runtime module name
Userid - Login credentials
Term - Oracle Terminal resource file
Debug - Invoke the PL/SQL Debugger
Debug_Messages - Display debugging messages
Keyout - Write Input keystrokes to file
Keyin - Read Input keystrokes from file
Output_File - Write display to file
Interactive - Write display to terminal
Array - Use array SQL Processing
Buffer_Records - Buffer records to temporary file
Logon_screen - Displays the screen to specify logon name
Block_Menu - Display block menu on startup
OptimizeSQL - Optimize V2-Style Trigger-Step SQL Processing
Quiet - Quiet Mode
Statistics - Show statistics
Query_Only - Show forms in query only mode
Help - Show the help screen related to parameters
Options_Screen - Displays options window (Only bitmap mode)
Pecs - Collect PECS data
Window_state - Root window state(Normal, Maximize, Minimize)

Parameters to Form Builder

The following are the valid parameters to ifbld90.exe (Form Builder 9i):

Module Module Name
Userid Database login credentials
Module_type FORM/LIBRARY/MENU
Term Oracle Terminal Resource Mapping File
Help To show all valid parameters to ifbld90

The following are the valid parameters to ifbld60.exe (Form Builder 6i):
Module Module Name
Userid
Database login credentials
Module_type
FORM/LIBRARY/MENU/PECS
Module_Access FILE/DATABASE
Term Oracle Terminal Resource Mapping File
Help To show all valid parameters to ifbld60

What is difference between UNIQUE and PRIMARY KEY constraints

Both UNIQUE and PRIMARY KEY constraints enforce uniqueness in the column. But there are two major differences:

Primary Key
------------
1. It does not allow NULL value to be inserted in the column.
2. Oracle creates a clustered index by default for the column.

Unique Key
-----------
1. It allows one NULL value to be inserted in the column.
2. Oracle creates a non-clustered index by default in the column.


Moreover a table can have more than one UNIQUE key but not more than one Primary key.

How to reset a sequence value?

What is the easiest way to set a sequence to a very high value.

E.g. currval = 100, set it to point to 1100.

Instead of dropping and recreating the sequence again, use the INCREMENT BY option to reset a sequence value. You can also decrement the sequence value by incrementing it with a negative number. Don't forget to reset the increment value to 1 or as the case.

Alter sequence x increment by 1000;

select x.nextval from dual;

Alter sequence x increment by 1;

Tip taken from:
http://www.amar-padhi.com/oradb_seq_reset.html

Setting SYSDATE to Fixed date and time

Login as SYS in SQL *Plus.

Issue the following command.

alter system set fixed_date='dd.mon.yyyy hh24:mi:ss';

This will alter the sysdate to always the date set.

To reset this to normal issue the following command.

alter system set fixed_date=NONE;

How to retrieve only unlocked rows?

How to retrieve only those records from a table that are not locked by other users? One way of doing it is by writing a PL/SQL code based on locking error (-54). Is there any other way of viewing unlocked records in SQL only?

Example:

Sql*plus session 1:
A user locks records with update emp set sal = sal*1.2
where deptno = 40;

Sql*plus session 2:
Another user likes to view all unlocked records from
table emp and is not aware of what is locked.
What should he/she do?

SKIP LOCKED option of select for update will list unlocked records. This is an undocumented feature.
select empno, ename, job, sal
from emp
for update skip locked;

Tip taken from :
http://www.amar-padhi.com/oradb_retrieve_unlocked_rows.html

How to rename an Index?

The normal way of renaming an index is by the following two commands:

drop index id1;

create index id2 on table_name(column_name, column_name1);

But starting from Oracle 8i, you will be able to rename an index by issuing the following command:

alter index id1 rename to id2;

Setting SYSDATE to Fixed date and time

Login as SYS in SQL *Plus.

Issue the following command.

alter system set fixed_date='dd.mon.yyyy hh24:mi:ss';

This will alter the sysdate to always the date set.

To reset this to normal issue the following command.

alter system set fixed_date=NONE;