How to create a dropdown list for user input in sql, using sql developer?

Question

Can we achieve this in SQL, using SQL Developer? instead of entering user input, can I pic the user input from the list.

SELECT * FROM HR.LOCATIONS
WHERE country_id LIKE '&COUNTRY_ID%';

Please review the image enclosed.

SQL User Input expected as dropdown

Answer 1

You can satisfy this requirement using reports with child reports. To illustrate with the emp/dept schema.

1. Create a report called "DEPT" with SQL Query:

SELECT * FROM dept

2. Add a child report to this report named EMP with SQL Query:

SELECT * FROM emp WHERE depno = :deptno

When you run the dept report and click on a row, the corresponding rows in the emp table will be displayed in the child report. You can map this to your tables: the master report would be your instance report and the user details the child report.