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

user_9485660 0
Viewed 108

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

oracle-apex oracle sql
1 Answers

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:

user_90979060
Related