CREATE TABLE EXCEL_FILE (
  SEQNO               NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  FILE_NAME           VARCHAR2(255) NOT NULL,           
  MIME_TYPE           VARCHAR2(100),                    
  CHARACTER_SET       VARCHAR2(50),                     
  FILE_CONTENT        BLOB,                             
  UPLOADED_AT         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
  UPLOADED_BY         VARCHAR2(100)                     
);
SQL
복사

엑셀 파일을 BLOB의 형태로 업로드해서 관리하는 테이블입니다.

FILE_NAME, MIME_TYPE, CHARACTER_SET, FILE_CONTENT 컬럼이 필수입니다.

엑셀 파일 내용을 담는 테이블 (EXCEL_UPLOAD)

CREATE TABLE EXCEL_UPLOAD (
  SEQNO               NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  EXCEL_SEQNO         NUMBER, 
  COL1                VARCHAR2(4000),           
  COL2                VARCHAR2(4000),           
  COL3                VARCHAR2(4000),           
  COL4                VARCHAR2(4000),           
  COL5                VARCHAR2(4000),           
  COL6                VARCHAR2(4000),           
  COL7                VARCHAR2(4000),           
  COL8                VARCHAR2(4000),           
  COL9                VARCHAR2(4000),           
  COL10               VARCHAR2(4000),           
  COL11               VARCHAR2(4000),           
  COL12               VARCHAR2(4000),           
  COL13               VARCHAR2(4000),           
  COL14               VARCHAR2(4000),           
  COL15               VARCHAR2(4000),           
  COL16               VARCHAR2(4000),           
  COL17               VARCHAR2(4000),           
  COL18               VARCHAR2(4000),           
  COL19               VARCHAR2(4000),           
  COL20               VARCHAR2(4000),           
  CREATED_AT          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
  CREATED_BY          VARCHAR2(100),                     
  UPDATED_AT          TIMESTAMP,  
  UPDATED_BY          VARCHAR2(100),
  
  CONSTRAINT EXCEL_UPLOAD_FK
    FOREIGN KEY (EXCEL_SEQNO)
    REFERENCES EXCEL_FILE(SEQNO)
    ON DELETE CASCADE         
);
SQL
복사

엑셀 파일의 컬럼수가 20개까지라고 가정하고 만드는 테이블입니다.

더 많은 컬럼이 필요한 경우, 상황에 맞게 늘려줄 수 있습니다.

엑셀 파일 테이블의 PK를 이 테이블에서 FK로 설정합니다.

2. 페이지 생성

페이지는 3개를 생성합니다.

업로드된 엑셀 파일 목록을 보는 페이지

엑셀 파일을 업로드하는 폼 페이지

엑셀 파일 내용을 조회하는 페이지

엑셀 파일 목록 페이지

엑셀 파일을 업로드하는 버튼이 있고, EXCEL_FILE 테이블의 데이터를 보여주는 테이블입니다.

페이지는 REGION TYPE 을 INTERACTIVE GRID 로 설정합니다.

버튼을 하나 추가하고 엑셀 파일 업로드라고 라벨을 입력합니다.

버튼은 엑셀 파일 업로드 폼으로 이동하도록 설정합니다.

엑셀 파일 업로드 폼

EXCEL_FILE 테이블로 폼을 만듭니다.

FILE_CONTENT를 TYPE을 File Upload 로 설정합니다. 그러면 아이텐에 Storage라고 컬럼을 지정할 수 있도록 표시됩니다. 여기에 EXCEL_FILE의 컬럼을 입력해줍니다.

MIME Type Column: MIME_TYPE

Filename Column: FILE_NAME

Character Set Column: CHARACTER_SET

BLOB Last Updated Column: UPLOADED_AT

File Types: .xls, .xlsx ⇒ 두 종류의 엑셀 파일만 업로드하도록 제한합니다.

Maximum File Size ⇒ 파일 사이즈를 제한합니다.

CHARACTER_SET 는 파일을 분석해서 가져올 수가 없습니다. Popup LOV로 선택할 수 있도록 만들어줍니다.

엑셀 파일 내용 페이지

엑셀 파일의 내용을 조회하는 페이지입니다. EXCEL_SEQNO를 받아서 EXCEL_UPLOAD 테이블에서 필터링한 결과를 보여줍니다.

엑셀 파일 목록 페이지에서 EXCEL_SEQNO를 받을 수 있도록 페이지 아이템을 추가해줍니다.

3. 패키지 생성

페이지 준비가 되었으니 엑셀을 업로드 했을 때 엑셀의 데이터를 EXCEL_UPLOAD에 넣어줄 수 있도록 함수와 프로시저를 담은 패키지를 만들어줍니다.

create or replace PACKAGE "AUTOOFFICE_EXCEL_UPLOAD" AS

FUNCTION GET_FILE_BLOB (P_SEQNO NUMBER) RETURN BLOB;
FUNCTION GET_FILE_NAME (P_SEQNO NUMBER) RETURN VARCHAR2;
FUNCTION GET_FILE_TYPE (P_SEQNO NUMBER) RETURN VARCHAR2;

