Oracle Apex 5.1: Split values of page item

1
votes

i try to split the values of an page item in Oracle Apex 5.1. What I did first was to concat the values like this for an autocomplete. The users need to know the department of the employees. The result is saved in the page item PX_Employee. 

select name || ',' || department from table;

Result: John Cooper, Department1

In order to get the E-Mail of John Cooper I need to split the values from each other. To get the email I use this code (works fine without concat)

DECLARE
  mail VARCHAR(40);
BEGIN 
  select email 
    into mail 
    FROM table 
    WHERE NAME = :PX_Employee;

  :PX_MAIL := mail;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    mail := NULL;
END;
2
oracleoracle-apexoracle-apex-5.1
Why are you trying to select a name that matches the concatenation of name and department? You need to show exactly what you are doing to set the value of :PX_Employee. - chrisis

2 Answers

2
votes

A simple SUBSTR with INSTR does the job. A few notes:

  • you don't need a local variable; put the result directly into the PX_MAIL item
  • in order NOT to handle a possible NO_DATA_FOUND, use an aggregate function (such as MAX, the one I did):
    • if there's an employee with that name, SELECT will return its e-mail address
    • if there are two (or more) employees with the same name, it'll return an e-mail address of the last employee (sorted alphabetically). This is a possible situation; you'd rather NOT rely on names as identifiers - switch to some kind of a unique ID
    • if there's no row with such an employee, it won't return NO_DATA_FOUND but NULL (so you don't have to handle it separately)
begin
  select max(t.mail)
    into :PX_MAIL
    from your_table t
    where t.name = substr(:PX_EMPLOYEE, 1, instr(:PX_EMPLOYEE, ',') - 1);
end;

For example:

SQL> with test (px_employee) as
  2    (select 'John Cooper, Department 1' from dual)
  3  select substr(PX_EMPLOYEE, 1, instr(PX_EMPLOYEE, ',') - 1)
  4  from test;

SUBSTR(PX_E
-----------
John Cooper

SQL>
1
votes

You can use the Apex Util Function STRING_TO_TABLE to split the String again and extract the name that way.

DECLARE
    l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
    mail VARCHAR(40);
BEGIN
    l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE('John Cooper, Department1', ',');
    BEGIN select email into mail FROM table WHERE NAME = l_vc_arr2(1);
END;