Pregunta1.sql

create or replace PACKAGE sailors_package
IS
	PROCEDURE sailors_reserves_file(p_sid IN VARCHAR2, p_resultado OUT VARCHAR2);
END;
/

create or replace PACKAGE BODY sailors_package
IS
	PROCEDURE sailors_reserves_file(p_sid IN VARCHAR2, p_resultado OUT VARCHAR2)
	IS
		f UTL_FILE.FILE_TYPE;
	BEGIN
		f := UTL_FILE.FOPEN('SAILORS_DIR', 'sailors_reserves.txt', 'w');
		FOR sailor IN (select sid, sname, rating, age from sailors where sid = p_sid) LOOP
			utl_file.put_line(f, '=======================================================================================');
			utl_file.put_line(f, 'Sid: ' || to_char(sailor.sid));
			utl_file.put_line(f, 'Sailor Name: ' || to_char(sailor.sname));
			utl_file.put_line(f, 'Sailor rating: ' || to_char(sailor.rating));
			utl_file.put_line(f, 'Sailor age: ' || to_char(sailor.age));
			utl_file.put_line(f, '=======================================================================================');
			utl_file.put_line(f, '');
			utl_file.put_line(f, '============================= Sailor Reserves =========================================');
			utl_file.put_line(f, 'Boat Id    Boat Name      Boat Color      Reserve Date');
			utl_file.put_line(f, '=======================================================================================');
			FOR boat IN (select bid, bname, color, day from boats natural join reserves where sid=sailor.sid) LOOP
				IF SQL%ROWCOUNT = 0 THEN
					utl_file.put_line(f, '');
					utl_file.put_line(f, LPAD('NO RESERVES', 43));
					utl_file.put_line(f, '');
				ELSE
					utl_file.put_line(f, RPAD(boat.bid, 11) || RPAD(boat.bname, 15) || RPAD(boat.color, 16) || RPAD(boat.day, 12));
				END IF;
			END LOOP;
			utl_file.FCLOSE(f);
			p_resultado := 1;
		END LOOP;
	EXCEPTION
		WHEN OTHERS THEN
			p_resultado := 0;
	END sailors_reserves_file;
END sailors_package;
/