ORACLE MASTERMINDS: September 2018

Friday, 28 September 2018

Send HTML Email with Attachment

Step 1:

Create directory and grant permission

Step 2: create table IMAGE_STORE1 and load the file from directory to the blob

Step 3:

Compile the below Scripts in user

create or replace PACKAGE BODY send_mail_attachment_html
AS

c_base64_line CONSTANT PLS_INTEGER := 57;
c_crlf VARCHAR2(2) := UTL_TCP.crlf;
/*
If a default SMTP server is specified here it will be used when the user doesn't specify a server list to try
instead of checking smtp_out_server and db_domain initialization parameters.

*/
c_default_smtp_server CONSTANT VARCHAR2(100) := NULL;
c_default_smtp_port CONSTANT INTEGER := 25;

g_boundary VARCHAR2(256);

/* Logging/debugging options */
g_log_options INTEGER := c_log_dbms_output;
g_log_text VARCHAR2(32767) := NULL;
g_verbose BOOLEAN := c_default_verbose;

---------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------*/

-- write the text to each logging option that is currently enabled
-- verbose comments will not be logged if verbose mode is off.
PROCEDURE write_to_log(v_text IN VARCHAR2, p_verbose IN BOOLEAN DEFAULT FALSE)
IS
BEGIN
-- If global verbose setting is ON (meaning log everything)
-- or if this text is not verbose then log it.
IF g_verbose OR NOT p_verbose
THEN
IF BITAND(g_log_options, c_log_dbms_output) > 0
THEN
-- DBMS_OUTPUT.put_line(v_text);
null;
END IF;

IF BITAND(g_log_options, c_log_rolling_buffer) > 0
THEN
IF LENGTH(g_log_text) + LENGTH(c_crlf) + LENGTH(v_text) > 32767
THEN
g_log_text := SUBSTR(g_log_text, INSTR(g_log_text, c_crlf, LENGTH(v_text) + 2));
END IF;

g_log_text := g_log_text || v_text || c_crlf;
END IF;

IF BITAND(g_log_options, c_log_client_info) > 0
THEN
DBMS_APPLICATION_INFO.set_client_info(v_text);
END IF;
END IF;
END write_to_log;

-- When verbose mode is TRUE then additional information will be written to the logs.
-- If logging is turned off then verbose mode won't add anything.
PROCEDURE set_verbose(p_verbose IN BOOLEAN)
IS
BEGIN
g_verbose := NVL(p_verbose, c_default_verbose);

IF p_verbose
THEN
write_to_log('Switching logging to verbose mode', TRUE);
END IF;
END set_verbose;

-- Returns the current setting of verbose mode (true/false)
FUNCTION get_verbose
RETURN BOOLEAN
IS
BEGIN
RETURN g_verbose;
END get_verbose;

-- Turn off logging (options=0) or turn on different options (see c_log_xxxxx constants)
PROCEDURE set_log_options(p_log_options IN INTEGER)
IS
BEGIN
-- options big mask must be between 0 and sum of all possible log options.
IF p_log_options < 0
OR p_log_options > c_log_dbms_output + c_log_rolling_buffer + c_log_client_info
THEN
raise_application_error(
-20001,
'Invalid log options, must be between 0 and '
|| TO_CHAR(c_log_dbms_output + c_log_rolling_buffer + c_log_client_info),
TRUE
);
END IF;

g_log_options := p_log_options;
END set_log_options;

-- Return current logging options
FUNCTION get_log_options
RETURN INTEGER
IS
BEGIN
RETURN g_log_options;
END get_log_options;

-- Clears (null) the logging buffers
PROCEDURE clear_log
IS
BEGIN
-- Clear the log buffer
g_log_text := NULL;

-- if logging to session client then clear that too
IF BITAND(g_log_options, c_log_client_info) > 0
THEN
DBMS_APPLICATION_INFO.set_client_info(NULL);
END IF;
END clear_log;

-- Return the current contents of the rolling log buffer
FUNCTION get_log_text
RETURN VARCHAR2
IS
BEGIN
RETURN g_log_text;
END get_log_text;

---------------------------------------------------------------------------
---------------------------------------------------------------------------

FUNCTION find_a_server(p_server_list IN VARCHAR2 DEFAULT NULL)
RETURN connection
IS
v_servers VARCHAR2(32767);
v_start INTEGER := 1;
v_index INTEGER;
v_port_split INTEGER;
v_host VARCHAR2(32767);
v_port INTEGER;
v_connected BOOLEAN := FALSE;
v_length INTEGER;
v_connection UTL_SMTP.connection;
v_reply UTL_SMTP.reply;
BEGIN
-- Enteries should be of the from
-- host[:port][,host[:port]][,host[:port]][,host[:port]]...
-- If the user didn't specify a list, and there is no default
-- then use the UTL_MAIL parameters
-- Use smtp_out_server if available or db_domain if not
IF p_server_list IS NULL
THEN
IF c_default_smtp_server IS NULL
THEN
SELECT *
INTO v_servers
FROM (SELECT TRIM(VALUE)
FROM v$parameter
WHERE name IN ('smtp_out_server', 'db_domain') AND TRIM(VALUE) IS NOT NULL
ORDER BY name DESC)
WHERE ROWNUM = 1;
ELSE
v_servers := c_default_smtp_server || ':' || c_default_smtp_port;
END IF;
ELSE
v_servers := p_server_list;
END IF;

v_length := LENGTH(v_servers);

LOOP
v_index := INSTR(v_servers, ',', v_start);

IF v_index = 0
THEN
v_port_split := INSTR(v_servers, ':');

IF v_port_split > 0
THEN
v_host := SUBSTR(v_servers, 1, v_port_split - 1);
v_port := TO_NUMBER(SUBSTR(v_servers, v_port_split + 1));
ELSE
v_host := TRIM(v_servers);
v_port := c_default_smtp_port;
END IF;

v_start := v_length + 1;
ELSE
v_host := SUBSTR(v_servers, v_start, v_index - v_start);
v_port_split := INSTR(v_host, ':');

IF v_port_split > 0
THEN
v_port := TO_NUMBER(SUBSTR(v_host, v_port_split + 1));
v_host := SUBSTR(v_host, 1, v_port_split - 1);
ELSE
v_port := c_default_smtp_port;
END IF;

v_start := v_index + 1;
END IF;

BEGIN
write_to_log(
'find_a_server: trying - ' || v_connection.HOST || ':' || v_connection.port,
TRUE
);
v_connection := UTL_SMTP.open_connection(v_host, v_port);
UTL_SMTP.helo(v_connection, v_host);
v_connected := TRUE;
EXCEPTION
WHEN OTHERS
THEN
write_to_log(
'find_a_server: failed - ' || v_connection.HOST || ':' || v_connection.port,
TRUE
);

-- If we've walked off the end of the server list
-- without getting connected then raise the last error
-- received from the attempt
-- otherwise, we'll try again with the next server/port
IF v_start > v_length
THEN
write_to_log('find_a_server: failed all hosts in server list', TRUE);
RAISE;
END IF;
END;

EXIT WHEN v_connected;
END LOOP;

write_to_log('find_a_server: ' || v_connection.HOST || ':' || v_connection.port);
RETURN v_connection;
END find_a_server;

FUNCTION instr_enc(
p_string IN VARCHAR2,
p_substring IN VARCHAR2,
p_start IN INTEGER DEFAULT 1,
p_occurence IN INTEGER DEFAULT 1,
p_enclosing IN VARCHAR2 DEFAULT NULL,
p_escape IN VARCHAR2 DEFAULT '\'
)
RETURN INTEGER
IS
v_occur_cnt INTEGER := 0;
v_sub_idx INTEGER;
v_sub_len INTEGER := LENGTH(p_substring);
v_enc_idx INTEGER;
v_enc_len INTEGER := LENGTH(p_enclosing);
v_esc_idx INTEGER;
v_esc_len INTEGER := LENGTH(p_escape);
v_index INTEGER := p_start;
v_enclosed BOOLEAN := FALSE;
v_max INTEGER := LENGTH(p_string) + 1;

FUNCTION first_found(a IN INTEGER, b IN INTEGER, c INTEGER)
RETURN INTEGER
IS
v_result INTEGER;
BEGIN
v_result :=
LEAST(NVL(NULLIF(a, 0), v_max), NVL(NULLIF(b, 0), v_max), NVL(NULLIF(c, 0), v_max));

IF v_result = v_max
THEN
v_result := 0;
END IF;

RETURN v_result;
END;
BEGIN
IF p_enclosing IS NULL
THEN
v_index :=
INSTR(
p_string,
p_substring,
p_start,
p_occurence
);
ELSE
LOOP
v_sub_idx := INSTR(p_string, p_substring, v_index);
v_enc_idx := INSTR(p_string, p_enclosing, v_index);
v_esc_idx := INSTR(p_string, p_escape || p_enclosing, v_index);

