CREATE OR REPLACE
PACKAGE file_api AS

  /* TODO enter package declarations (types, exceptions, methods etc) here */
  /* small subset of possible functionality for now, no exceptions etc) */

  /* file_upload function, returns id of newly uploaded file */
  FUNCTION file_upload (
    p_name IN VARCHAR2,            /* IN name of the file e.g. myResume.pdf */
    p_upload_time IN DATE,         /* IN upload time of the file */
    p_content_type IN VARCHAR2,    /* IN content type e.g. application/pdf */
    p_data IN BLOB,                /* IN binary data of file */
    p_data_size IN INTEGER)        /* IN size of file in bytes */
    RETURN INTEGER;                /* IN returns id of the uploaded file */

  /* file_download function takes in ID and sets output parameters */
  PROCEDURE file_download (
    p_id IN INTEGER,               /* IN id of the file to download */
    p_name OUT VARCHAR2,            /* OUT name of the file e.g. myResume.pdf */
    p_upload_time OUT DATE,         /* OUT upload time of file */
    p_content_type OUT VARCHAR2,    /* OUT content type of file e.g. application/pdf */
    p_data OUT BLOB,                /* OUT binary data of file */
    p_data_size OUT INTEGER         /* OUT size of file in bytes */
  );

  /* exceptions... file does not exist exception etc. */
END file_api;

CREATE OR REPLACE PACKAGE BODY file_api AS

  FUNCTION file_upload (
    p_name IN VARCHAR2,
    p_upload_time IN DATE,
    p_content_type IN VARCHAR2,
    p_data IN BLOB,
    p_data_size IN INTEGER)
    RETURN INTEGER
  IS out_id INTEGER;
  BEGIN
  out_id := files_seq.NEXTVAL;
  INSERT INTO files$ f (f.id, f.name, f.upload_time, f.content_type, f.data, f.data_size)
    VALUES (out_id, p_name, p_upload_time, p_content_type, p_data, p_data_size);
  RETURN(out_id);
  END;

  PROCEDURE file_download (
    p_id IN INTEGER,
    p_name OUT VARCHAR2,
    p_upload_time OUT DATE,
    p_content_type OUT VARCHAR2,
    p_data OUT BLOB,
    p_data_size OUT INTEGER)
  IS
  BEGIN
    SELECT f.name, f.upload_time, f.content_type, f.data, f.data_size INTO
      p_name, p_upload_time, p_content_type, p_data, p_data_size FROM
      files$ f WHERE f.id = p_id;
  END;
END file_api;