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;