PROCEDURE INSERT_DATA (P_SEQNO IN NUMBER, P_APPUSER IN VARCHAR2);

END "AUTOOFFICE_EXCEL_UPLOAD";
/
SQL
복사

create or replace PACKAGE BODY "AUTOOFFICE_EXCEL_UPLOAD" AS 

FUNCTION GET_FILE_BLOB (P_SEQNO NUMBER) 
RETURN BLOB IS L_BLOB BLOB;

    BEGIN
    SELECT
        FILE_CONTENT INTO L_BLOB
    FROM
        EXCEL_FILE
    WHERE
        SEQNO = P_SEQNO;

    RETURN L_BLOB;

    END;

FUNCTION GET_FILE_NAME (P_SEQNO NUMBER) 
RETURN VARCHAR2 IS L_FILE_NAME VARCHAR2 (200);

    BEGIN
    SELECT
        FILE_NAME INTO L_FILE_NAME
    FROM
        EXCEL_FILE
    WHERE
        SEQNO = P_SEQNO;

    RETURN L_FILE_NAME;

    END;

FUNCTION GET_FILE_TYPE (P_SEQNO NUMBER) 
RETURN VARCHAR2 IS L_FILE_TYPE VARCHAR2 (100);

    BEGIN
    SELECT
        APEX_DATA_PARSER.GET_FILE_TYPE(FILE_NAME) INTO L_FILE_TYPE
    FROM
        EXCEL_FILE
    WHERE
        SEQNO = P_SEQNO;

    RETURN L_FILE_TYPE;

    END;

PROCEDURE INSERT_DATA (
        P_SEQNO    IN NUMBER,
        P_APPUSER  IN VARCHAR2
    ) IS

    CURSOR C_EXCEL IS
    SELECT
        COL001,
        COL002,
        COL003,
        COL004,
        COL005,
        COL006,
        COL007,
        COL008,
        COL009,
        COL010,
        COL011,
        COL012,
        COL013,
        COL014,
        COL015, 
        COL016,
        COL017,
        COL018,
        COL019,
        COL020
    FROM
         TABLE ( APEX_DATA_PARSER.PARSE(
            P_CONTENT => AUTOOFFICE_EXCEL_UPLOAD.GET_FILE_BLOB(P_SEQNO), 
            P_FILE_NAME => AUTOOFFICE_EXCEL_UPLOAD.GET_FILE_NAME(P_SEQNO),
            P_SKIP_ROWS => 0, 
            P_FILE_TYPE => AUTOOFFICE_EXCEL_UPLOAD.GET_FILE_TYPE(P_SEQNO)
            ) 
        );

    BEGIN

        FOR R_EXCEL IN C_EXCEL LOOP

            INSERT INTO EXCEL_UPLOAD (
                EXCEL_SEQNO,
                COL1,
                COL2,
                COL3,
                COL4,
                COL5,
                COL6,
                COL7,
                COL8,
                COL9,
                COL10,
                COL11,
                COL12,
                COL13,
                COL14,
                COL15,
                COL16,
                COL17,
                COL18,
                COL19,
                COL20,
                CREATED_AT,
                CREATED_BY
            ) VALUES (
                P_SEQNO,
                R_EXCEL.COL001,
                R_EXCEL.COL002, 
                R_EXCEL.COL003,
                R_EXCEL.COL004,
                R_EXCEL.COL005,  
                R_EXCEL.COL006,
                R_EXCEL.COL007,
                R_EXCEL.COL008,
                R_EXCEL.COL009,
                R_EXCEL.COL010,
                R_EXCEL.COL011,
                R_EXCEL.COL012,
                R_EXCEL.COL013,
                R_EXCEL.COL014,
                R_EXCEL.COL015,
                R_EXCEL.COL016,
                R_EXCEL.COL017,
                R_EXCEL.COL018,
                R_EXCEL.COL019,
                R_EXCEL.COL020,
                SYSTIMESTAMP,
                P_APPUSER
            );        

        END LOOP;

        COMMIT;

        EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20123, 'EXCEL_UPLOAD INSERT ERROR =='                                                    
                                            || ' EXCEL_SEQNO:'
                                            || P_SEQNO
                                            || SQLERRM);

    END;

END "AUTOOFFICE_EXCEL_UPLOAD";
/
SQL
복사

INSERT_DATA 프로시저를 중심으로 설명합니다.

INSERT_DATA 프로시저는 엑셀 파일 업로드 폼에서 폼 프로세스를 완료한 뒤에 연속적으로 실행되는 프로세스로 추가합니다.

프로세스의 Type은 Invoke API를 선택하고, 패키지를 AUTOOFFICE_EXCEL_UPLOAD를 선택한 뒤, 프로시저를 INSERT_DATA를 선택합니다.

이렇게 선택하면 파라미터 값을 지정해주어야 하는데, p_seqno 에는 P21_SEQNO (여기서는 21번 페이지라서 P21입니다.), p_appuser에는 :APP_USER 를 입력합니다.

