sql - Oracle APEX Application Builder: Search for specific employee and return their name, salary & department in an hard coded textfield

Question

I am new to the Oracle APEX AB and currently trying to implement a specific mask that we need for work: We have a table in our SQL-Database for employees. It looks like the following:

| staff_id | name | salary | department_id|

The goal is to have an own input mask, where I have to write down the staff_id, e.g. 100600. Then I click a submit button and the output is created dynamically in the same mask as a text that consists the following (column value in brackets): "Employee [name] has a salary of [salary] € and works in department [department_id].

This scenario is visualized in the following picture:

My problem now is that I don't understand the following things:

• Which element do I need to use in the page designer as an input? (Currently I am trying it with a numberfield)
• How can I extract the value out of this numberfield and use it into an sql query for the output text.
• Third: How can I create a hard coded textfield that will only appear after submitting with columns that dynamically adjust to the Input-ID?

So the workflow should be something like: First: Insert staff_id into numb_field and submit Second: SELECT name, salary, department_id FROM EMPLOYEE WHERE STAFF_ID = numb_field_value Third: Put the received values name, salary, department_id in a hardcoded text as the gap fillers and give it as an output on the screen.

Thank you for your help! :)

Visualization of the Input and Output of the procedure

question from:https://stackoverflow.com/questions/65859662/oracle-apex-application-builder-search-for-specific-employee-and-return-their-n

Answer 1

Here's how:

• create Number datatype item, let's call it P72_STAFF_ID

• create a button; let it Submit the page

• create a display only item with no label (so that it is "invisible")

• create a process which runs PL/SQL code:

  select 'Employee ' || name || ' has a salary of ' || salary ||
         ' € and works in department ' || department_id
  into :P72_INFO
  from employees
  where staff_id = :P72_STAFF_ID;       
  

Run the page, enter employee's ID and hit the button. You'll see something like this: