Cabusao M1 M2 M3 Ta1
Cabusao M1 M2 M3 Ta1
Cabusao M1 M2 M3 Ta1
Administration
TECHNICAL ASSESSMENT
M1, M2, M3
Section: TR31
To find the number of airports from the countries table for a supplied country_name.
Based on this number, display a customized message as follows:
Write a PL/SQL block to read through rows in the countries table for all countries in region
5 (South America region). Country name must be entered by the user. For each selected
country, display the country_name, national_holiday_date, and national_holiday_name.
Use a record structure (user defined) to hold all the columns selected from the countries
table.
A. Add an exception handler to the following code to trap the following predefined Oracle
Server errors:
NO_DATA_FOUND, TOO_MANY_ROWS, and DUP_VAL_ON_INDEX. (5 pts)
B. Now test your block a third time using substring: al. There is exactly one language_name
beginning with
“Al”. Note that language_id 80 (Arabic) already exists. Explain the output. (5 pts)
Tried testing the above code with <substring> = al - Then it gives error DUP_VAL_ON_INDEX as record
with language id 80 already exists in the languages table.
C. Now (keeping the substring as “al”), add a non_predefined exception handler to trap then
encountered oracle exception code. Rerun the code and explain the result. (5 pts)
The code gives error message ORA-01400: cannot insert NULL into ("languages"."language_name")
Added non-predined exception named e_insert_excep to handle NULL value insertion in language name
column of language table.