v_index := first_found(v_sub_idx, v_enc_idx, v_esc_idx);

IF GREATEST(v_sub_idx, v_enc_idx, v_esc_idx) = 0
THEN
NULL;
ELSIF v_index = v_esc_idx -- escape character found
THEN
v_index := v_index + v_esc_len + v_enc_len;
ELSIF v_index = v_enc_idx -- enclosing character found
THEN
v_enclosed := NOT v_enclosed;
v_index := v_index + v_enc_len;
ELSE -- substring found
IF v_enclosed
THEN
v_index := v_index + v_sub_len;
ELSE -- not enclosed
v_occur_cnt := v_occur_cnt + 1;

IF v_occur_cnt < p_occurence
THEN
v_index := v_index + v_sub_len;
END IF;
END IF;
END IF;

EXIT WHEN v_index > LENGTH(p_string) OR v_index = 0 OR v_occur_cnt = p_occurence;
END LOOP;
END IF;

RETURN v_index;
END;

FUNCTION extract_address(p_string IN VARCHAR2)
RETURN VARCHAR2
IS
v_bracket_start INTEGER;
v_bracket_end INTEGER;
v_result VARCHAR2(32767) := TRIM(p_string);
BEGIN
v_bracket_start :=
instr_enc(
v_result,
'<',
1,
1,
'"'
);

IF v_bracket_start > 0
THEN
v_bracket_end :=
instr_enc(
v_result,
'>',
v_bracket_start + 1,
1,
'"'
);

IF v_bracket_end > 0
THEN
v_result :=
SUBSTR(v_result, v_bracket_start + 1, v_bracket_end - v_bracket_start - 1);
END IF;
END IF;

-- Take out any tab, line feed or carriage return characters
-- that might have been used to visually format the strings
RETURN TRIM(TRANSLATE(v_result, 'a' || CHR(9) || CHR(10) || CHR(13), 'a'));
END extract_address;

PROCEDURE parse_email_list(p_addresses IN VARCHAR2, p_list IN OUT NOCOPY DBMS_SQL.varchar2s)
IS
v_length INTEGER := LENGTH(p_addresses);
v_start INTEGER := 1;
v_delim_index INTEGER;
BEGIN
WHILE (v_start <= v_length)
LOOP
v_delim_index :=
instr_enc(
p_addresses,
',',
v_start,
1,
'"'
);

IF v_delim_index = 0
THEN
p_list(p_list.COUNT + 1) := extract_address(SUBSTR(p_addresses, v_start));
v_start := v_length + 1;
ELSE
p_list(p_list.COUNT + 1) :=
extract_address(SUBSTR(p_addresses, v_start, v_delim_index - v_start));
v_start := v_delim_index + 1;
END IF;
END LOOP;
END parse_email_list;

PROCEDURE begin_mime_block(
p_connection IN OUT NOCOPY connection,
p_mime_type IN VARCHAR2 DEFAULT 'text/plain;charset=us-ascii',
p_inline IN BOOLEAN DEFAULT TRUE,
p_filename IN VARCHAR2 DEFAULT NULL,
p_transfer_enc IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
UTL_SMTP.write_data(p_connection, '--' || g_boundary || c_crlf);
UTL_SMTP.write_data(p_connection, 'Content-Type: ' || p_mime_type || c_crlf);

IF (p_transfer_enc IS NOT NULL)
THEN
UTL_SMTP.write_data(
p_connection,
'Content-Transfer-Encoding: ' || p_transfer_enc || c_crlf
);
END IF;

IF (p_filename IS NOT NULL)
THEN
IF (p_inline)
THEN
UTL_SMTP.write_data(
p_connection,
'Content-Disposition: inline; filename="' || p_filename || '"' || c_crlf
);
ELSE
UTL_SMTP.write_data(
p_connection,
'Content-Disposition: attachment; filename="' || p_filename || '"' || c_crlf
);
END IF;
END IF;

UTL_SMTP.write_data(p_connection, c_crlf);
END begin_mime_block;

------------------------------------------------------------------------
PROCEDURE end_mime_block(
p_connection IN OUT NOCOPY connection,
p_last IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
UTL_SMTP.write_data(p_connection, c_crlf);

IF (p_last)
THEN
UTL_SMTP.write_data(p_connection, '--' || g_boundary || '--' || c_crlf);
END IF;
END end_mime_block;

PROCEDURE write_clob(p_connection IN OUT NOCOPY connection, p_clob IN OUT NOCOPY CLOB)
IS
v_len INTEGER;
v_index INTEGER;
BEGIN
v_len := DBMS_LOB.getlength(p_clob);
v_index := 1;

write_to_log('Starting write_clob: ' || TO_CHAR(v_len) || ' characters', TRUE);

WHILE v_index <= v_len
LOOP
UTL_SMTP.write_data(p_connection, DBMS_LOB.SUBSTR(p_clob, 32000, v_index));
v_index := v_index + 32000;
END LOOP;

write_to_log('End write_clob: ' || TO_CHAR(v_len) || ' characters', TRUE);
END write_clob;

PROCEDURE write_blob(p_connection IN OUT NOCOPY connection, p_blob IN OUT NOCOPY BLOB)
IS
v_len INTEGER;
v_index INTEGER;
v_chunk RAW(32767);
BEGIN
v_len := DBMS_LOB.getlength(p_blob);
v_index := 1;

write_to_log('Starting write_blob: ' || TO_CHAR(v_len) || ' bytes', TRUE);

WHILE v_index <= v_len
LOOP
v_chunk := DBMS_LOB.SUBSTR(p_blob, c_base64_line, v_index);
v_index := v_index + c_base64_line;

UTL_SMTP.write_data(
p_connection,
UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(v_chunk))
);
END LOOP;

write_to_log('End write_blob: ' || TO_CHAR(v_len) || ' bytes', TRUE);
END write_blob;

PROCEDURE send_all(
p_sender IN VARCHAR2,
p_recipients IN VARCHAR2,
p_cc IN VARCHAR2,
p_bcc IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN OUT NOCOPY CLOB,
p_mime_type IN VARCHAR2,
p_priority IN PLS_INTEGER,
p_clob IN OUT NOCOPY CLOB,
p_blob IN OUT NOCOPY BLOB,
p_att_inline IN BOOLEAN,
p_att_mime_type IN VARCHAR2,
p_att_filename IN VARCHAR2,
p_replyto IN VARCHAR2,
p_server_list IN VARCHAR2
)
IS
v_connection UTL_SMTP.connection;
v_address_list DBMS_SQL.varchar2s;
v_reply UTL_SMTP.reply;
BEGIN
v_connection := find_a_server(p_server_list);

v_reply := UTL_SMTP.mail(v_connection, extract_address(p_sender));
write_to_log(
'Mail(' || p_sender || '): ' || TO_CHAR(v_reply.code, 'fm009') || ' ' || v_reply.text
);

IF (p_recipients IS NOT NULL)
THEN
parse_email_list(p_recipients, v_address_list);
END IF;

IF (p_cc IS NOT NULL)
THEN
parse_email_list(p_cc, v_address_list);
END IF;

IF (p_bcc IS NOT NULL)
THEN
parse_email_list(p_bcc, v_address_list);
END IF;

IF v_address_list.COUNT > 0
THEN
FOR i IN 1 .. v_address_list.COUNT
LOOP
v_reply := UTL_SMTP.rcpt(v_connection, v_address_list(i));
write_to_log(
'Rcpt('
|| v_address_list(i)
|| '): '
|| TO_CHAR(v_reply.code, 'fm009')
|| ' '
|| v_reply.text
);
END LOOP;
END IF;

v_reply := UTL_SMTP.open_data(v_connection);
write_to_log('Open Data: ' || TO_CHAR(v_reply.code, 'fm009') || ' ' || v_reply.text);

IF (p_sender IS NOT NULL)
THEN
UTL_SMTP.write_data(v_connection, 'From: ' || p_sender || c_crlf);
ELSE
RAISE invalid_argument;
END IF;

IF (p_recipients IS NOT NULL)
THEN
UTL_SMTP.write_data(v_connection, 'To: ' || p_recipients || c_crlf);
END IF;

IF (p_cc IS NOT NULL)
THEN
UTL_SMTP.write_data(v_connection, 'CC: ' || p_cc || c_crlf);
END IF;

IF (p_replyto IS NOT NULL)
THEN
UTL_SMTP.write_data(v_connection, 'Reply-To: ' || extract_address(p_replyto) || c_crlf);
END IF;

UTL_SMTP.write_data(v_connection, 'Subject: ' || p_subject || c_crlf);

IF p_priority BETWEEN 1 AND 5
THEN
UTL_SMTP.write_data(v_connection, 'X-Priority: ' || p_priority || c_crlf);
ELSE
RAISE invalid_priority;
END IF;

IF p_clob IS NOT NULL OR p_blob IS NOT NULL
THEN
UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || c_crlf);
UTL_SMTP.write_data(v_connection, 'Content-Type: ' || multipart_mime_type || c_crlf);
UTL_SMTP.write_data(v_connection, c_crlf);
UTL_SMTP.write_data(
v_connection,
'This is a multi-part message in MIME format.' || c_crlf
);

