Storage
Package for storing and processing large binary objects
example
sql
DECLARE
id app_storage.id%TYPE;
b app_storage.content%TYPE;
n app_storage.file_name%TYPE;
s PLS_INTEGER;
e VARCHAR2(100);
m VARCHAR2(100);
BEGIN
-- local
b := utl_raw.cast_to_raw('Hello World!');
n := 'hello.txt';
pck_api_storage.upload(b, n, id);
dbms_output.put_line(' uploaded file ID: ' || id);
COMMIT;
b := EMPTY_BLOB();
pck_api_storage.download(id, b, n, s, e, m);
dbms_output.put_line(' downloaded file name: ' || n);
dbms_output.put_line(' downloaded file size: ' || s);
dbms_output.put_line(' downloaded file extension: ' || e);
dbms_output.put_line(' downloaded file mime type: ' || m);
dbms_output.put_line(' -- ');
dbms_output.put_line(' downloaded file actual size: ' || dbms_lob.getlength(b));
-- s3
-- pck_api_settings.write('APP_STORAGE_S3_URI', 'Storage URI for S3 files', 'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket/o/');
pck_api_settings.write('APP_STORAGE_S3_URI', 'Storage URI for S3 files', 'https://objectstorage.eu-stockholm-1.oraclecloud.com/n/axuctz2iko0g/b/odbvue-obj/o/');
pck_api_storage.upload(b, n, id, TRUE);
dbms_output.put_line(' uploaded file ID to S3: ' || id);
COMMIT;
b := EMPTY_BLOB();
pck_api_storage.download(id, b, n, s, e, m);
dbms_output.put_line(' downloaded file name from S3: ' || n);
dbms_output.put_line(' downloaded file size from S3: ' || s);
dbms_output.put_line(' downloaded file extension from S3: ' || e);
dbms_output.put_line(' downloaded file mime type from S3: ' || m);
dbms_output.put_line(' -- ');
dbms_output.put_line(' downloaded file actual size from S3: ' || dbms_lob.getlength(b));
END;
/specification
sql
CREATE OR REPLACE PACKAGE odbvue.pck_api_storage -- Package for storing and processing large binary objects
AS
PROCEDURE upload ( -- Procedure stores binary file
p_file app_storage.content%TYPE, -- File content
p_file_name app_storage.file_name%TYPE, -- File name
r_id OUT app_storage.id%TYPE, -- File ID
p_s3 BOOLEAN DEFAULT FALSE -- Upload to S3 flag
);
PROCEDURE upload ( -- Procedure stores binary file
p_file app_storage.content%TYPE, -- File content
p_file_name app_storage.file_name%TYPE, -- File name
p_s3 BOOLEAN DEFAULT FALSE -- Upload to S3 flag
);
PROCEDURE download ( -- Procedure retrieves binary file
p_id app_storage.id%TYPE, -- File ID
r_file OUT NOCOPY app_storage.content%TYPE, -- File content
r_file_name OUT app_storage.file_name%TYPE, -- File name
r_file_size OUT app_storage.file_size%TYPE, -- File size
r_file_ext OUT app_storage.file_ext%TYPE, -- File ext
r_mime_type OUT app_storage.mime_type%TYPE -- Mime type
);
PROCEDURE download ( -- Procedure retrieves binary file
p_id app_storage.id%TYPE, -- File ID
r_file OUT NOCOPY app_storage.content%TYPE, -- File content
r_file_name OUT app_storage.file_name%TYPE -- File name
);
PROCEDURE DELETE ( -- Procedure deletes binary file
p_id app_storage.id%TYPE -- File ID
);
PROCEDURE s3 ( -- Procedure migrates file to S3 storage
p_operation CHAR, -- Operation (U - upload from local to S3, D - download from S3 to local)
p_id app_storage.id%TYPE DEFAULT NULL, -- File ID
p_created_from TIMESTAMP DEFAULT NULL, -- Created from
p_created_to TIMESTAMP DEFAULT NULL, -- Created to
p_batch_size NUMBER DEFAULT 100 -- Batch size
);
END;
/
-- sqlcl_snapshot {"hash":"08ea336315aa0136745bebb3120095bb8226c250","type":"PACKAGE_SPEC","name":"PCK_API_STORAGE","schemaName":"ODBVUE","sxml":""}implementation
sql
CREATE OR REPLACE PACKAGE BODY odbvue.pck_api_storage AS
FUNCTION force_trailing_slash (
p_uri IN VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
RETURN
CASE
WHEN p_uri IS NULL THEN
'/'
WHEN substr(
trim(p_uri),
-1
) = '/' THEN
TRIM(p_uri)
ELSE
TRIM(p_uri)
|| '/'
END;
END;
FUNCTION to_s3 (
p_id app_storage.id%TYPE,
p_content app_storage.content%TYPE
) RETURN app_storage.s3_uri%TYPE AS
v_s3_uri app_storage.s3_uri%TYPE;
BEGIN
BEGIN
v_s3_uri := pck_api_settings.read('APP_STORAGE_S3_URI');
v_s3_uri := force_trailing_slash(v_s3_uri)
|| p_id;
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20001, 'S3 URI setting APP_STORAGE_S3_URI not found');
END;
dbms_cloud.put_object('OCI$RESOURCE_PRINCIPAL', v_s3_uri, p_content);
RETURN v_s3_uri;
END;
PROCEDURE from_s3 (
p_s3_uri app_storage.s3_uri%TYPE,
r_content IN OUT NOCOPY app_storage.content%TYPE
) AS
BEGIN
IF p_s3_uri IS NULL THEN
RETURN;
END IF;
r_content := dbms_cloud.get_object(
credential_name => 'OCI$RESOURCE_PRINCIPAL',
object_uri => p_s3_uri
);
END;
PROCEDURE rm_s3 (
p_s3_uri app_storage.s3_uri%TYPE
) AS
BEGIN
IF p_s3_uri IS NULL THEN
RETURN;
END IF;
dbms_cloud.delete_object(
credential_name => 'OCI$RESOURCE_PRINCIPAL',
object_uri => p_s3_uri
);
END;
PROCEDURE upload (
p_file app_storage.content%TYPE,
p_file_name app_storage.file_name%TYPE,
r_id OUT app_storage.id%TYPE,
p_s3 BOOLEAN DEFAULT FALSE
) AS
v_file_ext app_storage.file_ext%TYPE := substr(p_file_name,
nullif(instr(p_file_name, '.', -1) + 1,
1));
v_mime_type app_storage.mime_type%TYPE := pck_api_http.mime_type(v_file_ext);
v_id app_storage.id%TYPE := lower(sys_guid());
v_s3_uri app_storage.s3_uri%TYPE;
BEGIN
IF p_s3 THEN
v_s3_uri := to_s3(v_id, p_file);
END IF;
INSERT INTO app_storage (
id,
file_name,
file_size,
file_ext,
mime_type,
content,
s3_uri,
s3_created
) VALUES ( v_id,
p_file_name,
dbms_lob.getlength(p_file),
v_file_ext,
v_mime_type,
CASE
WHEN v_s3_uri IS NOT NULL THEN
empty_blob()
ELSE
p_file
END,
CASE
WHEN v_s3_uri IS NOT NULL THEN
v_s3_uri
ELSE
NULL
END,
CASE
WHEN v_s3_uri IS NOT NULL THEN
systimestamp
ELSE
NULL
END
);
r_id := v_id;
END;
PROCEDURE upload (
p_file app_storage.content%TYPE,
p_file_name app_storage.file_name%TYPE,
p_s3 BOOLEAN DEFAULT FALSE
) AS
v_id app_storage.id%TYPE;
BEGIN
upload(p_file, p_file_name, v_id, p_s3);
END;
PROCEDURE download (
p_id app_storage.id%TYPE,
r_file OUT NOCOPY app_storage.content%TYPE,
r_file_name OUT app_storage.file_name%TYPE,
r_file_size OUT app_storage.file_size%TYPE,
r_file_ext OUT app_storage.file_ext%TYPE,
r_mime_type OUT app_storage.mime_type%TYPE
) AS
v_s3_uri app_storage.s3_uri%TYPE;
BEGIN
SELECT
content,
file_name,
file_size,
file_ext,
mime_type,
s3_uri
INTO
r_file,
r_file_name,
r_file_size,
r_file_ext,
r_mime_type,
v_s3_uri
FROM
app_storage
WHERE
id = p_id;
IF v_s3_uri IS NOT NULL THEN
from_s3(v_s3_uri, r_file);
END IF;
END;
PROCEDURE download (
p_id app_storage.id%TYPE,
r_file OUT NOCOPY app_storage.content%TYPE,
r_file_name OUT app_storage.file_name%TYPE
) AS
v_s3_uri app_storage.s3_uri%TYPE;
v_file_size app_storage.file_size%TYPE;
v_file_ext app_storage.file_ext%TYPE;
v_mime_type app_storage.mime_type%TYPE;
BEGIN
download(p_id, r_file, r_file_name, v_file_size, v_file_ext,
v_mime_type);
END;
PROCEDURE DELETE (
p_id app_storage.id%TYPE
) AS
v_s3_uri app_storage.s3_uri%TYPE;
BEGIN
SELECT
s3_uri
INTO v_s3_uri
FROM
app_storage
WHERE
id = p_id;
IF v_s3_uri IS NOT NULL THEN
rm_s3(v_s3_uri);
END IF;
DELETE FROM app_storage
WHERE
id = p_id;
END;
PROCEDURE s3 (
p_operation CHAR,
p_id app_storage.id%TYPE DEFAULT NULL,
p_created_from TIMESTAMP DEFAULT NULL,
p_created_to TIMESTAMP DEFAULT NULL,
p_batch_size NUMBER DEFAULT 100
) AS
PRAGMA autonomous_transaction;
v_file app_storage.content%TYPE;
v_s3_uri app_storage.s3_uri%TYPE;
v_count_up PLS_INTEGER := 0;
v_count_dn PLS_INTEGER := 0;
BEGIN
IF p_operation = 'U' THEN
FOR r IN (
SELECT
id,
content
FROM
app_storage
WHERE
( p_id IS NULL
OR id = p_id )
AND ( p_created_from IS NULL
OR created >= p_created_from )
AND ( p_created_to IS NULL
OR created <= p_created_to )
AND s3_uri IS NULL
FETCH NEXT p_batch_size ROWS ONLY
) LOOP
v_s3_uri := to_s3(r.id, r.content);
UPDATE app_storage
SET
s3_uri = v_s3_uri,
s3_created = systimestamp,
content = empty_blob()
WHERE
id = r.id;
v_count_up := v_count_up + 1;
END LOOP;
COMMIT;
END IF;
IF p_operation = 'D' THEN
FOR r IN (
SELECT
id,
s3_uri
FROM
app_storage
WHERE
( p_id IS NULL
OR id = p_id )
AND ( p_created_from IS NULL
OR s3_created >= p_created_from )
AND ( p_created_to IS NULL
OR s3_created <= p_created_to )
AND s3_uri IS NOT NULL
FETCH NEXT p_batch_size ROWS ONLY
) LOOP
v_file := NULL;
from_s3(r.s3_uri, v_file);
UPDATE app_storage
SET
content = v_file,
s3_uri = NULL,
s3_created = NULL
WHERE
id = r.id;
v_count_dn := v_count_dn + 1;
END LOOP;
COMMIT;
END IF;
dbms_output.put_line('S3 migration completed. '
|| 'Uploaded: '
|| v_count_up
|| ' files. '
|| 'Downloaded: '
|| v_count_dn || ' files.');
END;
END;
/
-- sqlcl_snapshot {"hash":"4341cd9779a8d39068f31a81310dc10991410831","type":"PACKAGE_BODY","name":"PCK_API_STORAGE","schemaName":"ODBVUE","sxml":""}Dependencies:
| Referenced type | Referenced name |
|---|---|
| TABLE | APP_STORAGE |
DELETE
Procedure deletes binary file
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_ID | IN | CHAR | File ID |
DOWNLOAD
Procedure retrieves binary file
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_ID | IN | CHAR | File ID | |
| R_FILE | OUT | BLOB | File content | |
| R_FILE_NAME | OUT | VARCHAR2 | File name | |
| R_FILE_SIZE | OUT | NUMBER | File size | |
| R_FILE_EXT | OUT | VARCHAR2 | File ext | |
| R_MIME_TYPE | OUT | VARCHAR2 | Mime type |
DOWNLOAD
Procedure retrieves binary file
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_ID | IN | CHAR | File ID | |
| R_FILE | OUT | BLOB | File content | |
| R_FILE_NAME | OUT | VARCHAR2 | File name |
S3
Procedure migrates file to S3 storage
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_OPERATION | IN | CHAR | Operation (U - upload from local to S3, D - download from S3 to local) | |
| P_ID | IN | CHAR | File ID | |
| P_CREATED_FROM | IN | TIMESTAMP | NULL | Created from |
| P_CREATED_TO | IN | TIMESTAMP | NULL | Created to |
| P_BATCH_SIZE | IN | NUMBER | 100 | Batch size |
UPLOAD
Procedure stores binary file
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_FILE | IN | BLOB | File content | |
| P_FILE_NAME | IN | VARCHAR2 | File name | |
| R_ID | OUT | CHAR | File ID | |
| P_S3 | IN | PL/SQL BOOLEAN | FALSE | Upload to S3 flag |
UPLOAD
Procedure stores binary file
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_FILE | IN | BLOB | File content | |
| P_FILE_NAME | IN | VARCHAR2 | File name | |
| P_S3 | IN | PL/SQL BOOLEAN | FALSE | Upload to S3 flag |