PL/SQL API
The AME PL/SQL API ame_api_pkg consists out of 8 different ways to deal with images. In this section we give an overview by procedure and function.
-- Logger
g_logger_enabled constant boolean := true; -- set to true to write extra debug output to logger - see https://github.com/OrAMEenSource/Logger
-- SQL> ALTER PACKAGE ame_api21_pkg COMPILE PLSQL_CCFLAGS = 'logger_on:TRUE';
-- When compiled and this global variable is set to true, debug will be written to logger too
-- Call to AME Server
g_ame_url varchar2(200) := null; -- AME Server url
g_api_key varchar2(50) := null; -- AME API Key; only needed when AME Cloud is used (http(s)://www.apexofficeprint.com/api)
g_ame_mode varchar2(15) := null; -- AME Mode can be development or production; when running in development no cloud credits are used but a watermark is printed
g_failover_ame_url varchar2(200) := null; -- AME Server url in case of failure of AME url
g_failover_procedure varchar2(200) := null; -- When the failover url is used, the procedure specified in this variable will be called
g_proxy_override varchar2(300) := null; -- null=proxy defined in the application attributes
g_transfer_timeout number(6) := 1800; -- default of APEX is 180
g_wallet_path varchar2(300) := null; -- null=defined in Manage Instance > Instance Settings
g_wallet_pwd varchar2(300) := null; -- null=defined in Manage Instance > Instance Settings
g_https_host varchar2(300) := null; -- The host name to be matched against the common name (CN) of the remote server's certificate for an HTTPS request.
g_logging clob := ''; -- ability to add your own logging: e.g. "request_id":"123", "request_app":"APEX", "request_user":"RND"
g_debug varchar2(10) := null; -- set to 'Local' when only the JSON needs to be generated, 'Remote' for remore debug
g_debug_json clob := null; -- when run in debug mode, this global variable is filled with the JSON that is being send to the AME Server
g_debug_procedure varchar2(4000):= null; -- when debug in APEX is turned on, next to the normal APEX debug, this procedure will be called
g_cloud_provider varchar2(30) := null; -- dropbox, gdrive, onedrive, aws_s3, (s)ftp
g_cloud_location varchar2(300) := null; -- directory in dropbox, gdrive, onedrive, aws_s3 (with bucket), (s)ftp
g_cloud_access_token varchar2(500) := null; -- access token or credentials for dropbox, gdrive, onedrive, aws_s3, (s)ftp (needs json)
g_manipulator varchar2(100) := 'sharp'; -- This is an optional attribute that will specify which image/video manipulator to use. Available manipulators: "sharp" or "jimp". (default "sharp")
-- Output
g_output_directory varchar2(200) := '.'; -- set output directory on AME Server
-- if . is specified the files are saved in the default directory: outputfiles
g_output_db_directory varchar2(200) := null; -- set output directory on Database Server
g_output_stitch varchar2(1) := null; -- set to Y(es) in case you want to stitch pictures together
g_stitch_orientation varchar2(200) := null; -- the orientation on how the images should be stitched, can be horizontal or vertical.
g_stitch_alignment varchar2(200) := null; -- defines how an image should be stitched, when the width or height of the to stitched image is smaller than the output image
-- Available alignment: center (=default), top/left, bottom/right
g_stitch_background_color varchar2(200) := null; -- background color of the image used in background
-- any color such as black (=default), grey, red, green etc can be given as input.
g_stitch_mime_type varchar2(200) := null; -- mime type of output image e.g. image/jpeg
g_stitch_filename varchar2(200) := null; -- output file name of the stitched image
-- HELPER procedures and functions
-- check to see if the AME Server is running (function returning boolean)
function is_ame_accessible(
p_url in varchar2,
p_proxy_override in varchar2 default null,
p_wallet_path in varchar2 default null,
p_wallet_pwd in varchar2 default null)
return boolean;
-- check to see if the AME Server is running (procedure returning with htp.p and dbms_output)
procedure is_ame_accessible(
p_url in varchar2,
p_proxy_override in varchar2 default null,
p_wallet_path in varchar2 default null,
p_wallet_pwd in varchar2 default null);
-- check the version of the AME Server (function)
function get_ame_server_version(
p_url in varchar2,
p_proxy_override in varchar2 default null,
p_wallet_path in varchar2 default null,
p_wallet_pwd in varchar2 default null)
return varchar2;
-- check the version of the AME Server (procedure)
procedure show_ame_server_version(
p_url in varchar2,
p_proxy_override in varchar2 default null,
p_wallet_path in varchar2 default null,
p_wallet_pwd in varchar2 default null);
-- check the version of the AME Server (function)
function get_ame_plsql_version
return varchar2;
-- check the version of the AME Server (procedure)
procedure show_ame_plsql_version;
-- get supported image input types (function)
function get_ame_input_types(
p_url in varchar2,
p_proxy_override in varchar2 default null,
p_wallet_path in varchar2 default null,
p_wallet_pwd in varchar2 default null)
return varchar2;
-- show supported image input types (procedure)
procedure show_ame_input_types(
p_url in varchar2,
p_proxy_override in varchar2 default null,
p_wallet_path in varchar2 default null,
p_wallet_pwd in varchar2 default null);
-- get supported output types for a given input type (function)
function get_ame_output_type_for_input(
p_url in varchar2,
p_input_type in varchar2 default null,
p_proxy_override in varchar2 default null,
p_wallet_path in varchar2 default null,
p_wallet_pwd in varchar2 default null)
return varchar2;
-- show supported output types for a given input type (procedure)
procedure show_ame_output_type_for_input(
p_url in varchar2,
p_input_type in varchar2 default null,
p_proxy_override in varchar2 default null,
p_wallet_path in varchar2 default null,
p_wallet_pwd in varchar2 default null);
-- MAIN procedures and functions
/**
* @Description: Convert one or more files by using a SQL query, PL/SQL Function returning SQL or JSON
* This returns a blob which includes the output
* AME 20.1 will return one file. In AME 20.2 and higher multiple files will be returned as zip
* @Author: Dimitri Gielis
* @Created: 1-SEP-2019
*
* @Param: p_source_type type of source: c_source_type_sql ('SQL'), c_source_type_plsql_sql ('PLSQL_SQL'), c_source_type_plsql ('PLSQL_JSON'), c_source_type_json ('JSON')
* @Param: p_source Format of query:
* select "file",
* "quality", "width", "max_width", "height", "max_height",
* cursor("horizontal", "vertical") as "flip",
* cursor("value", "resize") as "rotate",
* cursor("font", "font_size", "x", "y", "rotation", "text", "image", "width", "height") as "overlay" ,
* cursor("x", "y", "width", "height") as "crop",
* "output_filename", "output_mime_type"
* from my_table
* @Param: p_binds Binds defined in the source (not necessary for APEX Page Items)
* @Param: p_output_to Where does the blob or file need to be sent to:
* - c_output_browser: the browser will open the file
* - c_output_inline: the output is defined for showing inline in a region
* - c_output_directory: the file is stored on the ame Server in this directory
* - c_output_cloud: a file is sent to the cloud (Dropbox, Amazon S3, Google Drive) using the credentials defined in g_cloud_provider, g_cloud_location and g_cloud_access_token
* @Param: p_debug Turning debugging on will generate the JSON that is sent to the ame Server in a file. The actual request to the ame Server is not done. Following constants can be used:
* - c_debug_remote: store the JSON in your dashboard on https://www.apexofficeprint.com
* - c_debug_local: store the JSON local on your pc
* - c_debug_application_item: depending the Application item ame_debug, Remote (Yes) or Local (Local) or no debugging is done
* @Param: p_ame_url Description: URL where the AME Server is running. For the ame Cloud use c_ame_url
* @Param: p_api_key Description: API Key which can be found when you login at https://www.apexofficeprint.com/ape/
* @Param: p_failover_ame_url: URL where the ame Failover Server is running. For the ame Cloud use c_ame_url_fallback
* @Param: p_failover_procedure: Procedure which is called when the failover URL is being used, so you are warned the main ame server has issues.
* @Param: p_log_procedure: Procedure which can be defined to do your own extra logging.
* @Return: Converted file in blob
*
* @Example:
-- SQL
select
ame_api_pkg.convert_files_b(
p_source_type => 'SQL',
p_source => q'[select apex_web_service.blob2clobbase64(blob_content) as "file",
:P14_MAX_WIDTH as "max_width", :P14_MAX_HEIGHT as "max_height"
--cursor
from ame_images
where id = 1
]',
p_ame_url => 'http://api.apexmediaextension.com',
p_api_key => ''
) as new_image
from ame_images
where id = 1;
-- PL/SQL
declare
l_new_blob blob;
begin
l_new_blob := ame_api_pkg.convert_files_b(
p_source_type => ame_api_pkg.c_source_type_sql,
p_source => q'[
select 'http://www.apexofficeprint.com/docs/media/e2MonwE.jpg' as "file",
640 as "max_width", 480 as "max_height",
cursor(select null as "image", null as "width", null as "height", 'watermark' as "text", 'open sans' as "font", 22 as "font_size", '#FFFFFF' as "font_color", 10 as "x", 10 as "y", 45 as "rotation" from dual
union all
select 'https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png' as "image", 60 as "width", 60 as "height", null as "text", null as "font", null as "font_size", null as "font_color", 10 as "x", 10 as "y", 45 as "rotation" from dual
) as "overlay",
'nice.png' as "output_filename", 'image/png' as "output_mime_type"
from dual
]',
p_ame_url => ame_api_pkg.c_ame_url,
p_api_key => ''
);
sys.htp.p('length new blob: ' || to_char(dbms_lob.getlength(l_new_blob)));
end;
-- JSON
declare
l_new_blob blob;
begin
-- 1 image with overlay
l_new_blob := ame_api_pkg.convert_files_b(
p_source_type => ame_api_pkg.c_source_type_json,
p_source => q'!
[ {
"file": "http://www.apexofficeprint.com/docs/media/e2MonwE.jpg",
"max_width": 640,
"max_height": 480,
"overlay": [{
"text": "Watermark",
"font": "open sans",
"font_size": 22,
"x": 25,
"y": 40,
"rotation": 0
},{
"image":"https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png",
"x": 10,
"y": 10,
"width": 60,
"height": 60
}],
"output_filename": "nice.png",
"output_mime_type": "image/png"
} ]
!',
p_ame_url => ame_api_pkg.c_ame_url,
p_api_key => ''
);
sys.htp.p('length new blob: ' || to_char(dbms_lob.getlength(l_new_blob)));
-- multiple images
ame_api_pkg.g_stitch_orientation := 'horizontal';
ame_api_pkg.g_stitch_alignment := 'center';
ame_api_pkg.g_stitch_background_color := 'black';
ame_api_pkg.g_stitch_mime_type := 'image/jpeg';
ame_api_pkg.g_stitch_filename := 'stitched_image';
l_new_blob := ame_api_pkg.convert_files_b(
p_source_type => ame_api_pkg.c_source_type_json,
p_source => q'!
[ {
"file": "http://www.apexofficeprint.com/docs/media/e2MonwE.jpg",
"max_width": 640,
"max_height": 480,
"output_filename": "e2MonwE.png",
"output_mime_type": "image/png"
},
{
"file": "https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png",
"max_width": 640,
"max_height": 480,
"output_filename": "apex-rd-logo.png",
"output_mime_type": "image/png"
}
]
!',
p_ame_url => ame_api_pkg.c_ame_url,
p_api_key => ''
);
sys.htp.p('length new blob: ' || to_char(dbms_lob.getlength(l_new_blob)));
end;
*/
function convert_files_b(
p_source_type in varchar2 default c_source_type_sql,
p_source in clob,
p_binds in wwv_flow_plugin_util.t_bind_list default c_binds,
p_output_to in varchar2 default null,
p_output_stitch in varchar2 default null,
p_debug in varchar2 default null,
p_ame_url in varchar2 default null,
p_api_key in varchar2 default null,
p_ame_mode in varchar2 default null,
p_failover_ame_url in varchar2 default null,
p_failover_procedure in varchar2 default null,
p_log_procedure in varchar2 default null
) return blob;
/**
* @Description: Convert one or more files by using a SQL query, PL/SQL Function returning SQL or JSON
* This returns a clob which includes the output in a JSON format
* @Author: Dimitri Gielis
* @Created: 1-SEP-2019
*
* @Param: p_source_type type of source: c_source_type_sql ('SQL'), c_source_type_plsql_sql ('PLSQL_SQL'), c_source_type_plsql ('PLSQL_JSON'), c_source_type_json ('JSON')
* @Param: p_source Format of query:
* select "file",
* "quality", "width", "max_width", "height", "max_height",
* cursor("horizontal", "vertical") as "flip",
* cursor("value", "resize") as "rotate",
* cursor("font", "font_size", "x", "y", "rotation", "text", "image", "width", "height") as "overlay" ,
* cursor("x", "y", "width", "height") as "crop",
* "output_filename", "output_mime_type"
* from my_table
* @Param: p_binds Binds defined in the source (not necessary for APEX Page Items)
* @Param: p_debug Turning debugging on will generate the JSON that is sent to the ame Server in a file. The actual request to the ame Server is not done. Following constants can be used:
* - c_debug_remote: store the JSON in your dashboard on https://www.apexofficeprint.com
* - c_debug_local: store the JSON local on your pc
* - c_debug_application_item: depending the Application item ame_debug, Remote (Yes) or Local (Local) or no debugging is done
* @Param: p_ame_url Description: URL where the AME Server is running. For the ame Cloud use c_ame_url
* @Param: p_api_key Description: API Key which can be found when you login at https://www.apexofficeprint.com/ape/
* @Param: p_failover_ame_url: URL where the ame Failover Server is running. For the ame Cloud use c_ame_url_fallback
* @Param: p_failover_procedure: Procedure which is called when the failover URL is being used, so you are warned the main ame server has issues.
* @Param: p_log_procedure: Procedure which can be defined to do your own extra logging.
* @Return: Converted file in blob
*
* @Example:
-- SQL
select
ame_api_pkg.convert_files_c(
p_source_type => 'SQL',
p_source => q'[select apex_web_service.blob2clobbase64(blob_content) as "file",
:P14_MAX_WIDTH as "max_width", :P14_MAX_HEIGHT as "max_height"
--cursor
from ame_images
where id = 1
]',
p_ame_url => 'http://api.apexmediaextension.com',
p_api_key => ''
) as new_image
from ame_images
where id in (1,2);
-- PL/SQL
declare
l_new_clob clob;
begin
l_new_clob := ame_api_pkg.convert_files_c(
p_source_type => ame_api_pkg.c_source_type_sql,
p_source => q'[
select 'http://www.apexofficeprint.com/docs/media/e2MonwE.jpg' as "file",
640 as "max_width", 480 as "max_height",
cursor(select null as "image", null as "width", null as "height", 'watermark' as "text", 'open sans' as "font", 22 as "font_size", '#FFFFFF' as "font_color", 10 as "x", 10 as "y", 45 as "rotation" from dual
union all
select 'https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png' as "image", 60 as "width", 60 as "height", null as "text", null as "font", null as "font_size", null as "font_color", 10 as "x", 10 as "y", 45 as "rotation" from dual
) as "overlay",
'nice.png' as "output_filename", 'image/png' as "output_mime_type"
from dual
]',
p_ame_url => ame_api_pkg.c_ame_url,
p_api_key => ''
);
sys.htp.p('length new clob: ' || to_char(dbms_lob.getlength(l_new_clob)));
end;
*/
function convert_files_c(
p_source_type in varchar2 default c_source_type_sql,
p_source in clob,
p_binds in wwv_flow_plugin_util.t_bind_list default c_binds,
p_output_stitch in varchar2 default null,
p_debug in varchar2 default null,
p_ame_url in varchar2 default null,
p_api_key in varchar2 default null,
p_ame_mode in varchar2 default null,
p_failover_ame_url in varchar2 default null,
p_failover_procedure in varchar2 default null,
p_log_procedure in varchar2 default null
) return clob;
/**
* @Description: Convert one image by specifying a blob or url
* @Author: Dimitri Gielis
* @Created: 1-SEP-2019
*
* @Param: p_source_blob the blob of the image you want to adjust
* @Param: p_source_url the url of the image you want to adjust (AME Server needs to be able to access it)
* @Param: p_manipulator specifies which image/video manipulator to use. Available manipulators: "sharp" (default) or "jimp".
* @Param: p_quality the quality of a JPEG image; values 0 till 100
* @Param: p_width the new width of the image; will not be proporsional
* @Param: p_max_width the new width of the image; will be proporsional
* @Param: p_height the new height of the image; will not be proporsional
* @Param: p_max_height the new height of the image; will be proporsional
* @Param: p_resize_method the method to use for resizing: Sharp: nearest_neighbour,bicubic,mitchell,lanczos2,lanczos3,resize,methods
* Jimp: nearest_neighbour,bilinear,bicubic,hermite,bezier
* @Param: p_crop select a part of the image {"x":"","y":"","width":"","height":""}
* @Param: p_flip flip or mirror the image; {"horizontal":true,
* "vertical":true}
* @Param: p_rotate rotate the image {"value":90, //degrees, clockwise
* "resize": true // if the width and height should be resized -> default false;}
* @Param: p_overlay put one or more text and/or image watermarks [{"font_size":"22px","font": "open sans","x": 25,"y": 40,"text": "Watermark",},
* {"x": 25,"y": 40,"rotation": 0,"image": "base64:xxxx","width":100,"height":100}]
* @Param: p_greyscale make an image grey
* @Param: p_output_filename the name of the output file
* @Param: p_output_mime_type the mime/type of the output
* @Return: Converted file in blob
*
* @Example:
-- SQL
begin
ame_api_pkg.g_api_key := '';
end;
select
ame_api_pkg.convert_image_b1(
p_source_blob => blob_content,
p_max_width => 100,
p_flip => '{"vertical": true}',
p_output_filename => 'output.jpg',
p_output_mime_type => 'image/jpeg'
) as new_image
from ame_images
where id = 1;
-- PL/SQL
-- make sure your API Key is set before you do call
declare
l_new_blob blob;
begin
ame_api_pkg.g_api_key := '';
for r in (select blob_content
from ame_images
where id = 1)
loop
-- for now only 1 image is supported
l_new_blob := ame_api_pkg.convert_image_b1(
p_source_blob => r.blob_content,
p_max_width => 100,
p_flip => '{"vertical": true}',
p_output_filename => 'output.jpg',
p_output_mime_type => 'image/jpeg'
);
sys.htp.p('length orig blob: ' || to_char(dbms_lob.getlength(r.blob_content)));
sys.htp.p('length new blob: ' || to_char(dbms_lob.getlength(l_new_blob)));
end loop;
end;
*/
function convert_image_b1(
p_source_blob in blob default null,
p_source_url in varchar2 default null,
p_manipulator in varchar2 default null,
p_quality in varchar2 default null,
p_width in number default null,
p_max_width in number default null,
p_height in number default null,
p_max_height in number default null,
p_resize_method in varchar2 default null,
p_crop in varchar2 default null,
p_flip in varchar2 default null,
p_rotate in number default null,
p_overlay in varchar2 default null,
p_greyscale in varchar2 default null,
p_output_filename in varchar2 default null,
p_output_mime_type in varchar2 default null
) return blob;
/**
* @Description: Convert one image by specifying a blob or url
* @Author: Dimitri Gielis
* @Created: 1-SEP-2019
*
* @Param: p_source_blob the blob of the image you want to adjust
* @Param: p_source_url the url of the image you want to adjust (AME Server needs to be able to access it)
* @Param: p_manipulator specifies which image/video manipulator to use. Available manipulators: "sharp" (default) or "jimp".
* @Param: p_quality the quality of a JPEG image; values 0 till 100
* @Param: p_width the new width of the image; will not be proporsional
* @Param: p_max_width the new width of the image; will be proporsional
* @Param: p_height the new height of the image; will not be proporsional
* @Param: p_max_height the new height of the image; will be proporsional
* @Param: p_resize_method the method to use for resizing: Sharp: nearest_neighbour,bicubic,mitchell,lanczos2,lanczos3,resize,methods
* Jimp: nearest_neighbour,bilinear,bicubic,hermite,bezier
* @Param: p_crop_x x coordinates to start crop of image
* @Param: p_crop_y y coordinates to start crop of image
* @Param: p_crop_width width of selection
* @Param: p_crop_height height of selection
* @Param: p_flip_horizontal (Y/N) horizontal flip the image
* @Param: p_flip_vertical (Y/N) vertical flip the image
* @Param: p_rotate_degrees rotate the image clockwise e.g. 90
* @Param: p_overlay_text put text on top of the image
* @Param: p_overlay_font font for text, default is open sans
* @Param: p_overlay_font_size font size
* @Param: p_overlay_font_color white or black
* @Param: p_overlay_txt_x x coordinates
* @Param: p_overlay_txt_y y coordinates
* @Param: p_overlay_txt_rotation rotate text
* @Param: p_overlay_image put another image on top of the image
* @Param: p_overlay_img_x x coordinates
* @Param: p_overlay_img_y y coordinates
* @Param: p_overlay_img_rotation rotate image
* @Param: p_overlay_img_width width of image
* @Param: p_overlay_img_height height of image
* @Param: p_greyscale make an image grey
* @Param: p_output_filename the name of the output file
* @Param: p_output_mime_type the mime/type of the output
* @Return: Converted file in blob
*
* @Example:
-- SQL
begin
ame_api_pkg.g_api_key := '';
end;
select
ame_api_pkg.convert_image_b2(
p_source_blob => blob_content,
p_max_width => 100,
p_flip_vertical => 'Y',
p_output_filename => 'output.jpg',
p_output_mime_type => 'image/jpeg'
) as new_image
from ame_images
where id = 1;
-- PL/SQL
-- make sure your API Key is set before you do call
declare
l_new_blob blob;
begin
ame_api_pkg.g_api_key := '';
for r in (select blob_content
from ame_images
where id = 1)
loop
l_new_blob := ame_api_pkg.convert_image_b2(
p_source_blob => r.blob_content,
p_max_width => 100,
p_flip_vertical => 'Y',
p_output_filename => 'output.jpg',
p_output_mime_type => 'image/jpeg'
);
sys.htp.p('length orig blob: ' || to_char(dbms_lob.getlength(r.blob_content)));
sys.htp.p('length new blob: ' || to_char(dbms_lob.getlength(l_new_blob)));
end loop;
end;
*/
function convert_image_b2(
p_source_blob in blob default null,
p_source_url in varchar2 default null,
-- manipulator
p_manipulator in varchar2 default null,
-- quality jpeg
p_quality in varchar2 default null,
-- resize
p_width in number default null,
p_max_width in number default null,
p_height in number default null,
p_max_height in number default null,
p_resize_method in varchar2 default null,
-- crop
p_crop_x in number default null,
p_crop_y in number default null,
p_crop_width in number default null,
p_crop_height in number default null,
-- flip
p_flip_horizontal in varchar2 default null,
p_flip_vertical in varchar2 default null,
-- rotate
p_rotate_degrees in number default null,
-- overlay (watermark)
p_overlay_text in varchar2 default null,
p_overlay_font in varchar2 default null,
p_overlay_font_size in number default null,
p_overlay_font_color in varchar2 default null,
p_overlay_txt_x in number default null,
p_overlay_txt_y in number default null,
p_overlay_txt_rotation in number default null,
p_overlay_image in clob default null,
p_overlay_img_x in number default null,
p_overlay_img_y in number default null,
p_overlay_img_rotation in number default null,
p_overlay_img_width in number default null,
p_overlay_img_height in number default null,
-- greyscale
p_greyscale in varchar2 default null,
-- output
p_output_filename in varchar2 default null,
p_output_mime_type in varchar2 default null
) return blob;
/**
* @Description: Convert one image by specifying a blob or url
* @Author: Dimitri Gielis
* @Created: 1-SEP-2019
*
* @Param: p_source_blob the blob of the image you want to adjust
* @Param: p_source_url the url of the image you want to adjust (AME Server needs to be able to access it)
* @Param: p_manipulator specifies which image/video manipulator to use. Available manipulators: "sharp" (default) or "jimp".
* @Param: p_quality the quality of a JPEG image; values 0 till 100
* @Param: p_width the new width of the image; will not be proporsional
* @Param: p_max_width the new width of the image; will be proporsional
* @Param: p_height the new height of the image; will not be proporsional
* @Param: p_max_height the new height of the image; will be proporsional
* @Param: p_resize_method the method to use for resizing: Sharp: nearest_neighbour,bicubic,mitchell,lanczos2,lanczos3,resize,methods
* Jimp: nearest_neighbour,bilinear,bicubic,hermite,bezier
* @Param: p_crop_x x coordinates to start crop of image
* @Param: p_crop_y y coordinates to start crop of image
* @Param: p_crop_width width of selection
* @Param: p_crop_height height of selection
* @Param: p_flip_horizontal (Y/N) horizontal flip the image
* @Param: p_flip_vertical (Y/N) vertical flip the image
* @Param: p_rotate_degrees rotate the image clockwise e.g. 90
* @Param: p_overlay_sql put one or more texts and/or images on top of the image
* use following syntax for the SQL statement:
* select 'url or base64 encoded blob' as "image", 50 as "width", 30 as "height", 50 as "max_width", 50 as "max_height",
'watermark' as "text", 'open sans' as "font", 12 as "font_size", 'white' as "font_color",
10 as "x", 10 as "y", 45 as "rotation"
from table
* @Param: p_binds Binds defined in the source (not necessary for APEX Page Items, which are known by AME automatically)
* @Param: p_greyscale make an image grey
* @Param: p_output_filename the name of the output file
* @Param: p_output_mime_type the mime/type of the output
* @Return: Converted file in blob
*
* @Example:
declare
l_orig_blob blob;
l_new_blob blob;
l_binds wwv_flow_plugin_util.t_bind_list;
begin
select blob_content
into l_orig_blob
from ame_images
where id=1;
-- set AME API key (only necessary for AME Cloud)
ame_api_pkg.g_api_key := '';
-- define bind variables
l_binds(1).name := 'p_id';
l_binds(1).value := '2';
l_new_blob := ame_api_pkg.convert_image_b3(
p_source_blob => l_orig_blob,
p_overlay_sql => q'[
select 'watermark' as "text", 'open sans' as "font", 40 as "font_size", 'white' as "font_color", to_clob(null) as "image", 50 as "x", 10 as "y", 45 as "rotation" , null as "width", null as "height" from dual
union all
select null as "text", null as "font", null as "font_size", null as "font_color", apex_web_service.blob2clobbase64(blob_content) as "image", 10 as "x", 10 as "y", 90 as "rotation", 90 as "width", 50 as "height" from ame_images where id=:p_id
union all
select null as "text", null as "font", null as "font_size", null as "font_color", to_clob('https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png') as "image", 200 as "x", 100 as "y", 0 as "rotation", 80 as "max_width", 60 as "max_height" from dual
]',
p_output_filename => 'output.jpg',
p_output_mime_type => 'image/jpeg'
);
sys.htp.p('length orig blob: ' || to_char(dbms_lob.getlength(l_orig_blob)));
sys.htp.p('length new blob: ' || to_char(dbms_lob.getlength(l_new_blob)));
end;
*/
function convert_image_b3(
p_source_blob in blob default null,
p_source_url in varchar2 default null,
-- manipulator
p_manipulator in varchar2 default null,
-- quality jpeg
p_quality in varchar2 default null,
-- resize
p_width in number default null,
p_max_width in number default null,
p_height in number default null,
p_max_height in number default null,
p_resize_method in varchar2 default null,
-- crop
p_crop_x in number default null,
p_crop_y in number default null,
p_crop_width in number default null,
p_crop_height in number default null,
-- flip
p_flip_horizontal in varchar2 default null,
p_flip_vertical in varchar2 default null,
-- rotate
p_rotate_degrees in number default null,
-- overlay (watermark)
p_overlay_sql in varchar2 default null,
p_overlay_binds in wwv_flow_plugin_util.t_bind_list default c_binds,
-- greyscale
p_greyscale in varchar2 default null,
-- output
p_output_filename in varchar2 default null,
p_output_mime_type in varchar2 default null
) return blob;
/**
* @Description: Convert one image by specifying a blob or url
* @Author: Dimitri Gielis
* @Created: 1-SEP-2019
*
* @Param: p_source_blob the blob of the image you want to adjust
* @Param: p_source_url the url of the image you want to adjust (AME Server needs to be able to access it)
* @Param: p_manipulator specifies which image/video manipulator to use. Available manipulators: "sharp" (default) or "jimp".
* @Param: p_quality the quality of a JPEG image; values 0 till 100
* @Param: p_width the new width of the image; will not be proporsional
* @Param: p_max_width the new width of the image; will be proporsional
* @Param: p_height the new height of the image; will not be proporsional
* @Param: p_max_height the new height of the image; will be proporsional
* @Param: p_resize_method the method to use for resizing: Sharp: nearest_neighbour,bicubic,mitchell,lanczos2,lanczos3,resize,methods
* Jimp: nearest_neighbour,bilinear,bicubic,hermite,bezier
* @Param: p_crop_x x coordinates to start crop of image
* @Param: p_crop_y y coordinates to start crop of image
* @Param: p_crop_width width of selection
* @Param: p_crop_height height of selection
* @Param: p_flip_horizontal (Y/N) horizontal flip the image
* @Param: p_flip_vertical (Y/N) vertical flip the image
* @Param: p_rotate_degrees rotate the image clockwise e.g. 90
* @Param: p_overlay_sql put one or more texts and/or images on top of the image
* use following syntax for the SQL statement:
* select 'url or base64 encoded blob' as "image", 50 as "width", 30 as "height", 50 as "max_width", 50 as "max_height",
'watermark' as "text", 'open sans' as "font", 12 as "font_size", 'white' as "font_color",
10 as "x", 10 as "y", 45 as "rotation"
from table
* @Param: p_binds Binds defined in the source (not necessary for APEX Page Items, which are known by AME automatically)
* @Param: p_greyscale make an image grey
* @Param: p_output_filename the name of the output file
* @Param: p_output_mime_type the mime/type of the output
* @Out: o_initial_width output initial width
* @Out: o_initial_height output initial height
* @Out: o_initial_mime_type output initial mime type
* @Out: o_initial_extension output extension e.g. jpg
* @Out: o_output_width output new width
* @Out: o_output_height output new height
* @Out: o_output_mime_type output new mime type
* @Out: o_output_extension output new extension
* @Out: o_output_blob output image (blob)
* @Out: o_meta_data output meta data
*
* @Example:
declare
l_amt integer := 4000;
l_pos integer := 1;
l_buf varchar2(32000);
l_orig_blob blob;
l_initial_width number;
l_initial_height number;
l_initial_mime_type varchar2(200);
l_initial_extension varchar2(200);
l_output_width number;
l_output_height number;
l_output_mime_type varchar2(200);
l_output_extension varchar2(200);
l_output_blob blob;
l_meta_data clob;
begin
select blob_content
into l_orig_blob
from ame_images
where id=1;
ame_api_pkg.g_api_key := '';
ame_api_pkg.convert_image_o1(
p_source_blob => l_orig_blob,
p_overlay_sql => q'[
select 'watermark' as "text", 'open sans' as "font", 40 as "font_size", 'white' as "font_color", null as "image", 50 as "x", 10 as "y", 45 as "rotation" , null as "width", null as "height" from dual
union all
select null as "text", null as "font", null as "font_size", null as "font_color", 'https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png' as "image", 10 as "x", 10 as "y", 90 as "rotation", 90 as "width", 50 as "height" from dual
union all
select null as "text", null as "font", null as "font_size", null as "font_color", 'https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png' as "image", 200 as "x", 100 as "y", 0 as "rotation", 80 as "max_width", 60 as "max_height" from dual
]',
p_output_filename => 'output.jpg',
p_output_mime_type => 'image/jpeg',
o_initial_width => l_initial_width,
o_initial_height => l_initial_height,
o_initial_mime_type=> l_initial_mime_type,
o_initial_extension=> l_initial_extension,
o_output_width => l_output_width,
o_output_height => l_output_height,
o_output_mime_type => l_output_mime_type,
o_output_extension => l_output_extension,
o_output_blob => l_output_blob,
o_meta_data => l_meta_data
);
sys.htp.p('length orig blob: ' || to_char(dbms_lob.getlength(l_orig_blob)));
sys.htp.p('image initial width: ' || to_char(l_initial_width));
sys.htp.p('image initial height: ' || to_char(l_initial_height));
sys.htp.p('initial mime_type: ' || l_initial_mime_type);
sys.htp.p('initial extension: ' || l_initial_extension);
sys.htp.p('length new blob: ' || to_char(dbms_lob.getlength(l_output_blob)));
sys.htp.p('image width: ' || to_char(l_output_width));
sys.htp.p('image height: ' || to_char(l_output_height));
sys.htp.p('mime_type: ' || l_output_mime_type);
sys.htp.p('extension: ' || l_output_extension);
sys.htp.p('meta data: ');
-- as the meta data can be over 32K we will loop over it
if dbms_lob.getlength(l_meta_data) > 0
then
loop
begin
dbms_lob.read(l_meta_data, l_amt, l_pos, l_buf);
l_pos := l_pos+l_amt;
l_amt := 4000;
sys.htp.prn(l_buf);
exception
when no_data_found then
exit;
end;
end loop;
end if;
end;
*/
procedure convert_image_o1(
p_source_blob in blob default null,
p_source_url in varchar2 default null,
-- manipulator
p_manipulator in varchar2 default null,
-- quality jpeg
p_quality in varchar2 default null,
-- resize
p_width in number default null,
p_max_width in number default null,
p_height in number default null,
p_max_height in number default null,
p_resize_method in varchar2 default null,
-- crop
p_crop_x in number default null,
p_crop_y in number default null,
p_crop_width in number default null,
p_crop_height in number default null,
-- flip
p_flip_horizontal in varchar2 default null,
p_flip_vertical in varchar2 default null,
-- rotate
p_rotate_degrees in number default null,
-- overlay (watermark)
p_overlay_sql in varchar2 default null,
p_overlay_binds in wwv_flow_plugin_util.t_bind_list default c_binds,
-- greyscale
p_greyscale in varchar2 default null,
-- output
p_output_filename in varchar2 default null,
p_output_mime_type in varchar2 default null,
-- output variables
o_initial_width out number,
o_initial_height out number,
o_initial_mime_type out varchar2,
o_initial_extension out varchar2,
o_output_width out number,
o_output_height out number,
o_output_mime_type out varchar2,
o_output_extension out varchar2,
o_output_blob out blob,
o_meta_data out clob
);
/**
* @Description: Convert one image by specifying a blob or url
* @Author: Dimitri Gielis
* @Created: 1-SEP-2019
*
* @Param: p_source_blob the blob of the image you want to adjust
* @Param: p_source_url the url of the image you want to adjust (AME Server needs to be able to access it)
* @Param: p_manipulator specifies which image/video manipulator to use. Available manipulators: "sharp" (default) or "jimp".
* @Param: p_quality the quality of a JPEG image; values 0 till 100
* @Param: p_width the new width of the image; will not be proporsional
* @Param: p_max_width the new width of the image; will be proporsional
* @Param: p_height the new height of the image; will not be proporsional
* @Param: p_max_height the new height of the image; will be proporsional
* @Param: p_resize_method the method to use for resizing: Sharp: nearest_neighbour,bicubic,mitchell,lanczos2,lanczos3,resize,methods
* Jimp: nearest_neighbour,bilinear,bicubic,hermite,bezier
* @Param: p_crop_x x coordinates to start crop of image
* @Param: p_crop_y y coordinates to start crop of image
* @Param: p_crop_width width of selection
* @Param: p_crop_height height of selection
* @Param: p_flip_horizontal (Y/N) horizontal flip the image
* @Param: p_flip_vertical (Y/N) vertical flip the image
* @Param: p_rotate_degrees rotate the image clockwise e.g. 90
* @Param: p_overlay_sql put one or more texts and/or images on top of the image
* use following syntax for the SQL statement:
* select 'url or base64 encoded blob' as "image", 50 as "width", 30 as "height", 50 as "max_width", 50 as "max_height",
'watermark' as "text", 'open sans' as "font", 12 as "font_size", 'white' as "font_color",
10 as "x", 10 as "y", 45 as "rotation"
from table
* @Param: p_binds Binds defined in the source (not necessary for APEX Page Items, which are known by AME automatically)
* @Param: p_greyscale make an image grey
* @Param: p_output_filename the name of the output file
* @Param: p_output_mime_type the mime/type of the output
* @Out: o_output_blob output image (blob)
* @Out: o_meta_data output meta data
*
* @Example:
declare
l_amt integer := 4000;
l_pos integer := 1;
l_buf varchar2(32000);
l_orig_blob blob;
l_output_blob blob;
l_meta_data clob;
begin
select blob_content
into l_orig_blob
from ame_images
where id=1;
ame_api_pkg.g_api_key := '';
ame_api_pkg.convert_image_o2(
p_source_blob => l_orig_blob,
p_overlay_sql => q'[
select 'watermark' as "text", 'open sans' as "font", 40 as "font_size", 'white' as "font_color", null as "image", 50 as "x", 10 as "y", 45 as "rotation" , null as "width", null as "height" from dual
union all
select null as "text", null as "font", null as "font_size", null as "font_color", 'https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png' as "image", 10 as "x", 10 as "y", 90 as "rotation", 90 as "width", 50 as "height" from dual
union all
select null as "text", null as "font", null as "font_size", null as "font_color", 'https://www.apexrnd.be/apexrnd/images/apex-rd-logo.png' as "image", 200 as "x", 100 as "y", 0 as "rotation", 80 as "max_width", 60 as "max_height" from dual
]',
p_output_filename => 'output.jpg',
p_output_mime_type => 'image/jpeg',
o_output_blob => l_output_blob,
o_meta_data => l_meta_data
);
sys.htp.p('length blob: ' || to_char(dbms_lob.getlength(l_output_blob)));
sys.htp.p('meta data: ');
-- as the meta data can be over 32K we will loop over it
if dbms_lob.getlength(l_meta_data) > 0
then
loop
begin
dbms_lob.read(l_meta_data, l_amt, l_pos, l_buf);
l_pos := l_pos+l_amt;
l_amt := 4000;
sys.htp.prn(l_buf);
exception
when no_data_found then
exit;
end;
end loop;
end if;
end;
*/
procedure convert_image_o2(
p_source_blob in blob default null,
p_source_url in varchar2 default null,
-- manipulator
p_manipulator in varchar2 default null,
-- quality jpeg
p_quality in varchar2 default null,
-- resize
p_width in number default null,
p_max_width in number default null,
p_height in number default null,
p_max_height in number default null,
p_resize_method in varchar2 default null,
-- crop
p_crop_x in number default null,
p_crop_y in number default null,
p_crop_width in number default null,
p_crop_height in number default null,
-- flip
p_flip_horizontal in varchar2 default null,
p_flip_vertical in varchar2 default null,
-- rotate
p_rotate_degrees in number default null,
-- overlay (watermark)
p_overlay_sql in varchar2 default null,
p_overlay_binds in wwv_flow_plugin_util.t_bind_list default c_binds,
-- greyscale
p_greyscale in varchar2 default null,
-- output
p_output_filename in varchar2 default null,
p_output_mime_type in varchar2 default null,
-- output variables
o_output_blob out blob,
o_meta_data out clob
);
/**
* @Description: Get the meta data from an image by specifying a blob or url
* @Author: Dimitri Gielis
* @Created: 1-SEP-2019
*
* @Param: p_source_blob the blob of the image you want to adjust
* @Param: p_source_url the url of the image you want to adjust (AME Server needs to be able to access it)
* @Out: o_output_width output new width
* @Out: o_output_height output new height
* @Out: o_output_mime_type output new mime type
* @Out: o_meta_data output meta data
*
* @Example:
declare
l_amt integer := 4000;
l_pos integer := 1;
l_buf varchar2(32000);
l_orig_blob blob;
l_width number(6);
l_height number(6);
l_mime_type varchar2(200);
l_extension varchar2(10);
l_meta_data clob;
begin
select blob_content
into l_orig_blob
from ame_images
where id=1;
ame_api_pkg.g_api_key := '';
ame_api_pkg.get_image_properties(
p_source_blob => l_orig_blob,
o_width => l_width,
o_height => l_height,
o_mime_type => l_mime_type,
o_extension => l_extension,
o_meta_data => l_meta_data
);
sys.htp.p('image width: ' || to_char(l_width));
sys.htp.p('image height: ' || to_char(l_height));
sys.htp.p('mime_type: ' || l_mime_type);
sys.htp.p('extension: ' || l_extension);
sys.htp.p('meta data: ');
-- as the meta data can be over 32K we will loop over it
if dbms_lob.getlength(l_meta_data) > 0
then
loop
begin
dbms_lob.read(l_meta_data, l_amt, l_pos, l_buf);
l_pos := l_pos+l_amt;
l_amt := 4000;
sys.htp.prn(l_buf);
exception
when no_data_found then
exit;
end;
end loop;
end if;
end;
*/
procedure get_image_properties(
p_source_blob in blob default null,
p_source_url in varchar2 default null,
p_manipulator in varchar2 default null,
-- output variables
o_width out number,
o_height out number,
o_mime_type out varchar2,
o_extension out varchar2,
o_meta_data out clob
);