IF p_message IS NOT NULL
THEN
attach_clob(
v_connection,
p_message,
p_mime_type,
TRUE,
NULL,
FALSE
);
END IF;

IF p_clob IS NOT NULL
THEN
attach_clob(
v_connection,
p_clob,
p_att_mime_type,
p_att_inline,
p_att_filename,
TRUE
);
END IF;

IF p_blob IS NOT NULL
THEN
attach_blob(
v_connection,
p_blob,
p_att_mime_type,
p_att_inline,
p_att_filename,
TRUE
);
END IF;
ELSE
IF p_mime_type IS NOT NULL
THEN
UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || c_crlf);
UTL_SMTP.write_data(v_connection, 'Content-Type: ' || p_mime_type || c_crlf);
UTL_SMTP.write_data(v_connection, c_crlf);
END IF;

IF p_message IS NOT NULL
THEN
write_clob(v_connection, p_message);
END IF;
END IF;

v_reply := UTL_SMTP.close_data(v_connection);
write_to_log('Close_Data:' || TO_CHAR(v_reply.code, 'fm009') || ' ' || v_reply.text);

v_reply := UTL_SMTP.quit(v_connection);
write_to_log('Quit: ' || TO_CHAR(v_reply.code, 'fm009') || ' ' || v_reply.text);
EXCEPTION
WHEN OTHERS
THEN
IF (v_connection.HOST IS NOT NULL)
THEN
UTL_SMTP.quit(v_connection);
END IF;

RAISE;
END send_all;

PROCEDURE send(
p_sender IN VARCHAR2,
p_recipients IN VARCHAR2,
p_cc IN VARCHAR2 DEFAULT NULL,
p_bcc IN VARCHAR2 DEFAULT NULL,
p_subject IN VARCHAR2 DEFAULT NULL,
p_message IN OUT NOCOPY CLOB,
p_mime_type IN VARCHAR2 DEFAULT 'text/plain;charset=us-ascii',
p_priority IN PLS_INTEGER DEFAULT 3,
p_replyto IN VARCHAR2 DEFAULT NULL,
p_server_list IN VARCHAR2 DEFAULT NULL
)
IS
v_dummyclob CLOB := NULL;
v_dummyblob BLOB := NULL;
BEGIN
send_all(
p_sender,
p_recipients,
p_cc,
p_bcc,
p_subject,
p_message,
p_mime_type,
p_priority,
v_dummyclob,
v_dummyblob,
NULL,
NULL,
NULL,
NULL,
p_server_list
);
END send;

PROCEDURE send_attach_clob(
p_sender IN VARCHAR2,
p_recipients IN VARCHAR2,
p_cc IN VARCHAR2 DEFAULT NULL,
p_bcc IN VARCHAR2 DEFAULT NULL,
p_subject IN VARCHAR2 DEFAULT NULL,
p_message IN OUT NOCOPY CLOB,
p_mime_type IN VARCHAR2 DEFAULT 'text/plain;charset=us-ascii',
p_priority IN PLS_INTEGER DEFAULT 3,
p_attachment IN OUT NOCOPY CLOB,
p_att_inline IN BOOLEAN DEFAULT TRUE,
p_att_mime_type IN VARCHAR2 DEFAULT 'text/plain;charset=us-ascii',
p_att_filename IN VARCHAR2 DEFAULT NULL,
p_replyto IN VARCHAR2 DEFAULT NULL,
p_server_list IN VARCHAR2 DEFAULT NULL
)
IS
v_dummy BLOB := NULL;
BEGIN
send_all(
p_sender,
p_recipients,
p_cc,
p_bcc,
p_subject,
p_message,
p_mime_type,
p_priority,
p_attachment,
v_dummy,
p_att_inline,
p_att_mime_type,
p_att_filename,
p_replyto,
p_server_list
);
END send_attach_clob;

PROCEDURE send_attach_blob(
p_sender IN VARCHAR2,
p_recipients IN VARCHAR2,
p_cc IN VARCHAR2 DEFAULT NULL,
p_bcc IN VARCHAR2 DEFAULT NULL,
p_subject IN VARCHAR2 DEFAULT NULL,
p_message IN OUT NOCOPY CLOB,
p_mime_type IN VARCHAR2 DEFAULT 'text/plain;charset=us-ascii',
p_priority IN PLS_INTEGER DEFAULT 3,
p_attachment IN OUT NOCOPY BLOB,
p_att_inline IN BOOLEAN DEFAULT TRUE,
p_att_mime_type IN VARCHAR2 DEFAULT 'application/octet-stream',
p_att_filename IN VARCHAR2 DEFAULT NULL,
p_replyto IN VARCHAR2 DEFAULT NULL,
p_server_list IN VARCHAR2 DEFAULT NULL
)
IS
v_dummy CLOB := NULL;
BEGIN
send_all(
p_sender,
p_recipients,
p_cc,
p_bcc,
p_subject,
p_message,
p_mime_type,
p_priority,
v_dummy,
p_attachment,
p_att_inline,
p_att_mime_type,
p_att_filename,
p_replyto,
p_server_list
);
END send_attach_blob;

PROCEDURE set_mime_boundary(p_boundary IN VARCHAR2)
IS
BEGIN
g_boundary := p_boundary;
END;

FUNCTION get_mime_boundary
RETURN VARCHAR2
IS
BEGIN
RETURN g_boundary;
END;

FUNCTION multipart_mime_type
RETURN VARCHAR2
IS
BEGIN
RETURN 'multipart/mixed; boundary="' || g_boundary || '"';
END;

