Requirement: I have a page with two Items: Department ID and Department Name. Also, I have a button with the label as Get Department Name.

Now, when the user enters a valid Department ID value in the Department ID item and clicks on the Get Department Name button, it should fetch the corresponding Department Name from the DEPT table and should display that value in the Department Name item.

The sample screen is shown below.

We will use the DEPT table with the below Data.

Solution: For the solution of the above requirement follow the steps as shown below:

Step 1: Create a blank page with the Name as Set Item Value using PL/SQL

Step 2: Create a Region with the below properties

• Title: Set Item Value using PL/SQL
• Type: Static Content

Step 3: Create an item and set the below properties:

• Name: P13_DEPT_ID
• Type: Text Field
• Label: Department ID

Step 4: Create a button and set the below properties:

• Button Name: GET_DEPT_NAME
• Label: Get Department Name

Step 5: Create an item and set the below properties:

• Name: P13_DEPT_NAME
• Type: Text Field
• Label: Department Name
• Read Only > Type: Always

Step 6: Create a Dynamic Action for the GET_DEPT_NAME button. Name the Dynamic Action as getDeptNameDA

For the True outcome set the below properties:

• Action: Set Value
• Set Type: PL/SQL Function Body
• Items to Submit: P13_DEPT_ID
• Selection Type: Item(s)
• Item(s): P13_DEPT_NAME

Add the below code in PL/SQL Function Body:

DECLARE
    v_dept_name varchar2(200);
BEGIN
    SELECT dept_name INTO v_dept_name 
        FROM dept WHERE dept_id = :P13_DEPT_ID;
    RETURN v_dept_name;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN null;
END;

Click on Save and Run Page button. Thus, we can see the running page as below:

Provide the Department ID value as 102. Click on the Get Department Name button.

And, we can see the value in the Department Name field as Marketing as shown below.

Hence, the solution to our requirement.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 1,243 total views,  4 views today