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.
Thanks & Regards,
493 total views, 6 views today