Tag Archives: example

Dynamic SQL in Oracle

Dynamic SQL in Oracle

Dynamic SQL is used to execute the things at run time. It is used for executing DDL Statement in the PL/SQL Blocks becauze you can not execute DDL directly in PL/SQL Blocks.

Following are the examples for using the Dynamic SQL
Following is the table used in the examples
Name: TEST
DATA:
SQL> select * from TEST;
NAME ID
—— ——–
RAM 1
SHAM 2

SELECT in Dynamic SQL
Pass the ID column and table name TEST as parameter in which dynamic query return the NAME column value as return output..

set serveroutput on
Declare
p_id varchar2(100) := '1';
p_table_name varchar2(100) := 'TEST';
VALUE VARCHAR2(100);
BEGIN
EXECUTE IMMEDIATE 'select name from ' || p_table_name ||' where id = :P' INTO VALUE USING p_id;
DBMS_OUTPUT.put_line(value);
END;
/

OUTPUT:
------------
RAM

INSERT in Dynamic SQL

DECLARE
query_text VARCHAR2(1000) := 'insert into test(id, NAME) values (:P_ID, :P_VAL)';
id NUMBER := 3;
VALUE VARCHAR2(100) := 'RAMESH';
BEGIN
EXECUTE IMMEDIATE query_text USING id, VALUE;
END;
/

OUTPUT:
SQL> select * from TEST;
NAME ID
------- -------
RAMESH 3
RAM 1
SHAM 2

Update values in dynamic SQL

DECLARE
query_text VARCHAR2(1000) := 'update test set name = :P_VAL where id = :P_ID';
id NUMBER := 3;
VALUE VARCHAR2(100) := 'RAKESH';
BEGIN
EXECUTE IMMEDIATE query_text USING VALUE,ID;
END;
/

OUTPUT:
SQL> select * from test;
NAME ID
---------- ----------
RAKESH 3
RAM 1
SHAM 2

NOTE: It means you should correct the sequence of input as query written inside the dynamic query clauses. otherwise following error occurred:
Error:
EXECUTE IMMEDIATE query_text USING id, VALUE;
— Error occurred due to update query use varchar2 first in sequence then number
DECLARE
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 6
Solution:
Correct the sequence of parameter passing in dynamic SQL.

Delete value in Dynmaic SQL

DECLARE
query_text VARCHAR2(1000) := 'delete from test where id = :P_ID';
id NUMBER := 3;
BEGIN
EXECUTE IMMEDIATE query_text USING ID;
END;
/

OUTPUT:
SQL> select * from test;
NAME ID
---------- ----------
RAM 1
SHAM 2

DDL Statements execute with Dynamic SQL

BEGIN
EXECUTE IMMEDIATE 'create table TEST_BKP (id number, column_value varchar2(100))';
END;
/

OUTPUT:
SQL> desc test_bkp;
Name Null? Type
-------------- -------- ---------------
ID NUMBER
COLUMN_VALUE VARCHAR2(100)

Advertisements

Triggers in Oracle

Triggers in Oracle

Triggers are special kind of procedure which is fired automatically don’t need to call by user explicitly. It automatically fired on some event happen insert update delete operation on which it built or code.

Syntax:
CREATE [OR REPLACE] TRIGGER schema.trigger-name
{BEFORE | AFTER} dmlevent ON tablename
[FOR EACH ROW]
[DECLARE ...]
BEGIN
-- PL/SQL code goes here.
[EXCEPTION ...]
END;
/
BEFORE: defines the trigger is executed before the statement executed on table.
AFTER: defines the trigger is executed after the statement executed on table.
ROW LEVEL TRIGGER:FOR EACH ROW Clause is used for defined, trigger is executed at each row modification.
STATEMENT LEVEL TRIGGER: without clause FOR EACH ROW, executed at start and end at table level.
Example of ROW-LEVEL or STATEMENT-LEVEL trigger:
Before statement-level trigger executes
Before row-level trigger executes
One row is updated
After row-level trigger executes
...
Before row-level trigger executes
Millionth row is updated
After row-level trigger executes
After statement-level trigger executes
Note: Basically Statement trigger is used data is in bulk inserted or updated or deleted operation is started.