PROCEDURE attach_text(
p_connection IN OUT NOCOPY connection,
p_data IN VARCHAR2,
p_mime_type IN VARCHAR2 DEFAULT 'text/plain;charset=us-ascii',
p_inline IN BOOLEAN DEFAULT TRUE,
p_filename IN VARCHAR2 DEFAULT NULL,
p_last IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
begin_mime_block(
p_connection,
p_mime_type,
p_inline,
p_filename
);
UTL_SMTP.write_data(p_connection, p_data);
end_mime_block(p_connection, p_last);
END attach_text;

PROCEDURE attach_clob(
p_connection IN OUT NOCOPY connection,
p_data IN OUT NOCOPY CLOB,
p_mime_type IN VARCHAR2 DEFAULT 'text/plain;charset=us-ascii',
p_inline IN BOOLEAN DEFAULT TRUE,
p_filename IN VARCHAR2 DEFAULT NULL,
p_last IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
begin_mime_block(
p_connection,
p_mime_type,
p_inline,
p_filename
);

write_clob(p_connection, p_data);

end_mime_block(p_connection, p_last);
END attach_clob;

PROCEDURE attach_raw(
p_connection IN OUT NOCOPY connection,
p_data IN RAW,
p_mime_type IN VARCHAR2 DEFAULT 'application/octet-stream',
p_inline IN BOOLEAN DEFAULT TRUE,
p_filename IN VARCHAR2 DEFAULT NULL,
p_last IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
begin_mime_block(
p_connection,
p_mime_type,
p_inline,
p_filename,
'base64'
);

UTL_SMTP.write_raw_data(
p_connection,
UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(p_data))
);

end_mime_block(p_connection, p_last);
END attach_raw;

PROCEDURE attach_blob(
p_connection IN OUT NOCOPY connection,
p_data IN OUT NOCOPY BLOB,
p_mime_type IN VARCHAR2 DEFAULT 'application/octet-stream',
p_inline IN BOOLEAN DEFAULT TRUE,
p_filename IN VARCHAR2 DEFAULT NULL,
p_last IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
begin_mime_block(
p_connection,
p_mime_type,
p_inline,
p_filename,
'base64'
);

write_blob(p_connection, p_data);

end_mime_block(p_connection, p_last);
END attach_blob;
BEGIN
G_BOUNDARY := '--' || RAWTOHEX(SYS_GUID());
END send_mail_attachment_html;

Step4: Execute the below Script to send the Mail with attachment

create or replace PROCEDURE "HTML_WITH_ATTACHMENT"

--L_CONNECTION UTL_SMTP.CONNECTION;

V_BLOB BLOB;

L_HTML VARCHAR2(32767);

V_ERRMSG VARCHAR2(500);

LV_V_SMTP_SERVER_IP_DOMAIN VARCHAR2(100);

LV_N_SMTP_SERVER_PORT NUMBER;

CURSOR C1

sELECT email FROM DUAL;

BEGIN

L_HTML :=

'<html>

<style>

{

margin-left;

}

</style>

<head>

<title>Password Protection Intimation Mail</title>

</head>

<body>

</p>

</center>

</p>

<br>

</body>

</html>'

;

SELECT IMAGE INTO V_BLOB FROM IMAGE_STORE1 WHERE ROWNUM <2;

FOR I IN C1

LOOP

BEGIN

SEND_MAIL_ATTACHMENT_HTML.SEND_ATTACH_BLOB( P_SENDER =>'testing@testing.in',

P_RECIPIENTS => 'testing@testing.in',

P_CC => '',

P_BCC => '',

P_SUBJECT => 'subject',

P_MESSAGE => L_HTML,

P_MIME_TYPE => 'text/html;charset=us-ascii',

P_ATTACHMENT => V_BLOB,

P_ATT_INLINE => TRUE,

P_ATT_FILENAME => 'filename',

P_SERVER_LIST =>LV_V_SMTP_SERVER_IP_DOMAIN||':'||LV_N_SMTP_SERVER_PORT );

EXCEPTION

WHEN OTHERS THEN

V_ERRMSG:=NULL;

V_ERRMSG:=SQLERRM;

END;

END LOOP;

END;

Script to Load file from Directory to Blob column

create or replace PROCEDURE load_file(

pi_id IN INTEGER,

pfname IN VARCHAR2)

SRC_FILE bfile;

dst_file BLOB:= EMPTY_BLOB();

lgh_file BINARY_INTEGER;

BEGIN

src_file := bfilename('dir', pfname);

INSERT

INTO image_store

(id_pk,fname,image)

VALUES

(pi_id,pfname,EMPTY_BLOB())

RETURNING image

INTO dst_file;

DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);

lgh_file := dbms_lob.getlength(src_file);

DBMS_LOB.LOADFROMFILE(DST_FILE, SRC_FILE, LGH_FILE);

--DBMS_LOB.FREETEMPORARY(dst_file);-- ADDED BY RAJAN

COMMIT;

dbms_lob.close(src_file);

END load_file;

Thursday, 27 September 2018

Oracle 18c- From a Developer's Perspective

Oracle 18C :

1. Why 18c from 12c?

2. 18c Key features from Developer’s Stand Point

-- PL/SQL & SQL Related Enhancements

*Private Temporary Tables

*Approximate Top-N Query Processing

*Inline External Tables

*Polymorphic Tables

*PL/SQL Qualified Expressions

*Not Persistable Types

*ROUND_TIES_TO_EVEN Function

-- Query Execution & Tuning Related Enhancements

*Manual Termination of Runaway Queries

*PL/SQL DBMS HPROF Procedure Improvements

*OCI Call Time Out Attribute

*Spatial Improvements

*Graph Improvements

--JSON Related Enhancements

*SODA for PL/SQL

*Support Indexing of JSON Key Names Longer than 64 Characters

*New SQL Function TO_UTC_TIMESTAMP_TZ

*JSON IS_EQUAL

*TREAT(...AS JSON)

*JSON_TABLE Materialized Views : ON STATEMENT Support

*JSON_DATAGUIDE Function Enhancements- Formatting & Hierarchy

*{USER|ALL|DBA|CDB}_JSON_DATAGUIDE_FIELDS Views

*JSON Dataguide Records for Arrays of Scalar Values

*Data-Dictionary Views to Record the Presence of JSON Columns

*JSON Path Expression Item Methods

*LOB Return Types for JSON_VALUE, JSON_QUERY, and JSON_TABLE Functions

*LOB Support for SQL/JSON Generation Functions

1. Why 18c from 12c?

While it may seem to customers that Oracle has apparently skipped 6 releases of Oracle , it may be simpler to regard “Oracle Database 18c” as “Oracle Database 12c Release 2 12.2.0.2”, where they have simply changed the naming to reflect the year in which the product is released(2018).

Oracle has decided to release a new version every year and quarterly updates , to adapt to the agile model. (Next year release of Oracle would be 19c etc.)

What’s the benefit?:

Oracle’s change to annual releases addresses both the delay in getting new features out, as well as the amount of changes introduced. This should enable developers and DBAs to rapidly adopt the new Releases, enabling new and innovative solutions.

2. Oracle 18c Key Features - From a Developer’s Stand Point

The following features are the key changes a developer would need to know , when upgrading to Oracle 18c.

3.3.1 Private Temporary Tables:

Private Temporary table is a new concept in Oracle in 18c, where the table object itself is temporary & Session specific, not just the data.

Private temporary tables are temporary database objects that are automatically dropped at the end of a transaction or a session.

A private temporary table is stored in memory and is visible only to the session that created it.

A private temporary table confines the scope of a temporary table to a session or a transaction, thus providing more flexibility in application coding, leading to easier code maintenance and a better ready-to-use functionality.

Temporary, private (session-based) table definition

– Private table name and shape

- Temporary, private (session-based) data content

– Session or transaction duration

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (

id NUMBER,

description VARCHAR2(20)

)

ON COMMIT DROP DEFINITION;

-- Insert, but don't commit, then check contents of PTT.

INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE');

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

COUNT(*)

--------------

-- Reconnect and check contents of GTT.

CONN test/test@pdb1

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

SELECT COUNT(*) FROM ora$ptt_my_temp_table

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

----------------------------------------------------------------------------------------------------------------

It doesn't make sense for a permanent PL/SQL object to directly reference a temporary object, as it would not exist at compile time.

If you want to use a private temporary table from a permanent object it would have to be done using dynamic SQL.

Example:

CREATE OR REPLACE FUNCTION ptt_test (p_id IN NUMBER)

RETURN VARCHAR2

l_sql VARCHAR2(32767);

l_return VARCHAR2(30);

BEGIN

l_sql := 'CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (

id NUMBER,

description VARCHAR2(20)

)

ON COMMIT DROP DEFINITION';

EXECUTE IMMEDIATE l_sql;

EXECUTE IMMEDIATE q'[INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE')]';

EXECUTE IMMEDIATE 'SELECT description INTO :l_return FROM ora$ptt_my_temp_table WHERE id = :id' INTO l_return USING p_id;

RETURN l_return;

END;

***********************************************************************

3.3.2 Approximate Top-N Query Processing:

" What are the top five products sold by week for the past year?

• Who are the top five earners by region?

• How many page views did the top five blog posts get last week?

• How much did my top fifty customers each spend last year?"

• New approximate functions APPROX_COUNT(), APPROX_SUM(), APPROX_RANK()

Data analysis applications heavily use aggregate functions. Approximate query processing (available since Oracle Database 12c Release 1) aims to

deliver faster results for these queries. The approximate results are not identical to the exact results but they are very close.

New approximate SQL functions for rank, sum and count are now available for Top-N style queries.

By making use of approximate query processing, you can instantly improve the performance of existing analytic workloads and enable faster ad-hoc data exploration.

***********************************************************************

3.3.3 Inline External Tables:

Inline external tables enable the run time definition of an external table to be part of a SQL statement, thus eliminating the need to create an external table as a persistent database object in the data dictionary.

Inline external tables remove the need for explicitly creating external tables to access data outside an Oracle database.

This simplifies accessing external data, and enables developing simpler and efficient database applications.

External table definition provided at run time

– Similar to in-line view

- No need to pre-create external tables that are used one time only

– Increased developer productivity

Before 18c:

CREATE TABLE sales_xt

(prod_id number, … )

TYPE ORACLE_LOADER

…

LOCATION ’new_sales_kw13')

REJECT LIMIT UNLIMITED );

INSERT INTO sales SELECT * FROM sales_xt;

DROP TABLE sales_xt;

After 18c:

INSERT INTO sales

SELECT sales_xt.*

FROM EXTERNAL(

(prod_id number, … )

TYPE ORACLE_LOADER

…

LOCATION ’new_sales_kw13')

REJECT LIMIT UNLIMITED );

***********************************************************************

3.3.4 Polymorphic Table Functions:

Polymorphic table functions (PTF) are table functions whose operands can have more than one type. The return type is determined by the PTF invocation arguments list.

These user defined Polymorphic Table Functions (PTF) can be invoked in the FROM clause of a SQL query block.

The PTF Functions are used to embed sophisticated algorithms in SQL ,hide the implementation of the algorithm and leverage powerful dynamic features of PL/SQL.

A PTF has two parts:

1. The PL/SQL package which contains the client interface for the PTF implementation.

2. The standalone or package function naming the PTF and its associated implementation package.

Steps to Implement a Polymorphic Table Function

1. Create the implementation package containing the DESCRIBE function (required) and the OPEN, FETCH_ROWS , and CLOSE procedures (optional). This is called PTF Client Interface.

