CREATE USER ORAAPPUSER IDENTIFIED BY ORA_APP_2022
   DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

GRANT CREATE SESSION, CREATE VIEW, CREATE SEQUENCE,
   CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER,
   CREATE TYPE, CREATE MATERIALIZED VIEW TO ORAAPPUSER;

CREATE TABLE FILE_MASTER (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  FILE_NAME VARCHAR2(500),
  FILE_CREATION_TS TIMESTAMP,
  RECORD_CREATION_TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  RECORD_CREATED_BY VARCHAR2(400),
  PRIMARY KEY(id)
);

CREATE OR REPLACE PROCEDURE files_get_all
AS
  c1 SYS_REFCURSOR;  
BEGIN
  OPEN c1 FOR
  SELECT * FROM FILE_MASTER;
  DBMS_SQL.RETURN_RESULT(c1);
END;

CREATE OR REPLACE PROCEDURE insert_file_master(
    v_filename FILE_MASTER.FILE_NAME%TYPE,
    v_file_creation FILE_MASTER.FILE_CREATION_TS%TYPE,
    v_record_by FILE_MASTER.RECORD_CREATED_BY%TYPE,
    v_file_id OUT FILE_MASTER.id%TYPE)
AS
BEGIN
  INSERT INTO FILE_MASTER (FILE_NAME, FILE_CREATION_TS, RECORD_CREATED_BY)
  VALUES(v_filename, v_file_creation, v_record_by)
  RETURNING id INTO v_file_id;
END;

CREATE TABLE FILE_CONTENT (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  FILE_ID NUMERIC(10),
  LINE_NO NUMERIC(10),
  LINE_CONTENT VARCHAR2(400),
  RECORD_CREATION_TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  RECORD_CREATED_BY VARCHAR2(400),
  CONSTRAINT file_content_pk PRIMARY KEY(id),
  CONSTRAINT fk_file_master
    FOREIGN KEY (FILE_ID)
    REFERENCES FILE_MASTER(id)
);

CREATE OR REPLACE PROCEDURE get_file_content_by_file_id(
    v_file_id FILE_CONTENT.FILE_ID%TYPE)
AS
  c1 SYS_REFCURSOR;  
BEGIN
  OPEN c1 FOR
  SELECT * FROM FILE_CONTENT
  WHERE FILE_ID = v_file_id;
  DBMS_SQL.RETURN_RESULT(c1);
END;

CREATE OR REPLACE PROCEDURE insert_file_content(
    v_file_id FILE_CONTENT.FILE_ID%TYPE,
    v_line_no FILE_CONTENT.LINE_NO%TYPE,
    v_line_content FILE_CONTENT.LINE_CONTENT%TYPE,
    v_record_by FILE_CONTENT.RECORD_CREATED_BY%TYPE)
AS
BEGIN
  INSERT INTO FILE_CONTENT (FILE_ID, LINE_NO, LINE_CONTENT, RECORD_CREATED_BY)
  VALUES(v_file_id, v_line_no, v_line_content, v_record_by);
END;