Fact about ORA-14551

You might have come across below error.

ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "xxxxx", line xx
ORA-06512: at line 1

Why?

This error raises in following scenario

You have Database function (say myFn) that has DML operation such as Update/Delete/Insert

and you are query some thing like below

Select id, myFn(id) from customer.

Here you are performing a Select operation on the myFn, which internally performs DML operation.

How do you avoid?

If you are testing this function,

simply test as below
var :rtn varchar2(10); -- assuming function retruns varchar
call myFn(id) into :rtn;
print :rtn;

− OR −

set serveroutput on
begin
dbms_output.put_line(myFn(20));
end;

− OR −

if you are not testing, but rather you want to do select operation on the function, define function as autonomous transaction.

i.e. Put    PRAGMA AUTONOMOUS_TRANSACTION; in the declaration section of the function

Read Full Post »

Remove Duplicate values from array

Oracle Database Version 10g and up allows to eliminate duplicate values in array elements using MULTISET operation.

Below is the example

Script

Set serveroutput on ;

DECLARE
   TYPE T_Sampleid IS TABLE OF VARCHAR2 (20);

   arr_sampleid    t_sampleid := T_Sampleid ();
   arr_sampleid2   t_sampleid := T_Sampleid ();
BEGIN
   --Adding 6 samples...
   arr_sampleid.EXTEND;
   arr_sampleid (1) := 'sample G001';
   arr_sampleid.EXTEND;
   arr_sampleid (2) := 'sample G002';
   arr_sampleid.EXTEND;
   arr_sampleid (3) := 'sample G003';
   arr_sampleid.EXTEND;
   arr_sampleid (4) := 'sample G001'; -- duplicate
   arr_sampleid.EXTEND;
   arr_sampleid (5) := 'sample G002'; -- duplicate.
   arr_sampleid.EXTEND;
   arr_sampleid (6) := 'sample G004';

   -- Let's display values
   DBMS_OUTPUT.put_line ('Display all samples...');

   FOR i IN arr_sampleid.FIRST .. arr_sampleid.LAST
   LOOP
      DBMS_OUTPUT.put_line (arr_sampleid (i));
   END LOOP;

   -- same array into array 2
   arr_sampleid2 := arr_sampleid;

   -- Identify distinct and return to main arr_sampleid
   arr_sampleid := arr_sampleid MULTISET UNION DISTINCT arr_sampleid2;

   DBMS_OUTPUT.put_line ('Display DISTINCT samples...');

   FOR i IN arr_sampleid.FIRST .. arr_sampleid.LAST
   LOOP
      DBMS_OUTPUT.put_line (arr_sampleid (i));
   END LOOP;
END;
/
Output
processing sample: G11-A15214-02-TA0001 1 1
Display all samples...
sample G001
sample G002
sample G003
sample G001
sample G002
sample G004
Display DISTINCT samples...
sample G001
sample G002
sample G003
sample G004

As you can see in the output, Second set don't have duplicates.

Read Full Post »

Shrink Database Object

Why Shrink Objects?

You want to shrink the Database objects in order to do claim unused disk for objects (tables and Index)

How to Shrink Objects?

In order to shrink objects, Objects needs to be enabled with row movement first (By Default they are Disabled).

Query below to see if the table is enabled for row movement

select table_name, row_movement from user_tables;

ALTER TABLE employee ENABLE ROW MOVEMENT;

Following are 3 options…

1) Recover space and amend the high water mark (HWM).
ALTER TABLE employee SHRINK SPACE;
2) Recover space, but don’t amend the high water mark (HWM).
ALTER TABLE employee SHRINK SPACE COMPACT;
3) Recover space for the object and all dependant objects.
ALTER TABLE employee SHRINK SPACE CASCADE;

Finally,
make sure to Disable the row movement (if this was the case at the beginning)
ALTER TABLE employee DISABLE ROW MOVEMENT;

Read Full Post »