Fri. Apr 23rd, 2021

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

 75 total views,  1 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

Leave a Reply

Your email address will not be published. Required fields are marked *