20230305 - 파일 업로드 to 테이블 BLOB 컬럼

테이블 생성, Quick SQL에서 file 타입으로 입력하면 자동으로 blob, _filename, _mimetype, _charset, _lastupd 컬럼이 생성됨

create table x230303_file (

    fid                            number generated by default on null as identity

                                   constraint x230303_file_fid_pk primary key,

    upload                         blob,

    upload_filename                varchar2(512 char),

    upload_mimetype                varchar2(512 char),

    upload_charset                 varchar2(512 char),

    upload_lastupd                 date

)

;

-- load data

 

-- Generated by Quick SQL Friday March 03, 2023  14:51:02

 

/*

x230303_file

  fid /pk

  upload file

# settings = { semantics: "CHAR", language: "EN", APEX: true }

*/

폼 페이지, 폼 리전 생성

P172_UPLOAD 항목 설정

Identification - Type : File Browse...

Setting - Display As : Block Dropzone

Storage Type : BLOB column specified in Item Source attribute

MIME Type Column : UPLOAD_MIMETYPE

Filename Column : UPLOAD_FILENAME

Character Set Column : UPLOAD_CHARSET

BLOB Last Updated Column : UPLOAD_LASTUPD

P172_UPLOAD Submit 될 때 체크할 Validation

Identification - Name : P172_UPLOAD_VAL

Validation - Type : Item is NOT NULL

Item : P172_UPLOAD

Error - Error Message : 업로드할 파일을 선택하십시오.

Display Location : Inline with Field and in Notification

Associated Item : P172_UPLOAD

Server-side Condition - When Button Pressed : CREATE

Type : Item is NULL

Item : P172_FID

파일 업로드 프로세싱과 Close Dialog

참고

Gallery - Sample File Upload and Download

https://forums.oracle.com/ords/apexds/post/how-to-upload-a-clob-into-a-table-9792

https://doyensys.com/blogs/25447-2/

https://arrayofpointers.wordpress.com/2015/01/11/upload-and-view-a-file-imagepdfdoc-in-a-list-of-oracle-apex-page/

http://jsfiddle.net/dirtyd77/LzLcZ/144/

• Add an unconditional page rendering page process
• Choose "Data Manipulation" and click "Next"
• Select the default, "Automated Row Fetch", then click "Next"
• Give the process a name, "Fetch Row from EBA_DEMO_FILES" (for example), change the processing point to "On load - After Header" and click "Next"
• Enter the table name of the application's "files" table, the APEX page item id of the page item that references the file being uploaded, and the column name of the blob column in the afore assigned application "files" table and click "Next"
• Optionally add a success and/or error message, click "Create Process"
• Create an "HTML" region (for holding your file upload form element)
• Create a hidden page item that will hold the primary key id value of the row that holds the file (e.g, P12_ID)*
• Now create a "File Browse" page item in the same region as the above page items, ensure the "Storage Type" value is set to "BLOB column specified in Item Source attribute"
• Define the application tables' column names for holding, MIME Type, Filename, Character Set, and BLOB Last Updated, and then click, "Next"
• Then click, "Create Item"*
• Create the other hidden page items that will store the filename, mimetype, and character set meta data*
• If your application's files table has a description (or comments) column, add a textarea page item for capturing this information*
• Now create an unconditional page processing process
• Choose "Data Manipulation" and click "Next"
• Give the process a name, "Process Row from EBA_DEMO_FILES" (for example), change the processing point to "On Submit - After Computations and Validations" and click "Next"
• Enter the table name of the application's "files" table, the APEX page item id of the page item that references the file being uploaded, and the column name of the blob column in the application's "files" table and then click "Create Item"
• Create the Cancel, Delete, Apply Changes, and Create buttons (see implementation of buttons on this page)
• Now create a conditional page processing process
• Choose "Session State" and click "Next"
• Select "Clear Cache for all Items on Pages (PageID,PageID,PageID)" and click "Next"
• Give it a name of "reset page", leave the other defaulted values, click "Next"
• Enter the current page id and then click "Next"
• Optionally add Success and/or Error Messages and then click "Next"
• Change the "When Button Pressed" condition to use the "Delete" button, leave all other options set to their default values and click "Create Process"

* Be sure to set the identified page item's "Source Type" as "Database Column" and assign their "Source value or expression" as the corresponding column name in the application's "files" table.