Tabular Forms - Expert Oracle Application Express - page 201

table has a column MGR showing the manager's ID. You will create an additional column showing
manager names ( MGR SEARCH ) and parse the selected manager's ID into the MGR column.
For this, you will need an additional procedure and two additional functions in your package:

• A procedure for creating a list of managers based on the EMP table and the

selected manager used for the On-Demand Process.

• A function for generating JavaScript. This function will also create an additional

text item used for the autocomplete list. You will assign the array 50 to it.

• An additional function for returning ENAME from the EMP table for the existing

MGR entries in the EMP table.

These additional procedures are shown in Listing 3-16.

Listing 3-16. Tabular Form—SQL for Autocomplete Returning Key Value

FUNCTION get manager for id (p mgr IN NUMBER)
RETURN VARCHAR2;

PROCEDURE get manager;

FUNCTION get autocomplete key (
p value IN VARCHAR2,
p key item IN VARCHAR2,
p rownum IN VARCHAR2,
p width IN NUMBER,
p process IN VARCHAR2,
p max length IN NUMBER DEFAULT 80,
p size IN NUMBER DEFAULT 80
)
RETURN VARCHAR2;

FUNCTION get manager for id (p mgr IN NUMBER)
RETURN VARCHAR2
IS
v ename emp.ename%TYPE;
BEGIN
FOR c IN (SELECT ename
FROM emp
WHERE empno = p mgr)
LOOP
v ename := c.ename;
END LOOP;

RETURN v ename;
END get manager for id;

PROCEDURE get manager
IS
v search VARCHAR2 (255);
v output VARCHAR2 (400);