Check trigger information in database

SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE TABLE_NAME = 'SALES'
SELECT * FROM DBA_TRIGGERS WHERE TRIGGER_NAME = 'AW_DROP_TRG';
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER';
SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'TRIGGER_NAME';

Get source or code for trigger

set heading off;
set echo off;
Set pages 999;
set long 20000;
select dbms_metadata.get_dependent_ddl('TRIGGER','EMP','SCOTT') output from dual;

Enable or Disable the Triggers

--Enable or disable single trigger
ALTER TRIGGER triggername DISABLE;
ALTER TRIGGER triggername ENABLE;

--Enable or disable all trigger at table level
ALTER TABLE tablename DISABLE ALL TRIGGERS;
ALTER TABLE tablename ENABLE ALL TRIGGERS;

--Enable or disable all trigger at schema level
select 'ALTER TABLE '||table_name||' DISABLE ALL TRIGGERS;' from user_triggers;
select 'ALTER TABLE '||table_name||' ENABLE ALL TRIGGERS;' from user_triggers;

Errors:
ORA-04082: NEW or OLD references not allowed in table level triggers
Solutions:
1. Eliminate the use of NEW OR OLD your statement in trigger.
2. Another option make add FOR EACH ROW clause in trigger.

Example of ROW LEVEL Trigger with OLD OR NEW keyword
We have table hr.sales for keep current value of sales data and hr.sales_bkp for taking backup or modification going in sales for keep track. We created trigger on sales table for taking backup.
OLD : old is pointing to UPDATE or DELETE old records.
NEW : New is pointing to INSERT or UPDATE new records.
Note: Update statement is having both OLD or NEW data pointer.

CREATE OR REPLACE TRIGGER t_test
BEFORE INSERT OR UPDATE OR DELETE ON hr.sales
FOR EACH ROW
BEGIN
IF inserting THEN
insert into hr.sales_bkp values ( :new.id, :new.name, :new.sales, sysdate ,'I');
END IF;
-- always set the modified date to now
IF updating THEN
insert into hr.sales_bkp values ( :old.id, :old.name, :old.sales, sysdate ,'U');
END IF;
IF DELETING THEN
insert into hr.sales_bkp values( :old.id, :old.name, :old.sales, sysdate ,'D');
END IF;
END;
/
Trigger created.

SQL> insert into sales values (1,'LG WM',25000);
1 row created.

SQL> update sales set name='SONY WM' where id = '1';
1 row updated.

SQL> update sales set sales='35000' where id = '1';
1 row updated.

SQL> delete from sales;
1 row deleted.

SQL> select * from sales_bkp;

  ID NAME            SALES CREATED_D F
---- ---------- ---------- --------- -
   1 LG WM           25000 11-SEP-18 I
   1 LG WM           25000 11-SEP-18 U
   1 SONY WM         25000 11-SEP-18 U
   1 SONY WM         35000 11-SEP-18 D

Example of STATMENT LEVEL TRIGGERS

--Create Statment Level Trigger
CREATE OR REPLACE TRIGGER trigger_Statement_test
BEFORE INSERT OR UPDATE OR DELETE ON hr.sales
BEGIN
IF inserting THEN
insert into hr.sales_bkp values ( '10','INSERTED',0,sysdate,'I');
END IF;
-- always set the modified date to now
IF updating THEN
insert into hr.sales_bkp values ( '10','UPDATED',0,sysdate,'U');
END IF;
IF DELETING THEN
insert into hr.sales_bkp values ( '10','DELETED',0,sysdate,'D');
END IF;
END;
/
--In this bulk operation of insert statement, Only one entry due to statement trigger.
insert into sales select * from sales_history;
4 rows inserted

--Check SALES_BKP table having STATEMENT LEVEL trigger entry and also have entries for ROW LEVEL trigger created in above example.
select * from sales_bkp;

   ID NAME            SALES CREATED_D F
----- ---------- ---------- --------- -
   10 INSERTED            0 11-SEP-18 I
    1 LG              20000 11-SEP-18 I
    2 SONY            20000 11-SEP-18 I
    3 DELL            25000 11-SEP-18 I
    4 LENOVO          40000 11-SEP-18 I

