Address Formatting using HZ_LOCATIONS.LOCATION_ID

HZ_FORMAT_PUB.format_address (
-- input parameters
p_location_id IN NUMBER,
p_style_code IN VARCHAR2,
p_style_format_code IN VARCHAR2,
p_line_break IN VARCHAR2,
p_space_replace IN VARCHAR2,
-- optional context parameters
p_to_language_code IN VARCHAR2,
p_country_name_lang IN VARCHAR2,
p_from_territory_code IN VARCHAR2,
-- output parameters
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_formatted_address OUT NOCOPY VARCHAR2,
x_formatted_lines_cnt OUT NOCOPY NUMBER,
x_formatted_address_tbl OUT NOCOPY string_tbl_type
);
Pass LOCATION_ID to this procedure and it returns formatted address through OUT parameter X_FORMATTED_ADDRESS. You may also pass line break character CHR(10) so that different address lines are returned with carriage return at appropriate places.