Example :

An example of how to use polymorphic table functions in 18c to dynamically convert CSV data to columns.

To create a polymorphic table function (PTF), you need to create a package with a describe function. This defines new columns in the output. Fetch_rows is optional. You use this to set values for new columns.

create or replace package csv_pkg as

/* The describe function defines the new columns */

function describe (

tab in out dbms_tf.table_t,

col_names varchar2

) return dbms_tf.describe_t;

/* Fetch_rows sets the values for the new columns */

procedure fetch_rows (col_names varchar2);

end csv_pkg;

Describe uses the col_names to define how many columns to add to the output and their name. Fetch_rows then extracts the values from the source string to the new columns.

create or replace package body csv_pkg as

function describe(

tab in out dbms_tf.table_t,

col_names varchar2

)

return dbms_tf.describe_t as

new_cols dbms_tf.columns_new_t;

col_id pls_integer := 2;

begin

/* Enable the source column for reading */

tab.column(1).pass_through := FALSE;

tab.column(1).for_read := TRUE;

new_cols(1) := tab.column(1).description;

/* Extract the column names from the header string,

creating a new column for each

for j in 1 .. ( length(col_names) - length(replace(col_names,',')) ) + 1 loop

new_cols(col_id) := dbms_tf.column_metadata_t(

name=>regexp_substr(col_names, '[^,]+', 1, j),--'c'||j,

type=>dbms_tf.type_varchar2

);

col_id := col_id + 1;

end loop;

return dbms_tf.describe_t( new_columns => new_cols );

end;

procedure fetch_rows (col_names varchar2) as

rowset dbms_tf.row_set_t;

row_count pls_integer;

begin

/* read the input data set */

dbms_tf.get_row_set(rowset, row_count => row_count);

/* Loop through the input rows... */

for i in 1 .. row_count loop

/* ...and the defined columns, extracting the relevant value

start from 2 to skip the input string

for j in 2 .. ( length(col_names) - length(replace(col_names,',')) ) + 2 loop

rowset(j).tab_varchar2(i) :=

regexp_substr(rowset(1).tab_varchar2(i), '[^,]+', 1, j - 1);

end loop;

/* Output the new columns and their values */

dbms_tf.put_row_set(rowset);

end;

end csv_pkg;

2. The below function is the interface to the implementation package. The first argument is the name of the table that is the source data set. The second is a comma separated list of the names of the new columns.

create or replace function csv_to_columns( tab table, col_names varchar2 )

return table pipelined row polymorphic using csv_pkg;

3. You can now pass a table with CSV data and the names of the new columns you want. The PTF will split these out.

with csvs as
( select 'two,comma-separated values' str from dual )

select * from csv_to_columns( csvs, 'c1,c2' );

STR	C1	C2
two,comma-separated values	two	comma-separated values

***********************************************************************

3.3.5 PL/SQL Qualified Expressions:

Oracle 18c makes it easier to provide complex values when initializing data.

Qualified Expressions are used to provide values when creating either RECORD Type or Associative Array (PL/SQL Table) easily.

Qualified expressions improve program clarity and developer productivity by providing the ability to declare and define a complex value in a compact form where the value is needed.

Example :

DECLARE

TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);

l_tab t_tab;

BEGIN

-- Pre-18c - Direct assignment to record columns.

l_tab('IND1') := 'ONE';

l_tab('IND2') := 'TWO';

l_tab('IND3') := 'THREE';

-- 18c - Qualified expression using named association.

l_tab := t_tab('IND1' => 'ONE',

'IND2' => 'TWO',

'IND3' => 'THREE');

END;

****************************************************************

DECLARE

TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);

PROCEDURE dummy (p_tab IN t_tab DEFAULT t_tab(1 => 'ONE', 2 => 'TWO',3 => 'THREE')) AS

BEGIN

NULL;

END;

BEGIN

NULL;

END;

***********************************************************************

3.3.6 Not Persistable Types:

Use [NOT] PERSISTABLE clause to indicate whether or not instances of the object type are persistable.

Only PERSISTABLE types can be stored in a table.

Oracle 18C provides the ability to mark TYPEs as NOT PERSISTABLE to make sure that unsuitable data types are not persisted to disk.

Marking a TYPE as NOT PERSISTABLE makes upgrades smoother and reduces errors.

Example:

--Default Type-> PERSISTABLE

CREATE TYPE ct_phone_list_type1 as VARRAY(8) of VARCHAR2(15);

CREATE TYPE ct_address_type1 as OBJECT (street_address_1 VARCHAR2(40),street_address_2 VARCHAR2(40), postal_code VARCHAR2(10),city VARCHAR2(10),state_province VARCHAR2(10),country_code CHAR(2),phone ct_phone_list_type1);

--Type Created

--NOT PERSISTABLE

CREATE TYPE ct_phone_list_type2 as VARRAY(8) of (VARCHAR2(15)) NOT PERSISTABLE;

CREATE TYPE ct_address_type2 as OBJECT (street_address_1 VARCHAR2(40),street_address_2 VARCHAR2(40), postal_code VARCHAR2(10),city VARCHAR2(10),state_province VARCHAR2(10),country_code CHAR(2),phone ct_phone_list_type2);

--TYPE - ct_address_type2

--ORA-22383 a persistable type cannot have non-persistable attributes

***********************************************************************

3.3.7 Manual termination of run-away queries:

A SQL query consuming excessive resources, including parallel servers, can be terminated using the ALTER SYSTEM CANCEL SQL statement.

This feature enables the database administrator to terminate a runaway SQL query using a SQL statement.

This frees the system resources consumed by the runaway SQL query so that other SQL queries can use them.

ALTER SYSTEM CANCEL SQL ‘sid,serial#’

/* optional parameters (within quotes) */

@instance_id

Sql_id

***********************************************************************

3.3.8 PL/SQL DBMS HPROF Procedure:

What is DBMS_HPROF?

The dbms_hprof PL/SQL built-in package and related dbmshp_parent_child_info table are used to help plot the execution and debugging of PL/SQL, revealing the hierarchy of calls to other PL/SQL functions.

What’s new in 18c about DBMS_HPROF ?

Starting with Oracle Database 18c, a new DBMS_HPROF procedure creates necessary tables and structures to collect and analyse raw profiler output as an alternative to the raw profiler data file.

A PL/SQL performance engineer with 'INSERT', 'CREATE', and 'SELECT' privileges can record profiler-run information into the database tables and generate reports with tools such as SQL*Plus or SQL Developer.

The engineer need not have additional file permissions to access the script files located in operating system directories.

Overview & Implementation:

You can use the PL/SQL hierarchical profiler to identify bottlenecks and performance-tuning opportunities in PL/SQL applications.

The profiler reports the dynamic execution profile of a PL/SQL program organized by function calls, and accounts for SQL and PL/SQL execution times separately.

To collect profile data from your PL/SQL program for the PL/SQL hierarchical profiler, follow these steps:

* Ensure that you have these privileges:

1. EXECUTE privilege on the DBMS_HPROF package

2. WRITE privilege on the directory that you specify when you call DBMS_HPROF.START_PROFILING

* Use the DBMS_HPROF.START_PROFILING PL/SQL API to start hierarchical profiler data collection in a session.

* Run your PL/SQL program long enough to get adequate code coverage.

* To get the most accurate measurements of elapsed time, avoid unrelated activity on the system on which your PL/SQL program is running.

*Use the DBMS_HPROF.STOP_PROFILING PL/SQL API to stop hierarchical profiler data collection.

Sample Code for DBMS_HPROF:

Consider this PL/SQL procedure, test:

CREATE OR REPLACE PROCEDURE test AUTHID DEFINER IS

n NUMBER;

PROCEDURE foo IS

BEGIN

SELECT COUNT(*) INTO n FROM EMPLOYEES;

END foo;

BEGIN -- test

FOR i IN 1..3 LOOP

foo;

END LOOP;

END test;

Consider the PL/SQL procedure that analyzes and generates HTML CLOB report from raw profiler data table

declare

reportclob clob ;

trace_id number;

begin

-- create raw profiler data and analysis tables

-- force_it =>TRUE will dropped the tables if table existed

DBMS_HPROF . CREATE_TABLES (force_it =>TRUE );

-- Start profiling .

-- Write raw profiler data in raw profiler data table

trace_id := DBMS_HPROF . START_PROFILING ;

-- Run procedure to be profiled

test ;

-- Stop profiling

DBMS_HPROF . STOP_PROFILING ;

-- analyzes trace_id entry in raw profiler data table and produce

-- analyzed HTML report in reportclob .

DBMS_HPROF .ANALYZE (trace_id , reportclob );

end;

Overview of PL/SQL Hierarchical Profiler