--Now Sales table has two triggers
SQL> select trigger_name from all_triggers where table_name = 'SALES';
TRIGGER_NAME
-----------------------
T_TEST
TRIGGER_STATEMENT_TEST

DDL Execution in Oracle Sharded Database

DDL Execution in Oracle Sharded Database
The Shard catalog database has copies of all the objects present in sharded database. If catalog validate the DDL then it applied on all shards(database).
If new shard is added then from catalog database it will propagate the ddls to all the shards (databases).

Two ways you can issue DDLs in an SDB:

1. GDSCTL sql command.
When you execute a command , all shards are updated and return status of execution. A master copy is present in shard catalog.


GDSCTL> sql "create tablespace set tbsset"

2. Connect to the shard catalog database using SQL*Plus using the GDS$CATALOG.sdbname service
When you execute the command, local shards response on execution and all other shards are working inbackgroud.

SQL> create tablespace set tbsset;

For SQL PLUS two type of object is created. SDB object and local object. Local objects are the object that exist in Shard catalog and used for administrative purpose.
Note: type of object (SDB or local) that is created in a SQL*Plus session depends on whether the SHARD DDL mode is enabled in the session. Mode is enabled default if connect with catalog database of SDB user.

--For Disable create local object
alter session disable shard ddl;

--For enable create shared objects
alter session enable shard ddl;

Note: If you direct connected with shared user or catalog, you donot need to enable shared it automatic enabled.

Check Status of DDL Commands:
SHOW DDL and CONFIG SHARD is used from GDSCTL. It will tell you failed shards on which command is failed.

GDSCTL> show ddl
id DDL Text Failed shards
-- ------------------------------------------- -------------
1 create user example_user identified by *****

Note: IF DDL failed on one shard all further DDL are blocked until failure is resolved (GDSCTL recover shard)

Example For checking the DDL command executed on all shreds:

-- Enable the Shard DDL Session
SQL> alter session enable shard ddl;

-- Fire the Command for creating a User
SQL> CREAT USER example_user IDENTRIFIED BY password1;
CREATE USER example_user IDENTRIFIED BY password1
*
ERROR at line 1:
ORA-00922: missing or invalid option

-- Check the Command show in List.
GDSCTL> show ddl
id DDL Text Failed shards
-- -------- -------------

Note: it show command is not listed means not executed in any Shard(database)

--Fire the create user command again
SQL> CREATE USER example_user IDENTIFIED BY password1;
User created.

-- Show the command is executed successfully
GDSCTL>show ddl
id DDL Text Failed shards
-- ------------------------------------------- -------------
1 create user example_user identified by *****

Oracle Redaction

Oracle 12c having new feature Oracle Redaction

Oracle redaction means mask the data while fetching by application. It have the ability to redact sensitive data.

Types of Redaction:
Full Redaction: Redact full column data.
Partial Redaction: column data portion or part is redact as per need.
Regular Redaction: Regular expression to redact column data as a pattern. It is for character data only.
Random Redaction: It will generate random value at time of query.
No Redaction: Test the internal operation of redaction policy.

Syntax to add policy of Redaction:

DBMS_REDACT.ADD_POLICY (
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2,
policy_description IN VARCHAR2 := NULL,
column_name IN VARCHAR2 := NULL,
column_description IN VARCHAR2 := NULL,
function_type IN BINARY_INTEGER := DBMS_REDACT.FULL,
function_parameters IN VARCHAR2 := NULL,
expression IN VARCHAR2,
enable IN BOOLEAN := TRUE,
regexp_pattern IN VARCHAR2 := NULL,
regexp_replace_string IN VARCHAR2 := NULL,
regexp_position IN BINARY_INTEGER :=1,
regexp_occurrence IN BINARY_INTEGER :=0,
regexp_match_parameter IN VARCHAR2 := NULL);

Example to add full redaction
In number datatype it change the value to 0 as default in full redaction.

BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
column_name => 'commission_pct',
policy_name => 'redact_com_pct',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/

Check the table after apply the redact policy

SELECT COMMISSION_PCT FROM HR.EMPLOYEES;
COMMISSION_PCT
--------------
0
0

