EXECUTE IMMEDIATE and DBMS_SQL

DYNAMIC SQL 

Dynamic SQL is a SQL statement that contains variables that can change during runtime.Is written using either DBMS_SQL or native dynamic SQL (Execute Immediate).

EXECUTE IMMEDIATE: The Execute Immediate statement can perform dynamic single-row queries. Also, this is used for functionality such as objects and collections, which are not supported by DBMS_SQL. If the statement is a multi-row SELECT statement you use OPEN-FOR, FETCH and CLOSE statements.

DBMS_SQL: The DBMS_SQL is used to perform dynamically multi-row query. DBMS_SQL package is used to write dynamic SQL in stored procedures and to parse DDL statements. Some of the procedures and functions of the package include:

1)      OPEN_CURSOR: Opens a new cursor and assigns a cursor ID number.

2)      PARSE: Every SQL statement must be parsed. Parsing the statement includes checking the statements syntax and validating the statement , ensuring that all references to objects are correct and ensuring that the relevant privileges to those objects exist.

3)      BIND_VARIABLES: Binds the given value to the variable identified by its name in the parsed statement in the given cursor.

4)      EXECUTE: Executes the SQL statement and returns the number of row processed.

5)      FETCH_ROWS: Retrieves a row for the specified cursor (for multiple rows, call in a loop)

6)      CLOSE_CURSOR: Closes the specified cursor.

 

­­You can use the INTO clause for a single-row query, but you must use OPEN-FOR, FETCH and CLOSE for a multi row query.

Dynamic SQL supports all the SQL data types but does not supports PL/SQL specific types except PL/SQL record.

DBMS_JOB: Enables the scheduling and execution of PL/SQL programs.

To Find Duplicate Data Quarter-Wise in Oracle

Query to fetch duplicate data entered Quarter-wise in Oracle:

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

CREATE TABLE TEMP

(PRODUCT_ID NUMBER PRIMARY KEY,

STOCK_NO VARCHAR2(30),

PRICE VARCHAR2(30),

QTY NUMBER,

CREATION_DATE DATE);

Step 2: Insert data into “TEMP” Table.

INSERT INTO TEMP VALUES (1,'MM001','45.55',5,'22-JAN-2012');

INSERT INTO TEMP VALUES (2,'MM002','55.00',6,'2-FEB-2012');

INSERT INTO TEMP VALUES (3,'MM003','60.00',8,'20-APR-2012');

INSERT INTO TEMP VALUES (4,'MM001','46.00',5,'29-MAR-2012');

INSERT INTO TEMP VALUES (5,'MM004','40.00',9,'2-JUL-2012');

INSERT INTO TEMP VALUES (6,'MM003','61.00',65,'3-MAY-2012');

INSERT INTO TEMP VALUES (7,'MM005','69.00',23,'22-AUG-2012');

INSERT INTO TEMP VALUES (8,'MM006','56.00',2,'22-SEP-2012');

INSERT INTO TEMP VALUES (9,'MM007','80.00',6,'22-OCT-2012');

INSERT INTO TEMP VALUES (10,'MM008','75.00',7,'22-NOV-2012');

INSERT INTO TEMP VALUES (11,'MM003','75.00',8,'22-NOV-2014');

Commit;

Step 3: Select the “TEMP” Table.

SELECT * FROM TEMP;

PRODUCT_ID	STOCK_NO	PRICE	QTY	CREATION_DATE
1	MM001	45.55	5	22-Jan-12
2	MM002	55	6	2-Feb-12
3	MM003	60	8	20-Apr-12
4	MM001	46	5	29-Mar-12
5	MM004	40	9	2-Jul-12
6	MM003	61	65	3-May-12
7	MM005	69	23	22-Aug-12
8	MM006	56	2	22-Sep-12
9	MM007	80	6	22-Oct-12
10	MM008	75	7	22-Nov-12
11	MM003	75	8	22-Nov-14

Step 4: Run the below query to fetch duplicate Stock Numbers entered Quarter-wise from given set of data. 

SELECT * FROM TEMP WHERE (STOCK_NO,TRUNC(CREATION_DATE,'Q')) IN(SELECT STOCK_NO,TRUNC(CREATION_DATE,'Q') FROM TEMP GROUP BY STOCK_NO,TRUNC(CREATION_DATE,'Q') HAVING COUNT( STOCK_NO)>1)

ORDER BY STOCK_NO;

Output:

PRODUCT_ID	STOCK_NO	PRICE	QTY	CREATION_DATE
1	MM001	45.55	5	22-Jan-12
4	MM001	46	5	29-Mar-12
3	MM003	60	8	20-Apr-12
6	MM003	61	65	3-May-12