Non hierarchical (flat) profilers record the time that a program spends within each subprogram—the function time or self time of each subprogram. Function time is helpful, but often inadequate. For example, it is helpful to know that a program spends 40% of its time in the subprogram INSERT_ORDER, but it is more helpful to know which subprograms call INSERT_ORDER often and the total time the program spends under INSERT_ORDER (including its descendant subprograms). Hierarchical profilers provide such information.

The PL/SQL hierarchical profiler:

l Reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls

l Accounts for SQL and PL/SQL execution times separately

l Requires no special source or compile-time preparation

l Stores results in database tables (hierarchical profiler tables) for custom report generation by integrated development environment (IDE) tools (such as SQL Developer and third-party tools)

l Provides subprogram-level execution summary information, such as:

Number of calls to the subprogram

Time spent in the subprogram itself (function time or self time)

Time spent in the subprogram itself & in its descendent subprograms (subtree time)

The PL/SQL hierarchical profiler is implemented by the DBMS_HPROF package and has two components:

Data collection

The data collection component is an intrinsic part of the PL/SQL Virtual Machine. The DBMS_HPROF package provides APIs to turn hierarchical profiling on and off and write the raw profiler output to a file or raw profiler data table.

Analyzer

The analyzer component processes the raw profiler output and produce analyzed results. The analyzer component analyzes:

Raw profiler data located in the raw profiler data file and raw profiler data table into HTML CLOB report, analyzed report file, and hierarchical profiler analysis tables.

Example Implementation in Detail :

https://docs.oracle.com/en/database/oracle/oracle-database/18/adfns/hierarchical-profiler.html#GUID-B927E682-0B0C-46E3-B011-B43FF7F156C0

***********************************************************************

3.3.9 OCI Call Time Out Attribute :

This feature introduces a new attribute allowing OCI applications to specify a millisecond time out value for round trip calls to the database.

Gives applications fine-grained control, allowing them to avoid unexpected amounts of time for OCI calls such as statement execution.

The Oracle Call Interfaces (OCI) is a set of low-level APIs (Application Programming Interface Calls) used to interact with the Oracle Database.

It allows one to use operations like log on, execute, parse, fetch, etc. OCI programs are normally written in C or C++,

although they can be written in almost any programming language.

OCI offers a procedural API for not only performing certain database administration tasks (such as system start up and shutdown), but also for using PL/SQL or SQL to query, access, and manipulate data.

ODBC Loader etc is based on OCI

***********************************************************************

3.3.10 SODA for PL/SQL : - Simple Oracle Document Access

SODA for PL/SQL is a PL/SQL API that implements Simple Oracle Document Access (SODA).

You can use it with PL/SQL to perform create, read (retrieve), update, and delete (CRUD) operations on documents of any kind,

and you can use it to query JSON documents.

SODA is a set of NoSQL-style APIs that let you create and store collections of documents in Oracle Database, retrieve them,

and query them, without needing to know Structured Query Language (SQL) or how the data in the documents is stored in the database.

Oracle relational database management system (RDBMS) supports storing and querying JSON data.

To access this functionality, you need structured query language (SQL) with special JSON SQL operators.

SODA for PL/SQL hides the complexities of SQL/JSON programming.

Sample Implementation

This example creates collection myCollectionName with the default metadata.

DECLARE

collection SODA_Collection_T;

BEGIN

collection := DBMS_SODA.create_collection('myCollectionName');

END;

This example uses PL/SQL function DBMS_SODA.open_collection to open the collection named myCollectionName and returns a SODA_COLLECTION_T instance that represents this collection. If the value returned is NULL then there is no existing collection named myCollectionName.

DECLARE

collection SODA_COLLECTION_T;

BEGIN

collection := DBMS_SODA.open_collection('myCollectionName');

END;

This example uses DBMS_SODA.open_collection to try to open an existing collection named myCollectionName. It prints a message if no such collection exists.

DECLARE

collection SODA_COLLECTION_T;

BEGIN

collection := DBMS_SODA.open_collection('myCollectionName');

IF collection IS NULL THEN

DBMS_OUTPUT.put_line('Collection does not exist');

END IF;

END;

Detailed Implementation and Use Cases are described in the attached document.

***********************************************************************

3.3.11 Support Indexing of JSON Key Names Longer than 64 Characters:

* The upper limit is increased for JSON key names that can be indexed by the JSON Search index.

* The JSON key name upper limit in Oracle Database 18c is 255 bytes. In previous releases, JSON search indexes that were created did not index key names greater than 64 bytes.

* Key names greater than 64 bytes are quite common when object serializers, such as GSON and JACKSON, are used to serialize Java Hash maps, such as JSON. Operations on JSON Path expressions containing JSON Key names up to 255 characters long can now be optimized by the JSON Search Index.

* Raising the limit on the size of the key name for indexing increases the efficiency of searching JSON documents generated from HASH MAP-like structures.

***********************************************************************

3.3.12 New SQL Function TO_UTC_TIMESTAMP_TZ:

SQL function to_UTC_timestamp_tz takes as input an ISO 8601 date format string and returns an instance of SQL data type TIMESTAMP WITH TIMEZONE.

This new function has been introduced ,to be able to comply with ISO 8601 standard.

It normalizes the input to UTC time (Coordinated Universal Time, formerly Greenwich Mean Time).

Unlike SQL function to_timestamp_tz, the new function assumes that the input string uses the ISO 8601 date format, defaulting the time zone to UTC 0.

Although JSON doesn't care what format you use for dates and timestamp,

Java Script typically converts dates and time stamps to strings conforming to the ISO 8601 standard, so this has become common in JSON data.

Example :

select TO_UTC_TIMESTAMP_TZ(sysdate) from dual;

TO_UTC_TIMESTAMP_TZ(SYSDATE)

18-MAY-15 12.00.00.000000 AM +00:00

***********************************************************************

JSON Improvements in 18C:

3.3.13 JSON_EQUAL:

The JSON_EQUAL condition allows you to compare two JSON documents without worrying about member order or formatting.

Example:

The example below requires the following test table. Each row contains two JSON documents that will be compared.

CREATE TABLE json_equal_tab (

id NUMBER NOT NULL,

data1 VARCHAR2(50),

data2 VARCHAR2(50),

CONSTRAINT json_equal_tab_pk PRIMARY KEY (id),

CONSTRAINT json_equal_tab_json1_chk CHECK (data1 IS JSON),

CONSTRAINT json_equal_tab_json2_chk CHECK (data2 IS JSON)

);

-- Matching empty JSON.

INSERT INTO json_equal_tab VALUES (1, '{}', '{}');

-- Matching members, order and format.

INSERT INTO json_equal_tab VALUES (2, '{"name1":"value1","name2":"value2"}', '{"name1":"value1","name2":"value2"}');

-- Matching members and order, but differing format.

INSERT INTO json_equal_tab VALUES (3, '{"name1":"value1","name2":"value2"}', '{ "name1":"value1", "name2":"value2" }');

-- Matching members, but differing order.

INSERT INTO json_equal_tab VALUES (4, '{"name1":"value1","name2":"value2"}', '{"name2":"value2","name1":"value1"}');

-- Differing members.

INSERT INTO json_equal_tab VALUES (5, '{"name1":"value1","name2":"value2"}', '{"name2":"value2","name3":"value3"}');

-- Differing members.

INSERT INTO json_equal_tab VALUES (6, '{"name1":"value1","name2":"value2"}', '{"name2":"value2"}');

-- Duplicate members.

INSERT INTO json_equal_tab VALUES (7, '{"name1":"value1"}', '{"name1":"value1","name1":"value1"}');

COMMIT;

Regular String Comparison Results - Pre 18c:

If you do a regular string comparisons between two JSON documents they will only be seen as matching if all the members are in the same order and there is no difference in formatting. We can see this using the following two queries.

SELECT id,

data1,

data2

FROM json_equal_tab

WHERE data1 = data2

ORDER BY 1;

ID DATA1 DATA2

---------- ---------------------------------------- ----------------------------------------

1 {} {}

2 {"name1":"value1","name2":"value2"} {"name1":"value1","name2":"value2"}

SQL>

SELECT id,

data1,

data2

FROM json_equal_tab

WHERE data1 != data2

ORDER BY 1;

ID DATA1 DATA2

---------- ---------------------------------------- ----------------------------------------

3 {"name1":"value1","name2":"value2"} { "name1":"value1", "name2":"value2" }

4 {"name1":"value1","name2":"value2"} {"name2":"value2","name1":"value1"}

5 {"name1":"value1","name2":"value2"} {"name2":"value2","name3":"value3"}

6 {"name1":"value1","name2":"value2"} {"name2":"value2"}

7 {"name1":"value1"} {"name1":"value1","name1":"value1"}

18c Result with JSON_EQUAL:

SELECT id,

data1,

data2

FROM json_equal_tab

