Variables in APEX

PublishedJanuary 5, 2011

Comments

No comments yet. Be the first to comment.

More from this blog

SQLcl host Command

Oracle SQLcl is the command line tool to connect to an Oracle database. One neat feature it has is the ability to call host commands (i.e call terminal commands directly from SQLcl). You can use any of the following to trigger a host command: host ho...

Jul 5, 20251 min read

Tips for APEX Application Constants

How to use Text Messages for Constants

Jan 22, 20244 min read

SQL Developer for VS Code

Last week SQL Developer for VS Code was released (direct link to VS Code extension here). If you're not using VS Code (VSC) I highly suggest you download and install as it's the de facto editor for developers (regardless of programming language). I'v...

Jan 22, 20243 min read

How to Generate a sitemap.xml in SQL

I recently had to generate a sitemap.xml for an public facing APEX application. The official sitemap protocol has the following example: <?xml version="1.0" encoding="UTF-8"?> <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> ...

Jan 17, 20242 min read

What Happens to APEX Page Items that are Never Rendered?

Every APEX page item has a Server Side condition which controls whether the item is rendered on the page. They're various server side condition options and for the purpose of this article we'll just set it to Never (can do things like queries that re...

Jan 15, 20242 min read

TalkApex

321 posts

Substitution Strings

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:

>Hello &APP_USER. Welcome to the demo application.

V / NV Functions

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 AS BEGIN INSERT INTO tapex_log ( username, apex_page_id, access_date) VALUES (V ('APP_USER'), NV ('APP_PAGE_ID'), SYSDATE); END;

#Hash#

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.

Shortcuts

Shortcuts can only be used in specific areas within APEX (such as some regions, labels, and templates) and can be rendered both statically or dynamically (using PL/SQL functions). To reference a Shortcut use the "shortcutname" notation (quotes included). The following article covers Shortcuts in more detail, including where you can use them in APEX: http://www.talkapex.com/2014/02/apex-shortcuts.html

For more information about referencing variables in APEX read the Referencing Session State section in the builder guide.

Command Palette