Integrated Document Solutions
for Business Applications

Sort Posts

Get Fresh Updates

Home / Blog / Current Page

Forms Personalization: How To Pass Function Parameters

Posted on by Brent Lowe

To continue with my last post on adding a new icon to the menu bar, the ultimate goal of my personalization was to launch a form with dynamic parameters that are named. Launching a form is no problem with Forms Personalization, you simply have a ‘Built-In’ action with the type ‘Launch a Function’.

Forms Personalization: Launch a Function

Knowing what to put in the parameter field is where I got a little lost. I needed to pass through multiple NAMED parameters consisting of information from the form itself (block.item) and SQL Statements. For example:

PARAM1=<value1> PARAM2=<value2>
value1 is a field on the current form I was personalizing
value2 is the result of a SQL statement

The documentation for Forms Personalization did not really have too much meat as to how to do this, so through some trial and error, the documentation, some forum postings and a My Oracle Support note (429604.1)… I found the following to be true.

1. You can pass form field values as named parameters using the following syntax:
‘PARAM1=’ || ${item.<block>.<item>.value} || ‘ PARAM2=’ || ${item.<block>.<item>.value}

2. Additionally, you can pass form field values as named parameters using the following syntax:
=’PARAM1=’ || :block.item || ‘ PARAM2=’ || :block.item

3. You can pass the value returned from a select statement as a named parameter using the following syntax:
=select ‘PARAM1=’ || <column> A from <table> where <x> = <:block.item>

4. And finally you can combine 2 & 3 above to pass both form field values and the result of a SQL statement at the same time using the following syntax:
==select ‘PARAM1=’ || <:block.item> || ‘ PARAM2=’ || <column> A from <table> where x = &lt:block.item>

Hope this helps!

Other content you may find interesting:

Share This

13 Responses to Forms Personalization: How To Pass Function Parameters

  1. michael says:

    many thanks to your blog!

  2. Varaprasad Akula says:

    WIll it take the null values for the parameter .. ???

    • Brent Lowe says:

      May need some more information here in order to answer appropriately. If you do not specify a value for a parameter then the form will just use the default value.

  3. Avi says:


    I need to launch Ar Transaction form from Order line. I have this Sql to make the link between the Order line and the AR Transaction. The destination Function is “Transactions View”

    What is the format i need to put in the Parameters ? I have this select:

    = ‘fp_customer_trx_id = SELECT rca.customer_trx_id
    FROM oe_order_lines_all oola
    ,ra_customer_trx_lines_all rctl
    ,ra_customer_trx_all rca
    Where 1=1
    And rctl.Interface_line_attribute6=:LINE.LINE_ID
    And rca.customer_trx_id =rctl.customer_trx_id’

    But it does not work

    • Brent Lowe says:

      Hi Avi, if you are trying to populate the parameter fp_customer_trx_id from a select statement, try the method in option 3 of the post:

      select ‘fp_customer_trx_id=’ || rca.customer_trx_id a from …..

      If your query is correct, this will produce a string that ends up looking like this

  4. coba says:

    Great post, really helpful. Many thanks for sharing your knowledge.

  5. ahmed says:

    how can i get result from the following equation through personalization

    i want to know what is the syntax and what is the required steps to get the result automatically


  6. Thomas says:

    I am using your notes on this set of parameters and they are working great till I added a parameter where the values have spaces in them. The parameter comes across with the first part of the data up until the spaces and therefore the form fails because it doesn’t find this data from the validation query I have on the form.

    I tried by passing as a parameter


    I tried by passing in the actual text value


    I tried using what I found on spaces


    Double Quotes (Wasn’t sure if it was single quotes or double quotes.

    Still only passes DTOC.

    Any help would be appreciated.

    • Brent Lowe says:

      Hi Thomas, thanks for reading.

      We had the exact same problem with spaces. In the end we were unable to find an ‘Oracle’ way to do this so we made up our own. Because the personalization that we were setting up was to invoke a form that WE wrote we had some leeway. Essentially what we did was that we replace the spaces when passing in the parameters with %20. In the form, we take the parameters and replace %20 with space resulting in the orginal value. We chose %20 because it is the URL Encoding value for a space and we were confident it would not already exist in our incoming data.

      For example:


      In order to make this dynamic, you will likely have to use the SELECT syntax to set the parameters and use the SQL REPLACE function to replace spaces with %20 or whatever character(s) you choose to use. Of course, this solution all depends if you have control over the form you are launching from the personalization.

      Hope this helps. If you do find a Oracle way to pass spaces that does work, please do share.


  7. Roshan says:

    Dear Brent Lowe, Hope you are doing great.
    I am exactly looking to pass a value to the form function to launch a form. I followed the guidance exactly as per your suggestion. But I have not had success…Your suggestion was =select ‘PARAM1=’ || A from where =

    I tried to use the following variations with no success.

    =select ‘DEMAND_SOURCE_HEADER_ID =’ || sales_order_id a from mtl_sales_orders where segment1 = :ORDER.HEADER_ID

    =select ‘DEMAND_SOURCE_HEADER_ID =’ || sales_order_id from mtl_sales_orders where segment1 = :ORDER.HEADER_ID

    =select ‘DEMAND_SOURCE_HEADER_ID =’ || sales_order_id from apps.mtl_sales_orders where segment1 = :ORDER.HEADER_ID

    Your help will be highly appreciated. Thanks.

    • Roshan says:

      I think i was able to resolve with the following. Apologies for the inconvenience.

      =select ‘DEMAND_SOURCE_HEADER_ID =’ || TO_CHAR(SALES_ORDER_ID) from apps.mtl_sales_orders where segment1 = :ORDER.ORDER_NUMBER

      This might help others through your blog.

  8. Jojo says:


Leave a Reply to Thomas Cancel reply

Your email address will not be published. Required fields are marked *