WHERE JSON_EQUAL(data1, data2)

ORDER BY 1;

ID DATA1 DATA2

---------- ---------------------------------------- ----------------------------------------

1 {} {}

2 {"name1":"value1","name2":"value2"} {"name1":"value1","name2":"value2"}

3 {"name1":"value1","name2":"value2"} { "name1":"value1", "name2":"value2" }

4 {"name1":"value1","name2":"value2"} {"name2":"value2","name1":"value1"}

7 {"name1":"value1"} {"name1":"value1","name1":"value1"}

SQL>

SELECT id,

data1,

data2

FROM json_equal_tab

WHERE NOT JSON_EQUAL(data1, data2)

ORDER BY 1;

ID DATA1 DATA2

---------- ---------------------------------------- ----------------------------------------

5 {"name1":"value1","name2":"value2"} {"name2":"value2","name3":"value3"}

6 {"name1":"value1","name2":"value2"} {"name2":"value2"}

***********************************************************************

3.3.14 TREAT(...AS JSON):

In Oracle 18c the TREAT function can be used in SQL to change the declared type of an expression to JSON, where the expression is an object containing JSON data.

Example:

Create a test table containing some JSON data. Notice there is no IS JSON constraint on the column holding the JSON data.

CREATE TABLE json_documents (

id RAW(16) NOT NULL,

data CLOB,

CONSTRAINT json_documents_pk PRIMARY KEY (id)

);

INSERT INTO json_documents VALUES (SYS_GUID(), '{"name":"banana", "quantity":10}');

INSERT INTO json_documents VALUES (SYS_GUID(), '{"name":"apple", "quantity":5}');

INSERT INTO json_documents VALUES (SYS_GUID(), '{"name":"orange", "quantity":7}');

COMMIT;

Without the IS JSON constraint on the column, if we try to query the column and traverse the JSON data inside it using dot notation the query fails, as the data is not seen as JSON data by SQL.

SELECT j.data.name,

j.data.quantity

FROM json_documents j

ORDER BY 1;

j.data.quantity

ERROR at line 2:

ORA-00904: "J"."DATA"."QUANTITY": invalid identifier

In the following example we use the TREAT(... AS JSON) function inside an inline view to tell SQL the contents of the column is JSON.

This allows the outer query to use dot notation, even though the original column doesn't have the IS JSON check constraint.

The TREAT(... AS JSON) function works equally well in a view.

SELECT jd.id,

jd.json_data.name,

jd.json_data.quantity

FROM (SELECT id,

TREAT(data AS JSON) AS json_data

FROM json_documents) jd

ORDER BY 2;

ID NAME QUANTITY

-------------------------------- --------------- ---------------

6943671DB33E4A21E05336BB1C0A4E06 apple 5

6943671DB33D4A21E05336BB1C0A4E06 banana 10

6943671DB33F4A21E05336BB1C0A4E06 orange 7

***********************************************************************

3.3.15 JSON_TABLE Materialized Views : ON STATEMENT Support:

What is On Statement Refresh ??

Specify ON STATEMENT in Refresh Definition for a Materialized View to indicate that a fast refresh is to occur whenever DML happens on a base table of the materialized view. This is to say, ON STATEMENT materialized view is always in sync with base table changes even before the transaction commits. If a transaction that made changes to the base tables rolls back, the corresponding changes in on statement MV are rolled back as well.

In previous database versions we were able to create materialized views containing the JSON_TABLE function using the ON DEMAND and ON COMMIT clauses, but attempting to use the ON STATEMENT clause resulted in the following error.

ORA-32428: on-statement materialized join view error: Shape of MV is not supported

This restriction has been lifted in Oracle 18c.

Example :

CREATE MATERIALIZED VIEW json_documents_v

REFRESH FAST

ON STATEMENT

SELECT id, jt.*

FROM json_documents,

JSON_TABLE(data, '$'

COLUMNS (FirstName, LastName, Job, Active,

NESTED ContactDetails COLUMNS (Email, Phone)

)

) jt;

Query the data from the materialized view to check it is working as expected.

SET LINESIZE 120

COLUMN firstname FORMAT A10

COLUMN lastname FORMAT A10

COLUMN email FORMAT A23

COLUMN phone FORMAT A13

SELECT * FROM json_documents_v;

ID FIRSTNAME LASTNAME JOB ACTIVE EMAIL PHONE

-------------------------------- ---------- ---------- ---------- ------ ----------------------- -------------

696878605B4F4508E05336BB1C0A6517 John Doe Clerk true john.doe@example.com 44 123 123456

Add a new row but don't commit it. You will see the new row is present in the materialized view.

INSERT INTO json_documents (id, data)

