Oracle Portal Dynamic SQL Portlets

I recently had some trouble in getting parameter-based SQL Omni Portlets to do what I wanted in Oracle Portal. I wanted the portlet to show different results for each user, filtered on their username. I got confused by Page Parameters, which will not be needed in order to achive this functionality. After finally figuring out how it should be put together, it became a lot less complicated to set up. In this post I will provide the steps to how I was able to achieve the desired functionality. Hope someone finds it usefull!

1: Defining the portlet

First, add an OMNI portlet to the page that you want to work with. Lets assume you have a table called emp_salary that contains... well... you guessed it. Setup the SQL query like you normally would ("Add portlet" -> "Omni Portlet" -> "SQL") and specify the connection and SQL; e.g.:
Select empName, salary from emp_salary where empName = 'Andras'
Now make sure that the SQL works and save the portlet for later editing.

2: Defining a Portlet Parameter

Next we want a page parameter that will contain the name of the employee that we want to show the salary for. Go to the page properties:

Then select the Parameters tab

On this page, we can see a list of OmniPortlets which we can define parameters for. Let us go ahead and map Param1 to the logged in username:

This just maps a new parameter called "Param1" to the username of the currently logged in portal user (which is the value we want to use in our SQL-where-clause).

3: Using the Parameter in our SQL

We have now populated a portlet parameter that will contain the username of the current Portal User. Let's head back to our OmniPortlet and make use of it. Go back to the OmniPortlet and edit it by pressing the pencil icon (edit defaults). Now in the SQL section change the query to:
Select empName, salary from emp_salary where empName = :SELUSR
Then press the "Show Bind Variables" button, to display the bind variable:

We now want the value that we created earlier to be passed to the bind variable, and we there set the SELUSR field to contain the value: ##Param1##

Now apply the changes and/or press OK. All that is left now is checking if it works!