Oracle Intellect: 2009

November 24, 2009

Table Mutating Trigger

When a Trigger is created on a table and the trigger code is performing the read operation on the same table, then the mutating happens.

CREATE TRIGGER sample_trigger
 AFTER INSERT
    ON sample_table
   FOR EACH ROW
local_variable NUMBER;
BEGIN
  IF INSERTING
  THEN
    SELECT sample_column
      INTO local_variable
      FROM sample_table;
  .
  .
  .

  END IF;
END sample_trigger;

To resolve this, we need to store the required values in global variables when row level trigger is fired and create a statement level trigger which uses this global variable.

CREATE PACKAGE sample_package AS
  global_variable NUMBER;
END sample_package;

--row level trigger

CREATE TRIGGER sample_trigger_1
 AFTER INSERT
    ON sample_table
   FOR EACH ROW
BEGIN
  IF INSERTING
  THEN
    global_variable := :NEW.sample_column;
  END IF;
END sample_trigger_1;

--statement level trigger

CREATE TRIGGER sample_trigger_2
 AFTER INSERT
    ON sample_table
BEGIN
  IF INSERTING
  THEN
    local_variable := global_variable;
  .
  .
  .

  END IF;
END sample_trigger_2;

Disadvantage: It will be useful only when COMMIT happens for each and every row separately. It will not be useful when COMMIT happens after multiple updates. The second trigger fires only after COMMIT and the required operation happens only for the last row.

November 10, 2009

NUMBER Datatype - Scale greater than Precision

Number(p,s) is one of the numeric datatypes available in Oracle.

Where p is the precision and s is the scale.

For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal -> 12345.67

You can specify a scale that is greater than precision, although it is uncommon.

In this case,

Only digit before the decimal point is 0 (zero)
Scale s specifies the maximum number of digits to the right of the decimal point
Scale-Precision s-p specifies the minimum number of zeros present after the decimal point

For example, number(2,7) can store 0.0000012.

Maximum number of digits to the right of the decimal point is 7

Minimum number of zeros present after the decimal point is 7-2=5

November 03, 2009

Reset Oracle Sequence Value

Many of the Oracle developers are searching the internet for resetting the sequence value.

There is no such attribute to alter the sequence value directly in Oracle.

But a few simple steps will solve the problem.

Let me take an example and explain the steps.

I have a sequence, emp_id_seq, and its current value is 100.

I need to reset the sequence value to 150.

SELECT emp_id_seq.currval FROM DUAL;

O/P: 100

In Oracle, we can alter the sequence increment by value.

With the help of it, alter the increment value such that the next increment should reach your expected value. In our example, I am incrementing by 50 to reach 150 from 100.

ALTER SEQUENCE emp_id_seq INCREMENT BY 50;

Do select the sequence next value to reach the expected value.

SELECT emp_id_seq.nextval FROM DUAL;

O/P: 150

Set back your increment value. If your increment value before is 1, set it back to 1.

ALTER SEQUENCE emp_id_seq INCREMENT BY 1;

Thats it!!!

Its very simple.

From here, your sequence value increments by 1 as usual.

SELECT emp_id_seq.nextval FROM DUAL;

O/P: 151

Note: You can also give negative numbers in the Increment By option. For example, if you need to reset the sequence value from 150 to 100, then give -50 so that it will be decremented by 50.

November 02, 2009

Handling Parameter List

I got a question on handling parameter list (String with Comma Separated values from Java), nothing but table type parameter in Oracle.

Here is the question:

Hi,

How can I create an Oracle stored procedure which accepts a variable number of parameter values used to feed a IN clause?

This is what I am trying to achieve.

FUNCTION

EXECUTE_UPDATE ( value IN int)

RETURN int ISBEGIN [...other statements...]

Select * from table1 where id in ()

RETURN SQL%ROWCOUNT ;END;

We get the parameter_list from java
e.g., parameter_list = "’A’,’B’,’C’,’D’";

We need a solution which does not uses any temporary table for achieving this.

And here is my suggestion:

Hi,

Check the below piece of code. It may help you.

Table:

CREATE TABLE available_products (product VARCHAR2 (100));

Type:

CREATE OR REPLACE TYPE products_list AS TABLE OF VARCHAR2 (100);

Data:

INSERT INTO available_products VALUES ('A');

INSERT INTO available_products VALUES ('B');

INSERT INTO available_products VALUES ('C');

INSERT INTO available_products VALUES ('D');

Function:

CREATE OR REPLACE FUNCTION check_availability (products IN products_list)

RETURN NUMBER AS

  l_count NUMBER;

BEGIN

  SELECT COUNT (1)
    INTO l_count
    FROM available_products
   WHERE product IN (SELECT COLUMN_VALUE FROM TABLE (products));

  RETURN l_count;

END check_availability;

PL/SQL Block:

BEGIN

  DBMS_OUTPUT.put_line (
    'count:' || check_availability (products_list ('B', 'C'))
  );

END;

Output:

count:2

August 18, 2009

Find and Delete Duplicate Rows in a Table

To find duplicate rows:

SELECT column1, column2
  FROM table_name
 GROUP BY column1, column2
HAVING COUNT(*) > 1;

To delete duplicate rows:

DELETE FROM table_name a
 WHERE ROWID > (SELECT MIN(ROWID)
                  FROM table_name b
                 WHERE a.column1 = b.column1
                   AND a.column2 = b.column2
               );

String Tokenizer

Query to tokenize comma seperated string:


SELECT TRIM(SUBSTR( txt
                   ,INSTR(txt, ',', 1, level ) + 1
                   ,INSTR(txt, ',', 1, level+1) - INSTR(txt, ',', 1, level) - 1
                  )
           ) AS token
  FROM (SELECT ',' 'Comma,Seperated,String' ',' AS txt FROM DUAL )
CONNECT BY level <= LENGTH(txt) - LENGTH(REPLACE(txt,',','')) - 1;

Output:

TOKEN
------
Comma
Seperated
String