이제 폼으로 엑셀을 업로드하면 INSERT_DATA가 실행되면서 EXCEL_UPLOAD 테이블에 해당 엑셀의 데이터를 INSERT 해줍니다. 코드 레벨로 살펴보실까요?

PROCEDURE INSERT_DATA (
        P_SEQNO    IN NUMBER,
        P_APPUSER  IN VARCHAR2
    ) IS

    CURSOR C_EXCEL IS
    SELECT
        COL001,
        COL002,
        COL003,
        COL004,
        COL005,
        COL006,
        COL007,
        COL008,
        COL009,
        COL010,
        COL011,
        COL012,
        COL013,
        COL014,
        COL015, 
        COL016,
        COL017,
        COL018,
        COL019,
        COL020
    FROM
         TABLE ( APEX_DATA_PARSER.PARSE(
            P_CONTENT => AUTOOFFICE_EXCEL_UPLOAD.GET_FILE_BLOB(P_SEQNO), 
            P_FILE_NAME => AUTOOFFICE_EXCEL_UPLOAD.GET_FILE_NAME(P_SEQNO),
            P_SKIP_ROWS => 0, 
            P_FILE_TYPE => AUTOOFFICE_EXCEL_UPLOAD.GET_FILE_TYPE(P_SEQNO)
            ) 
        );
SQL
복사

C_EXCEL이라는 커서를 만드는데, APEX_DATA_PARSER.PARSE 라는 내장함수를 사용해서 만든 테이블에서 SELECT를 합니다.

APEX_DATA_PARSER.PARSE는 다음과 같은 파라미터를 받습니다.

p_content: 엑셀 파일의 BLOB 입니다.

AUTOOFFICE_EXCEL_UPLOAD.GET_FILE_BLOB(P_SEQNO) 으로 BLOB을 가져옵니다. 이 함수는 앞에 정의가 되어 있는데, p_seqno로 blob을 가져오는 함수입니다.

p_file_name: AUTOOFFICE_EXCEL_UPLOAD.GET_FILE_NAME(P_SEQNO) 으로 파일이름을 가져옵니다. (생략해도 동작하기는 합니다.)

p_skip_rows: 몇 행을 SKIP할지 결정합니다. 0으로 설정하면 맨 첫 행의 라벨도 가져옵니다. 비정형의 엑셀 파일을 다양하게 활용할 때에는 0으로 하면 EXCEL_UPLOAD에서 라벨까지 볼 수 있어서 편리합니다.

p_file_type: AUTOOFFICE_EXCEL_UPLOAD.GET_FILE_TYPE(P_SEQNO)으로 파일 확장자를 가져옵니다. (좀 더 정확하게는 APEX에서 사용하는 형태로 변환한 값을 가져옵니다.)

더 자세히 보시려면, 오라클 웹사이트를 방문하세요.

API Reference

The PARSE function enables you to parse XML, XLSX, CSV or JSON files and returns a generic table of the following structure:

https://docs.oracle.com/en/database/oracle/application-express/21.1/aeapi/PARSE-Function.html

그 다음으로 커서를 순회하면서 각 행을 EXCEL_UPLOAD에 INSERT 합니다.

이 때 EXCEL_SEQNO에 p_seqno 를 넣어주어서 FK역할을 하도록 만들어줍니다.

4. 페이지 링크 연결

이제 데이터는 정상적으로 들어가기 때문에 페이지 링크만 연결을 해주면 됩니다.

엑셀 파일 목록 페이지 - 엑셀 파일 내용 페이지

엑셀 파일 목록 페이지에서 SEQNO에는 엑셀 파일 내용 페이지로 연결을 해주면서 SEQNO를 EXCEL_SEQNO 아이템으로 넘겨줍니다.

엑셀 파일 내용 페이지에서는 REGION의 소스에 EXCEL_SEQNO로 WHERE 문을 넣어 필터링을 해줍니다.

연결이 정상적으로 되면, 엑셀 파일 목록 페이지에서 엑셀 파일 내용 페이지로 넘어가면서 해당 데이터를 표시해주게 됩니다.

엑셀 파일 목록 페이지 - 엑셀 파일 폼

FILE_NAME 아이템에는 엑셀 파일 폼 페이지로 연결을 해주면서 SEQNO를 넘겨줍니다.

이렇게 하면 폼 페이지로 들어가 업로드했던 파일을 다운로드 할 수도 있습니다.

이렇게 해서 엑셀 파일을 업로드하고 내용을 APEX에서 확인하는 페이지를 생성하였습니다.

실무에서는 여기서 그치지 않고, 파싱을 좀 더 정교하게 해서 필요한 데이터로 만든 후에 목적하는 테이블로 INSERT 합니다. 그래서 패키지에 INSERT_DATA 역할을 하는 프로시저가 엑셀 파일의 템플릿에 따라 여러개 만들어지곤 합니다.

이 포스트를 기초로 하면 응용도 가능할 것입니다.