Hi all!
I'm having a PL/SQL Anonymous Block here, which I wanted to use as a source for an interactive report. Only, I didn't knew that interactive reports didn't support PL/SQL...
So, the code:
DECLARE
a_userid NUMBER;
BEGIN
SELECT userid
INTO a_userid
FROM users
WHERE UPPER(username) = :APP_USER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
a_userid := 0;
IF a_userid = 0 THEN
SELECT distinct a.name AS Foodname, a.foodid AS Foodid
FROM food a
INNER JOIN foodunit c
ON a.foodid = c.foodid
WHERE a.foodlanguageid = :P18_LANGUAGE
OR a.foodlanguageid = :P18_SET_LANGUAGE
AND a.userid = 0
ORDER BY a.name;
ELSIF a_userid = :APP_USER THEN
SELECT DISTINCT a.name AS Foodname, a.foodid AS Foodid
FROM food a
INNER JOIN foodunit c
ON a.foodid = c.foodid
WHERE a.foodlanguageid = :P18_LANGUAGE
OR a.foodlanguageid = :P18_SET_LANGUAGE
AND a.userid = 0
AND a.userid = a_userid
ORDER BY a.name;
END IF;
END;
Off course this code won't work because I didn't include any INTO statements, but I'm just trying to make clear what it is that I'm aiming for.
The point of this code is that when a user is not logged in (and visits the page as a public user), it should fetch the rows 'NAME' and 'FOODID' from table FOOD. I've added a column to this table (named USERID), and every row has value '0' for this column.
Now, when a user signs in, he/she has a fixed userid (saved in a different table, named 'USERS'). A user is then able to add food items of their own to the list.
Off course, a visitor isn't supposed to see all the food items that have been added to the table by registered users. So, I wanted to display the data separated. Visitors (not registered) only see the default values (all rows in which the userid = 0) and registered users should see both their own added food items (say my userid is 1, then I want to see all rows for which the userid = 1) and the rows with default values (where userid = 0).
I found a way here:
http://www.oracleapplicationexpress.com/tutorials/71-oracle-apex-interactive-report-based-on-plsql-function
But this just doesn't makes sense to me. Is there either an easy way to explain me how the tutorial in the link works, or do you know an easier way? Like making a procedure or something, or a function, or a page process, and somehow refer to that in the SQL source code of the interactive report?
Any help is greatly appreciated.
APEX version: 4.1.1.00.23