They're four ways to reference variables in APEX:
- V / NV functions
Bind variables can be used in any block of SQL or PL/SQL code inside APEX. For example if creating a report to display all the employees in a selected department the query would be:
Where P1_DEPTNO is a page item that you created.
WHERE deptno = :p1_deptno
Substitution strings use the &variable. notation. They can be used anywhere in your APEX application such as a HTML region or even a template. You can also use them in inline SQL and PL/SQL code but it is not recommended for security reasons. For more information on this security risk read the following Oracle white paper on SQL Injection
A simple example of using a substitution string is in a HTML region which displays a welcome message. The region source would be:
V / NV Functions
Welcome to the demo application.
If you want to reference APEX variables in compiled code, such as views, packages, and procedures, you can't use bind variables. The easiest way to reference the variables is to use the V (for strings) and NV (for numbers) functions. For example:
CREATE OR REPLACE PROCEDURE log_apex_info
INSERT INTO tapex_log ( username, apex_page_id, access_date)
VALUES (V ('APP_USER'), NV ('APP_PAGE_ID'), SYSDATE);
The hash notation of #variable# is used in multiple places. When creating column links in a report you can use the hash notation to represent column values. The following figure highlights how column values are referenced in a report link. #EMPNO# is used to reference the EMPNO column and #JOB# is used to pass the job as a parameter to Page 2.
The hash notation is also used in templates (Shared Components > Templates). When modifying a template they're special variables available depending on the type of template. These variables can be found at the bottom of the screen in the Substitution Strings section.
For more information about referencing variables in APEX read the Referencing Session State section in the builder guide.