VALUES (SYS_GUID(),

"FirstName" : "Mary",

"LastName" : "Doe",

"Job" : "President",

"Address" : {

"Street" : "101 My Street",

"City" : "My City",

"Country" : "UK",

"Postcode" : "A12 34B"

"ContactDetails" : {

"Email" : "mary.doe@example.com",

"Phone" : "44 123 234567",

"Twitter" : "@marydoe"

"DateOfBirth" : "01-JAN-1980",

"Active" : true

}');

SELECT * FROM json_documents_v;

ID FIRSTNAME LASTNAME JOB ACTIVE EMAIL PHONE

-------------------------------- ---------- ---------- ---------- ------ ----------------------- -------------

696878605B4F4508E05336BB1C0A6517 John Doe Clerk true john.doe@example.com 44 123 123456

696878605B514508E05336BB1C0A6517 Mary Doe President true mary.doe@example.com 44 123 234567

Rollback the insert and check the materialized view again. The row is no longer present.

ROLLBACK;

SELECT * FROM json_documents_v;

ID FIRSTNAME LASTNAME JOB ACTIVE EMAIL PHONE

-------------------------------- ---------- ---------- ---------- ------ ----------------------- -------------

696878605B4F4508E05336BB1C0A6517 John Doe Clerk true john.doe@example.com 44 123 123456

***********************************************************************

3.3.16 JSON_DATAGUIDE Function Enhancements- Formatting & Hierarchy :

A data guide is a summary of the structural and type information contained in a set of JSON documents. It records meta data about the fields used in those documents.

In Oracle 12.2 the JSON_DATAGUIDE function returned a flat formatted and minimized data guide.

In Oracle 18c the JSON_DATAGUIDE function can return the flat or hierarchical data guide, and display in pretty print using new optional parameters.

Example:

Create and populate the following table to provide some JSON data to work with.

For the data guide functionality to work the table must include the IS JSON check constraint on the column holding the JSON data.

CREATE TABLE json_documents (

id RAW(16) NOT NULL,

data CLOB,

CONSTRAINT json_documents_pk PRIMARY KEY (id),

CONSTRAINT json_documents_json_chk CHECK (data IS JSON)

);

INSERT INTO json_documents (id, data)

VALUES (SYS_GUID(),

"FirstName" : "John",

"LastName" : "Doe",

"Job" : "Clerk",

"Address" : {

"Street" : "99 My Street",

"City" : "My City",

"Country" : "UK",

"Postcode" : "A12 34B"

"ContactDetails" : {

"Email" : "john.doe@example.com",

"Phone" : "44 123 123456",

"Twitter" : "@johndoe"

"DateOfBirth" : "01-JAN-1980",

"Active" : true

}');

INSERT INTO json_documents (id, data)

VALUES (SYS_GUID(),

"FirstName" : "Jayne",

"LastName" : "Doe",

"Job" : "Manager",

"Address" : {

"Street" : "100 My Street",

"City" : "My City",

"Country" : "UK",

"Postcode" : "A12 34B"

"ContactDetails" : {

"Email" : "jayne.doe@example.com",

"Phone" : ""

"DateOfBirth" : "01-JAN-1982",

"Active" : false

}');

COMMIT;

12.2 Result :

SELECT JSON_DATAGUIDE(data) dg_doc

FROM json_documents;

DG_DOC

--------------------------------------------------------------------------------

[{"o:path":"$.Job","type":"string","o:length":8},{"o:path":"$.Active","type":"

boolean","o:length":8},{"o:path":"$.Address","type":

"object","o:length":128},{"o:path":"$.Address.City",

"type":"string","o:length":8},{"o:path":"$.Address.S

treet","type":"string","o:length":16},{"o:path":"$.A

ddress.Country","type":"string","o:length":2},{"o:pa

th":"$.Address.Postcode","type":"string","o:length":

8},{"o:path":"$.LastName","type":"string","o:length"

:4},{"o:path":"$.FirstName","type":"string","o:lengt

h":8},{"o:path":"$.DateOfBirth","type":"string","o:l

ength":16},{"o:path":"$.ContactDetails","type":"obje

ct","o:length":128},{"o:path":"$.ContactDetails.Emai

l","type":"string","o:length":32},{"o:path":"$.Conta

ctDetails.Phone","type":"string","o:length":16},{"o:

path":"$.ContactDetails.Twitter","type":"string","o:

length":8}]

18.1 Result:

SELECT JSON_DATAGUIDE(data, DBMS_JSON.format_hierarchical, DBMS_JSON.pretty) dg_doc

FROM json_documents;

DG_DOC

--------------------------------------------------------------------------------

{

"type" : "object",

"properties" :

{

"Job" :

{

"type" : "string",

"o:length" : 8,

"o:preferred_column_name" : "Job"

"Active" :

{

"type" : "boolean",

"o:length" : 8,

"o:preferred_column_name" : "Active"

"Address" :

{

"type" : "object",

"o:length" : 128,

"o:preferred_column_name" : "Address",

"properties" :

{

"City" :

{

"type" : "string",

"o:length" : 8,

"o:preferred_column_name" : "City"

"Street" :

{

"type" : "string",

"o:length" : 16,

"o:preferred_column_name" : "Street"

"Country" :

{

"type" : "string",

"o:length" : 2,

"o:preferred_column_name" : "Country"

"Postcode" :

{

"type" : "string",

"o:length" : 8,

"o:preferred_column_name" : "Postcode"

}

"LastName" :

{

"type" : "string",

"o:length" : 4,

"o:preferred_column_name" : "LastName"

"FirstName" :

{

"type" : "string",

"o:length" : 8,

"o:preferred_column_name" : "FirstName"

"DateOfBirth" :

{

"type" : "string",

"o:length" : 16,

"o:preferred_column_name" : "DateOfBirth"

"ContactDetails" :

{

"type" : "object",

"o:length" : 128,

"o:preferred_column_name" : "ContactDetails",

"properties" :

{

"Email" :

{

"type" : "string",

"o:length" : 32,

"o:preferred_column_name" : "Email"

"Phone" :

{

"type" : "string",

"o:length" : 16,

"o:preferred_column_name" : "Phone"

"Twitter" :

{

"type" : "string",

"o:length" : 8,

"o:preferred_column_name" : "Twitter"

}

***********************************************************************

3.3.17 {USER|ALL|DBA|CDB}_JSON_DATAGUIDE_FIELDS Views:

In Oracle 12.2 if you wanted to display the fields present in the data guide in a relational format you had to combine the data guide with JSON_TABLE to explore the output, as shown here.

In Oracle 18c the {USER|ALL|DBA|CDB}_JSON_DATAGUIDE_FIELDS views have been added to simplify displaying this field information.

SELECT path,

type,

length

FROM user_json_dataguide_fields

WHERE table_name = 'JSON_DOCUMENTS'

AND column_name = 'DATA'

ORDER BY 1;

PATH TYPE LENGTH

---------------------------------------- ---------- ---------- ------- -------------

$.Active boolean 8

$.Address object 128

$.Address.City string 8

$.Address.Country string 2

$.Address.Postcode string 8

$.Address.Street string 16

$.ContactDetails object 128

$.ContactDetails.Email string 32

$.ContactDetails.Phone string 16

$.ContactDetails.Twitter string 8

$.DateOfBirth string 16

$.FirstName string 8

$.Job string 8

$.LastName string 4

***********************************************************************

3.3.18 JSON Dataguide Records for Arrays of Scalar Values:

When a JSON document contains an array of scalar values the data guide now records both the array itself and the scalar type of the array.

INSERT INTO json_documents (id, data)

VALUES ( SYS_GUID(),

"element1" : [1,2,3,4,5],

"element2" : ["one","two","three"]

}' );

COMMIT;

The data guide is automatically updated, so we can query the field information to see the impact of this. Notice the array path is listed

with the type array, and the scalar type in the array is listed using the array path with "[*]" appended to it.

SELECT path,

type,

length

FROM user_json_dataguide_fields

WHERE table_name = 'JSON_DOCUMENTS'

AND column_name = 'DATA'

ORDER BY 1;

PATH TYPE LENGTH

---------------------------------------- ---------- ----------

$.element1 array 16

$.element1[*] number 1

$.element2 array 32

$.element2[*] string 8

***********************************************************************

3.3.19 Data-Dictionary Views That Record the Presence of JSON Columns:

Data-dictionary views USER_JSON_COLUMNS, ALL_JSON_COLUMNS, and DBA_JSON_COLUMNS now list the views, as well as the tables, that have columns with JSON data.

***********************************************************************

3.3.20 JSON Path Expression Item Methods:

JSON Path Expressions first introduced in Oracle 12c may use ‘item methods’ to transform selected data. The JSON standard describes six item methods:

12c: abs(), ceiling(), double(), floor()

18c: size(), type()

Oracle extends the JSON standard with:

12c: date(), length(), lower(), number(), string(), timestamp(), upper()

18c: boolean(), booleanOnly(), numberOnly(), stringOnly()

***********************************************************************

3.3.21 LOB Return Types for JSON_VALUE, JSON_QUERY, and JSON_TABLE Functions:

* SQL/JSON function json_value can now return a CLOB instance.

* SQL/JSON function json_query can now return a CLOB or BLOB instance.

Previously:

* json_value supported only VARCHAR2, NUMBER, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and SDO_GEOMETRY as return types.

*json_query supported only VARCHAR2 as return type.

JSON_VALUE - Basic Definition and Example:

The JSON_VALUE function returns an element from the JSON document, based on the specified JSON path.

SELECT JSON_VALUE(a.data, '$.FirstName') AS first_name,

JSON_VALUE(a.data, '$.LastName') AS last_name

FROM json_documents a

ORDER BY 1, 2;

FIRST_NAME LAST_NAME

--------------- ---------------

Jayne Doe

John Doe

2 rows selected.

JSON Query - Basic Definition and Example:

The JSON_QUERY function returns a JSON fragment representing one or more values. In the following example, JSON_QUERY is used to return a JSON fragment representing all the contact details for each person. The WITH WRAPPER option surrounds the fragment with square brackets.

SELECT a.data.FirstName,

a.data.LastName,

JSON_QUERY(a.data, '$.ContactDetails' RETURNING VARCHAR2(1000) WITH WRAPPER) AS contact_details

FROM json_documents a

ORDER BY a.data.FirstName,

a.data.Last_name;

FIRSTNAME LASTNAME CONTACT_DETAILS

--------------- --------------- --------------------------------------------------

Jayne Doe [{"Email":"jayne.doe@example.com","Phone

":""}]

John Doe [{"Email":"john.doe@example.com","Phone"

:"44 123 123456","Twitter":"@johndoe"}]

2 rows selected.

*********************************************************************

3.3.22 LOB Support for SQL/JSON Generation Functions:

The SQL generation functions

JSON_OBJECT

JSON_OBJECTAGG

JSON_ARRAY

JSON_ARRAY can now return LOB results, with the output determined by the RETURNING clause. The valid options are as follows.

{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]

| CLOB

| BLOB

}

The following data types are now supported as input expressions.

-- 12cR2

NUMBER

VARCHAR2

DATE

TIMESTAMP

-- 18c

BINARY_DOUBLE

BINARY_FLOAT

CLOB

BLOB

NVARCHAR2

RAW

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

JSON_OBJECT - Basic Definition and Example:

The JSON_OBJECT function converts a comma-separated list of key-value pairs into object members within a JSON object.

SELECT JSON_OBJECT (

KEY 'department-number' VALUE d.deptno,

KEY 'department-name' VALUE d.dname,

KEY 'location' VALUE d.loc

) AS departments

FROM dept d

ORDER BY d.deptno;

DEPARTMENTS

--------------------------------------------------------------------------------

{"department-number":10,"department-name":"ACCOUNTING","location":"NEW YORK"}

{"department-number":20,"department-name":"RESEARCH","location":"DALLAS"}

JSON_OBJECTAGG - Basic Definition and Example:

The JSON_OBJECTAGG aggregate function creates a single JSON object containing a list of object members formed by aggregating a key-value pair from each row.

SELECT JSON_OBJECTAGG (

KEY d.dname VALUE d.deptno

) AS departments

FROM dept d

ORDER BY d.deptno;

DEPARTMENTS

--------------------------------------------------------------------------------

{"ACCOUNTING":10,"RESEARCH":20,"SALES":30,"OPERATIONS":40}

JSON_ARRAY - Basic Definition and Example:

The JSON_ARRAY function converts a comma-separated list of expressions into a JSON array of JSON values.

SELECT JSON_ARRAY(

ROWNUM,

JSON_OBJECT(KEY 'department_no' VALUE d.deptno),

JSON_OBJECT(KEY 'department_name' VALUE d.dname)

) AS department_json_array

FROM dept d;

DEPARTMENT_JSON_ARRAY

--------------------------------------------------------------------------------

[1,{"department_no":10},{"department_name":"ACCOUNTING"}]