Check the reduction present in database

SELECT * FROM REDACTION_POLICIES;

SELECT * FROM REDACTION_COLUMNS;

SELECT * FROM REDACTION_VALUES_FOR_TYPE_FULL;

Alter the existing reduction syntax

DBMS_REDACT.ALTER_POLICY (
object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2,
action IN BINARY_INTEGER := DBMS_REDACT.ADD_COLUMN,
column_name IN VARCHAR2 := NULL,
function_type IN BINARY_INTEGER := DBMS_REDACT.FULL,
function_parameters IN VARCHAR2 := NULL,
expression IN VARCHAR2 := NULL,
regexp_pattern IN VARCHAR2 := NULL,
regexp_replace_string IN VARCHAR2 := NULL,
regexp_position IN BINARY_INTEGER := NULL,
regexp_occurrence IN BINARY_INTEGER := NULL,
regexp_match_parameter IN VARCHAR2 := NULL,
policy_description IN VARCHAR2 := NULL,
column_description IN VARCHAR2 := NULL);

ACTION parameter value defines what will the change done on the existing redaction policy: Following are the value defines the ACTION value:
DBMS_REDACT.MODIFY_COLUMN : Change the column_name value.
DBMS_REDACT.ADD_COLUMN : Add a new column in existing redaction policy.
DBMS_REDACT.DROP_COLUMN : Remove column from redaction policy.
DBMS_REDACT.MODIFY_EXPRESSION: Change the expression value.
DBMS_REDACT.SET_POLICY_DESCRIPTION: Change the description
DBMS_REDACT.SET_COLUMN_DESCRIPTION: Change the description of column.

Example of adding a new column in existing policy of data redaction

BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol',
action => DBMS_REDACT.ADD_COLUMN,
column_name => 'hire_date',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => DBMS_REDACT.REDACT_DATE_EPOCH);
END;
/
Note: detail you got in partial redaction page.

Drop the policy

BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol');
END;
/

Enable the data redaction policy

BEGIN
DBMS_REDACT.ENABLE_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol');
END;
/

Disable the data redaction policy

BEGIN
DBMS_REDACT.DISABLE_POLICY (
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol');
END;
/

Rman Merged backup

Rman merged backup script for windows environment:

Rman Merged Backup Script works as for both level 1 backup merged with level 0 backup.

Level 0 Backup is full backup of database

Level 1 is incremental backup after level 0.

Merged level 1 backup merged backup with level 0 backup files. It update old level 0 files backup and merge with them.

Following command is used at Enterprise edition to enable the block change tracking for level 1 incremental backup. Its not supported in Standard Editions

It will track all the record updated after level 0 backup so that it fasten the process of rman level 1 backup

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'C:\blockchangetracking.dbf' REUSE;

Level 0 Backup Script

run {
CONFIGURE RETENTION POLICY TO REDUNDANCY 10;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\RMAN\Db_df%t_s%s_s%p';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\rman\ctrl_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
BACKUP INCREMENTAL LEVEL 0 DATABASE format 'C:\RMAN\%d_%s_%T_%c_DB0' TAG="DB_LEV0";
BACKUP ARCHIVELOG ALL format 'C:\RMAN\%d_%s_%T_%c_ARCH0' tag "ARCH_LEV0";
backup current controlfile format 'C:\RMAN\%d_%s_%T_%c_ARCH0%d_%s_%T_CONTROL' tag "CNTRLFL";
backup spfile format 'C:\RMAN\%d_%s_%T_%c_ARCH0%d_%s_%T_SPFILE' tag "SPFILE" ;
}

Level 1 Backup

run {
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG DB_LEV0 DATABASE;
RECOVER COPY OF DATABASE WITH TAG DB_LEV0;
BACKUP ARCHIVELOG ALL format 'C:\RMAN\%d_%s_%T_%c_ARCH0' tag "ARCH_LEV1";
backup current controlfile format 'C:\RMAN\%d_%s_%T_CONTROL' tag "CNTRLFL" ;
backup spfile format 'C:\RMAN\%d_%s_%T_SPFILE' tag "SPFILE" ;
}