Objective: To split the given string separated by special character as an individual column value using oracle pipelined Function. Step 1: Create table type of varchar: create or replace…
Read MoreTable of Contents Overview Technologies and Tools Used Use Case Architecture Screen Shot 1. Overview This document will be helpful to compares Prod and Dev schema and generates alter scripts…
Read MoreRETURNING Clause With EXECUTE IMMEDIATE 1. Overview The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete…
Read MoreRETURNING Clause Using Bulk Collect 1. Overview The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form.…
Read MoreUse RETURNING Clause to Avoid Unnecessary SQL Statements 1. Overview The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an…
Read MoreCreates the DDL for the specified job:- SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY',…
Read MoreScript to create foreign key DDL on all tables. SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param…
Read MoreThe Oracle introduced the PIVOT clause from oracle 11g. This makes converting rows to column easy. The value in the new columns must be the result of an aggregate function…
Read MoreObjective: To find the validity of the email address using Oracle Pl/sql and to find the invalid email address from a object like table using Oracle Sql. Using Pl/Sql: DECLARE…
Read More— Trigger to check if the sal greater than 100000, if yes, log the error message, else update sucessfully create or replace trigger trig_validate_sal before update of sal on emp_t…
Read More