Disable/Enable Schema Triggers in PL/SQL

Disable Schema Triggers:

BEGIN

FOR x IN (SELECT OBJECT_NAME FROM all_objects where OBJECT_TYPE IN ('TRIGGER') and owner = 'SCOTT')

LOOP

  EXECUTE IMMEDIATE 'ALTER TRIGGER ' || x.OBJECT_NAME || ' DISABLE';

END LOOP;

END;

Enable Schema Triggers:

BEGIN

FOR x IN (SELECT OBJECT_NAME FROM all_objects where OBJECT_TYPE IN ('TRIGGER') and owner = 'SCOTT')

LOOP

  EXECUTE IMMEDIATE 'ALTER TRIGGER ' || x.OBJECT_NAME || ' ENABLE';

END LOOP;

END;

SQL Query to Fetch Numeric Data from String value in Oracle

Query to fetch Numeric Data from String value in an Oracle Table:

Step 1: Create a Table “Test” as shown below:

CREATE TABLE TEST (A VARCHAR2(30));

Step 2: Insert string data value into “TEST” Table.

INSERT INTO TEST VALUES ('1DA2SDJD+4567@@##HGSD89HH0');

Step 3: Select the “TEST” Table.

SELECT * FROM TEST;

A

1DA2SDJD+4567@@##HGSD89HH0

Step 4: Run the below query to fetch “Numeric” value from String data.

select regexp_replace(A,'[^0-9]') as a FROM TEST3;

Output:

A

124567890

SQL Query to Fetch Order-wise Month from Date Column in Oracle

Query to fetch order-wise “Month” from Date column in an Oracle Table:

Step 1: Create a Table “Test” as shown below:

CREATE TABLE TEST (A DATE);

Step 2: Insert few random Date values into “TEST” Table.

INSERT INTO TEST VALUES('01-JAN-12');

INSERT INTO TEST VALUES('11-FEB-12');

INSERT INTO TEST VALUES('21-MAR-12');

INSERT INTO TEST VALUES('30-APR-12');

INSERT INTO TEST VALUES('01-MAY-12');

INSERT INTO TEST VALUES('02-JUN-12');

INSERT INTO TEST VALUES('06-JUL-12');

INSERT INTO TEST VALUES('10-AUG-12');

INSERT INTO TEST VALUES('30-SEP-12');

INSERT INTO TEST VALUES('28-OCT-12');

INSERT INTO TEST VALUES('18-NOV-12');

INSERT INTO TEST VALUES('31-DEC-12');

Step 3: Select the “TEST” Table.

SELECT * FROM TEST;

A

30-Apr-12

10-Aug-12

31-Dec-12

11-Feb-12

1-Jan-12

6-Jul-12

2-Jun-12

21-Mar-12

1-May-12

18-Nov-12

28-Oct-12

30-Sep-12

Step 4: Run the below query to fetch “MONTH” from Date Column “Order-wise”.

SELECT TO_CHAR(A,'MON') FROM TEST ORDER BY TO_CHAR(A,'MM');

Output:

TO_CHAR(A,'MON')

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

SQL Query to Fetch Even/Odd values in Oracle

To fetch Even/Odd values from a numeric column in an Oracle Table:

Step 1: Create a Table “Test” as shown below:

CREATE TABLE TEST(A NUMBER);

Step 2: Insert few random numeric values into “TEST” Table.

INSERT INTO TEST VALUES(1);

INSERT INTO TEST VALUES(15);

INSERT INTO TEST VALUES(4);

INSERT INTO TEST VALUES(26);

INSERT INTO TEST VALUES(497);

INSERT INTO TEST VALUES(590);

INSERT INTO TEST VALUES(9826);

INSERT INTO TEST VALUES(1003);

INSERT INTO TEST VALUES(333);

Step 3: Select the “TEST” Table.

SELECT * FROM TEST;

A
1
15
4
26
497
590
9826
1003
333

Step 4: Run the below query to fetch “EVEN” values from above Table column.

SELECT * FROM TEST WHERE MOD(A,2)=0;

Output:

A

4

26

590

9826

Step 5: Run the below query to fetch “ODD” values from above Table column.

SELECT * FROM TEST WHERE MOD(A,2)!=0;

Output:

A

1

15

497

1003

333