Lexical Parameter

Lexical Parameters:

Lexical Parameters performs dynamic SQL query.

Use to change the data definition at runtime.

Use Lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

Denoted by ‘&’.

You cannot make lexical references in a PL/SQL statement. However, use a bind Reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.

Code is written in AFTER PARAMETER FORM Trigger.

Example:

     DATA MODEL QUERY:

     Select * from emp where dept_id=10 &status-----lexical parameter

    CODING AT AFTER PARAMETER FORM

    Function AfterPForm return Boolean is

    BEGIN

    IF :status=’ALL’ Then

    :status:=’AND (STATUS IN (“P”,”N”) OR STATUS IS NULL)’;

    ELSIF :status=’P’ Then

    :status:=’AND STATUS=”P” ‘;

    ELSE

    Null;

    END IF;

    END;

Report Builder Columns

Different Columns contained in Report Builder are:

1)      Data Columns

2)      Formula Columns

3)      Summary Columns

4)      Placeholder Columns

Data Columns:

Data Columns contain the data values for a report.

Data Columns are corresponding to the table columns included in the select list of a query.

Each column is placed in the group associated with the query.

Formula Columns:

Formula Columns performs user-defined computations.

Formula Columns executes a PL/SQL function and must return a value.

Formula Columns can be placed at the Report or Query level.
Formula Columns should not be used to set a parameter’s value.

Formula Columns is denoted by CF_

Summary Columns:

Summary Columns are used for calculating summary information like

1)      Sum

2)      Average

3)      Minimum

4)      Maximum

5)      Count

6)      First

7)      Last

8)      % of Total

9)      Std Deviation

10)  Variance

Summary Columns can be placed at the Report or Query level.

Summary Columns is denoted by CS_

Placeholder Columns:

Placeholder Columns are storage areas in the Data Model.

Used for Temporary storage of data for future reference.

Placeholder Columns value can be set in the following places:

1)      Before Report Trigger

2)      Report-Level Formula Columns

Placeholder Columns can be populated by a Before Report Trigger but are generally populated by a Formula.

Although Placeholders allow PL/SQL to be written within them, they cannot be self-populated, their value must be set by an external program Unit, any attempts at self-population will result in a runtime compilation error. However the Formula assigned to the Placeholder must return a value.

Placeholder Columns is denoted by CP_

Example: At Formula column:

     Function CF_1formula return Number is

     Begin

             If :no_in_stock<10 then

                 :CP_1:=’Only’||:no_in_stock||’Items left in stock!!’;

             End if;

               Return 1;

     End;

Example at Placeholder column:

     Function CP_1formula return Char is

     Begin

               Return null;

     End;

Oracle PL/SQL Interview Questions

• What is difference between SQL and PL/SQL?
• Difference between Truncate and Delete commands.
• What is a Merge command?
• Difference between Implicit and Explicit Locking
•  Types of Joins in Oracle
•  Types of Integrity Constraints in Oracle
• How many different types of NULL functions are there in Oracle?
• Difference between Union and Union ALL
• What are Synonyms?
• What are materialized View? Syntax? How to create Primary key on Materialized Views?
• What are Indexes? Different types of Indexes?
• Types of Cursors?
•  Explain Cursors Attribute?
• Difference between Procedures and Functions
• Difference between Packages and Stored Procedures
• What are Packages?
• Name any In-built Oracle Packages
• Types of Triggers
• Difference between Before and After Trigger
• Difference between Package Specification and Package Body
• Types of Exceptions in PLSQL
•  What are Ref Cursors?
• Explain Optimization Techniques?
• What is Explain Plan?
•  Difference between Normalization and Denormalization
• Explain SDLC Life cycle and name few different Models
•  Explain AGILE Model
• What is Group BY and having clause?
• Oracle 11g new features
• Difference between VARCHAR and VARCHAR2
• Name parameter modes for stored procedures
• What is DB LINK?
• What is an Autonomous Transaction?
• What is a Savepoint
•  Explain Rollup and Cube statement
•  Write a query to find even rows from a Table
• Write a query to find duplicate rows from a Table
• Write a query to find nth highest salary from employee Table
• Write a query to find first 10 rows from a table
• Write a query to find random 7 rows out of 47 rows in a Table

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