Wednesday, January 5, 2011

Variables in APEX

When I first started using APEX I was unsure about which method to use when referencing variables (values in session state). The purpose of this post is to outline the different ways to reference APEX variables and provide an examples for each case.

They're five ways to reference variables in APEX:

  • :Bind_variables

  • &Substitution_strings.

  • V / NV functions

  • #Hash#

  • "Shortcuts"

Bind Variables
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:
SELECT ename
  FROM emp
 WHERE deptno = :p1_deptno
Where P1_DEPTNO is a page item that you created.

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.

20 comments:

  1. Is it possible to use substitution strings in helptext? It's not working for me.

    ReplyDelete
  2. I just tested on apex.oracle.com (which is using version 4.0.2.00.07) and you can use substitution strings in help text.

    What version of APEX are you using?

    ReplyDelete
  3. I'm using 4.0.2.00.07 as well.
    Oh I guess I should've specified that it's help text within a table column of an interactive report.

    something in the format of &P1_TEXT. does not work :/

    ReplyDelete
  4. Hi,

    I just tested it and it works in an IR. Does your item have a value when the IR is being generated?

    If you want please send me an email (address is on the top right of the blog) and we can trouble shoot it.

    Martin

    ReplyDelete
  5. Oh don't worry about it. I've found a way to work around the problem. Thanks for the help though.

    ReplyDelete
  6. What if the variable is not a page item (like P1_DEPTNO for example) but a report column (like RC_DEPTNO for example).
    How can I refer to this report column? :RC_DEPTNO? It is not working

    ReplyDelete
    Replies
    1. Report columns can be referenced when editing the column itself and you should use the #COLUMN_NAME# notation. This variable is only valid in the column attributes.

      Delete
    2. So you cannot reference a Report column outside of the Report?

      Delete
    3. Hi Nick,

      The simple answer is no, you can't access a report column outside of a report. You can however create a custom report template which references the column position.

      Martin

      Delete
    4. Martin, we've just introduced a pivot query to a report, which creates 3 pivot columns in the FOR clause, EARLY, LATE and NIGHT.

      However, referring to #LATE# in the report > column > HTML expression doesn't seem to work (have tried #'LATE'# as well).

      So we tried clicking the torch on the COLUMN LINK, VALUE, to get Apex to show us what it thinks the column is called. It shows #LATE# in the list, but it cannot be selected.

      Any ideas on how to reference the pivot column? I guess because it is derived, Apex is struggling, but didn't know if you'd heard of any problems/solutions?
      Many thanks in advance.

      Delete
    5. That's odd since #LATE# should work. If you'd like me to look into this please create a sample app in apex.oracle.com and give me access (email me @clarifit.com)

      Delete
    6. Martin, thank you for your reply. As the application was sensitive and because I was helping a colleague, so I wasn't in full possession of the project, I decided to formulate an e-mail to you, to show you the problem with a few screen shots.

      As I started to build the shots, the field worked! Typical. I promise that it wasn't working, and that we had tried every combination (I've been programming for 20 years now, so I'll put it down to "one of those things" even thinking of having that on my gravestone :o) )

      It still won't let you select the field in the link column, so Apex is slightly unhappy with the pivot SQL, but it worked with the HTML, thanks for your time, hope I didn't waste too much of it.

      Delete
  7. Thanks for the variable explanation they where very helpfull

    ReplyDelete
  8. Hi Martin,

    We are calling a procedure(to download attachment) as below,
    select
    ''
    ||attachment_name
    || '
    ' "Attachment
    Name"
    from attachment

    In this procedure we are using v('APP_ALIAS'), which is not being fetched.

    Is this because we are calling it from query instead of process. Please let me know how can I get it work.

    Thanks,
    Shoaib

    ReplyDelete
    Replies
    1. That should work. I'd ensure that your app has an APP_ALIAS and that you're calling this procedure from within an APEX session. I.e. if you were to test it in SQL*Plus without manually simulating an APEX session, APP_ALIAS would be null.

      Delete
    2. Martin,

      thanks for your reply. They are fetched perfectly when I call the same procedure from a apex page process.

      Thanks

      Delete
    3. If you're testing from a PL/SQL session you can create an APEX session by using the following technique: http://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql.html Note This procedure will be part of the new OOS Utils package: https://github.com/OraOpenSource/oos-utils/issues/7

      Delete
    4. Thanks Martin.

      The issue was we were calling the procedure from the link of report query. When the link in the report is clicked, it is the browser that is calling the procedure, not APEX. Setting Apex session is also very helpful.

      Delete
  9. Hi Martin...

    Your hyperlink to "referencing session state" under the "Shortcuts" header no longer works. Have you an alternative, please? Thanks loads.


    -victorbax-
    -leiderdorp, the netherlands-

    ReplyDelete