PLSQL Compress BLOB

PLSQL Compress BLOB

Compressing binary large objects (BLOB) or files using PL/SQL has been challenging. For at least a decade, Oracle includes UTL_COMPRESS as a means of compressing or zipping files. This technology faced a few limitations specifically, adding a BLOB with the UTL_COMPRESS.LZ_COMPRESS_ADD procedure. The source “file” is required to be raw. The utility spews errors when you asked it compress a BLOB directly. PLSQL Compress BLOB is easier with APEX_ZIP.

There are alternatives. Anton Scheffer wrote and maintains a fine package called AS_ZIP (the “AS” likely to reference Anton’s initials.

I learned this week after searching and searching that Oracle has picked up Anton’s utility and included it as an APEX Package. It is called APEX_ZIP. According to Christian Neumueller, it was Patrick Wolff that adopted Anton’s work.

How did I miss this? Well, I searched compress BLOB. I ought to have searched ZIP file. As of early May 2017, the phrases on the APEX_ZIP utility documentation are zip and unzip. The phrases on the UTL_COMPRESS utility are compress and uncompress. I avoided searching “ZIP” because of its association with a brand. Dooh! Let’s hope that this blog helps others find the compress blob utility APEX_ZIP.

PLSQL BLOB Compression

The purpose of this blog is to assist the next Oracle PLSQL developer who wants to compress blobs or zip blobs to find the APEX_ZIP utility. Have I used the phrases compress blob and zip blob enough to be picked up by search engines? I hope so.

APEX_ZIP

Please search for the APEX_ZIP utility. There are a few nuggets to know. First, you really can add a file to an empty set. Unlike Alice’s experience at a tea party, when offered “a little more tea?”. She states: “Well, I haven’t had any yet, so I can’t very well take more.” And the readers of all things Oracle PL/SQL know that one can’t add to a null. But, in this case you may add to an empty set.

ORA-06502

Protect the procedure call and screen for nulls. A null filename gives a ORA-06502 “numeric or value error” from sys.utl_raw.

What’s Next

Having a big ole BLOB with a set of zipped or compressed BLOBs doesn’t yet help the end user. We tend to use global temporary tables (“GTT”) as a means of hosting BLOBs for users to download via Oracle APEX. While GTT are self-cleaning and offer protection against nosy neighbors from seeing data, when using Global Temporary Tables in APEX, they can be seen by another user. So we always post the session ID with a record and we filter on that. That keeps nosy neighbors out and boundaries clean.

Function ZIP_BLOBS

The function below is a sample. It spins through a table with a bunch of blobs that need compressing. It puts the compressed BLOB into a global temporary table that is easily used in Oracle APEX.

Contributors

Anton Scheffer wrote a fine blog and utility in 2010. He has updated this utility as recently as April 2016. His code gives insights into the workings. Dimitri Gielis unceremoniously pointed out that had I searched ZIP instead of compress, I would have save days of misery. We really do need friends like that! Christian Neumueller shared the story of APEX_ZIP package and confirmed its roots. Patrick Wolf adopted Anton’s work.

Sample Code/Function

— sql create or replace function zip_blobs return number

as l_compress_blob blob; l_session number; l_zip_filename varchar2(60); l_sb_pk number; begin

l_zip_filename := ‘compressed_blobs.zip’; dbms_lob.createtemporary(l_compress_blob, false); l_session := apex_application.g_instance;

delete from tg_store_blob where sb_session = l_session;

– fetch blobs, build zip file for i in ( select doc_pk, doc_blob, doc_filename from tg_doc ) loop – Compress BLOB / ZIP BLOB if i.doc_filename is not null and i.doc_blob is not null then apex_zip.add_file ( p_zipped_blob => l_compress_blob, p_file_name => i.doc_filename, p_content => i.doc_blob); end if; – parameters not null end loop;

– Let the utility close up the zip file and add necessary footers apex_zip.finish(l_compress_blob);

insert into tg_store_blob ( sb_session, sess_date, blob_file,

created_date,
blob_mimetype,
blob_filename

) values (

l_session,
localtimestamp,
l_compress_blob,

localtimestamp,
null,
l_zip_filename

) returning sb_pk into l_sb_pk; commit; return l_sb_pk; end zip_blobs;

Oracle PL/SQL AWS S3

Amazon Web Services (AWS) Simple Storage Solution (S3) has upgraded its application programming interface (API) to support HTTPS. This upgrade involved a redesign of the authentication process at AWS. The authentication process now involves authenticating the user and verifying the canonical request made to AWS S3.

Morten Braten and Jason Straub have published and supported tools related to AWS S3 and Oracle PL/SQL. These tools, located within the Alexandria Library, do not support the new AWS4 signature and do not support HTTPS.

During the recent months, I have researched the AWS interface and written an updated package. I have hosted these tools at my github . I think with solid collaboration and greater testing from our Oracle and APEX community we can get this package solid enough to be included in the library.

Why?

Answer #1

At Storm Petrel, we use AWS S3 to augment the database for the storage of documents in our various document management tools. We keep an original in a BLOB and a modified copy at S3. We have wanted to make sure that ALL communication between the users and our infrastructure is 100% encrypted. With the link to AWS S3 travelling via HTTPS, we can stand behind this statement.

Answer #2

Initially, AWS S3 was all hosted from the Northern Virginia region. S3 is now hosted in 15 regions. Some of these regions require HTTPS and some require the AWS4 signature. It seems that AWS will want to depreciate the HTTP interface in time. Best to stay ahead of the demand!

Answer #3

This was well out of my comfort zone. In 2015, we wrote an interface to MailChimp. In 2016, interfaces to FEMA and to Nexmo for SMS and multifactor authentication. The AWS S3 stuff was tough!!!!

What is next?

I focused on getting the feature we use in place. AWS S3 has a huge number of features. This package does not even have all of the features that Morten put in to the original effort. So, testing and continued development is what is next.

I can’t do it alone. The foundation is here. The authentication stuff and HTTPS stuff works. Now to add the other bits that S3 has to offer, or that we need.

GitHub!

Please direct yourself to the Github site for more information about the package, and to get the packages. At that site you will find:

  • Documentation
  • The package (specification and body)Oracle PLSQL AWS S3
AWS4 Signature

-/qed

Oracle PLSQL SMS and MFA

Introduction

In 2015, I wrote an entry about doing multifactor authentication with PL/SQL (link: https://storm-petrel.com/orablog/2015/11/29/oracle-apex-multifactor-authentication/)

The SMS services that I based my work on has disappeared from the internet landscape, rendering my code and efforts futile.

So it was time to start again. The advantage was that I got to use better RESTful tools and JSON parsers.

The variance this time is that I put 100% of the code on Github

Why MFA?

Why use multifactor authentication (two-factor or 2FA) authentication? Well, frankly it is the right thing to do as I wrote in 2015…

The United States Office of Personnel Management is notifying 2.7 million people that their data were stolen. The blame lays at the feet of the people responsible for these data. While the ultimate responsibility rests with the executives who did not fund investment into data security, I shoulder that responsibility daily.

Github

https://github.com/cmoore-sp/pl-sql_nexmo_sms

Nexmo Multifactor Authentication

The last time I visited this topic, I used some random generators to create a 4-digit code that I then sent via text message to a phone. With the assistance of an Oracle global temporary table and precise timestamps, I did the heavy-ish lift on generating the code, storing the code, sending the code, and verifying the code and the timestamps. Not tough, but the Nexmo approach takes some of the effort.

The process initiates with MFA request to the phone. You get a message ID back. You do NOT get the code.

When the user keys in the code, you (the programmer) has to take the message ID generated from the request and the 4-digit code entered by the user. You send these off to Nexmo. They send back a status code. Zero is good. Not zero is bad.

Interesting Annoying Stuff

The tool apex_json.get_varchar2() does NOT like a dash in the “field name”. The vendor, Nexmo, put dashes in the field names for JSON, but did NOT for the XML interface. So a little replace() action is need to make Oracle happy.

To recap – this is acceptable but does not match Nexmo’s data definition

			r_response.client_ref :=
				apex_json.get_varchar2(
					p_values => l_values, 
					p_path => 'messages[' || trim(to_char(json_row)) ||'].clientRef'
				);

This is not acceptable to Oracle, but does match Nexmo’s data definition

			r_response.client_ref :=
				apex_json.get_varchar2(
					p_values => l_values, 
					p_path => 'messages[' || trim(to_char(json_row)) ||'].client-ref'
				);

Oh well, the solution is just as easy as changing the JSON data:

l_return := replace(l_return, 'client-ref', 'clientRef');

Oracle PL/SQL SMS and MFA

A few quick hints about the code…

Sending SMS text looks rather like this:

begin
	-- validate the parameters
	sms_ok(p_sms,'send_sms','Y');
	l_text := msg_ok(p_msg,'send_sms');

	-- structure the URL
	l_url := g_sms_uri 	||
			'api_key=' 	|| g_key || amp ||
			'api_secret=' 	|| g_secret || amp ||
			'to=' 		|| p_sms || amp ||
			'from=' 		|| g_sender || amp ||
			'text=' 		|| l_text ;
	if p_client_ref is not null then
		l_url := l_url		|| amp ||
			'client-ref='	|| p_client_ref;
	end if; -- client reference is not null
	
	l_return := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
			 p_url              => l_url
			,p_http_method      => 'GET'
			,p_wallet_path		=> g_wallet_path
			,p_wallet_pwd		=> g_wallet_pwd
			);
...

APEX Application

I whipped up a quick APEX 5.1 application to show the integration of the package and the Nexmo API. It is also posted at Github. It will send an SMS message, do two-factor authentication, and show you your balance.

Oracle APEX Multifactor Authentication

Requirements

  1. You MUST have an account with Nexmo.com. You can get a demo account for free.
  2. You MUST put your own Oracle Wallet Path and Wallet password into the packages and compile them (G_WALLET_PATH and G_WALLET_PWD)
  3. You MUST manually install the Root and Intermediate SSL certs for nexmo.com in your wallet
    • ROOT: Digicert Global Root CA, v3, valid through 09NOV2031
    • Intermediate: Digicert SHA2 Secure Server CA, valid thorugh 08MAR2013
  4. If you are on Oracle 12c, DO NOT install the SSL cert for *.nexmo.com
  5. You’ll need to create the table that is in the package body. It is in the commented out section.
  6. You’ll need to put in your own G_KEY and G_SECRET at the top of the package body.

-\qed

Calendar Invitations

ICS File - Calendar Invitations

Calendar invitations are crude text files that follows standards established in RFC 5545. Creating these files from within Oracle PL/SQL requires understanding the rules. Often when putting together a new utility, I think if my youngest days with Lego (yes, my first computer was actually made from Lego. My inspiration came from the 1960s Bat Computer and the rectangular punch cards that could be found in every neighbor’s home.) You snap existing stuff together in new ways (or old ways). The information I read didn’t actually allow me to create an invitation that was accepted by Microsoft Outlook 365.

Step 0 – Read a few blogs & Wing it!

Other blogs and toolkits emphasized the simplicity of these vCalendar text files. I’d write one in NotePadd++, then pull it into MS Outlook 365. I got error messages, most commonly:

“We couldn’t find this meeting in the calendar. It may have been moved or deleted.”

Well, thank you says I. It isn’t in the calendar; you are so right my dear error message. I want to add it to my calendar. No amount of clicking accept helped (nor did the error message).

Step 1 – Actually Research

The calendar format (vCalendar, ics, ical, etc) is defined in RFC 5545. The authoritative references is here: https://icalendar.org/RFC-Specifications/iCalendar-RFC-5545/

Reading the specifications is made more boring by having to click next after each paragraph. Apparently, the Spartan-inspired authors forbade skimming. Even the Oracle document gives the occasional example, I couldn’t find any on their website.

Step 2 – Hack a message that Works

In my Outlook, I found a robust activity on my calendar: it came from outside source; it had a location; it had long description; it had multiple attendees. I exported it as text file with an ics extension. This, when I opened it in NotePad++, clearly looked like the stuff in RFC 5545.

I changed a date and time, saved it and Outlook gladly accept it as a new invitation. It also imported well into Google Calendar.

BEGIN:VCALENDAR
PRODID:-//Microsoft Corporation//Outlook 16.0 MIMEDIR//EN
VERSION:2.0
METHOD:REQUEST
X-MS-OLK-FORCEINSPECTOROPEN:TRUE
BEGIN:VEVENT
ATTENDEE;CN="Barnibus A Rubble";RSVP=FALSE:mailto:barney.rubble@fakeemail.com
ATTENDEE;CN=frederick.flintstone@fakeemail.com;RSVP=FALSE:mailto:frederick.fl
	intstone@fakeemail.com
ATTENDEE;CN="Christina Moore";RSVP=FALSE:mailto:not.here@storm-petrel.com
CLASS:PUBLIC
CREATED:20161216T134426Z
DESCRIPTION:You have been invited to a join.me online meeting \n\nJoin the 
	meeting: https://join.me/************ \n\nOn a computer\, use any browser. 
	Nothing to download. \nOn a phone or tablet\, launch the join.me app and e
	nter meeting code: *********** \n\nJoin the audio conference: \nDial a pho
	ne number and enter access code\, or connect via internet. \n\nBy phone: \
	nUnited States - Los Angeles\, CA   +1.213.***.1066 \nUnited States - New 
	York\, NY   +1.646.***.1990 \nUnited States - Hartford\, CT   +1.***.970.0
	010 \nUnited States - Camden\, DE   +1.***.202.5900 \nUnited States - Tamp
	a\, FL   +1.813.***.0500 \nUnited States - Washington\, DC   +1.***.602.12
	95 \nUnited States - San Francisco\, CA   +1.***.594.5500 \nUnited States 
	- Atlanta\, GA   +1.404.***.8750 \nAccess Code   ***-687-515# \n\nOther in
	ternational numbers available \n\nBy computer via internet: \nJoin the mee
	ting\, click the phone icon and select 'Call via internet'. A small downlo
	ad might be required. \n\nStart time by time zones \n\n\n
DTEND:20161216T160000Z
DTSTAMP:20161207T162659Z
DTSTART:20161216T150000Z
LAST-MODIFIED:20161216T134426Z
LOCATION:join.me/*******\, see conference numbers in the invitation
ORGANIZER;CN="Christina Moore via join.me":mailto:scheduler@****.me
PRIORITY:5
SEQUENCE:2
SUMMARY;LANGUAGE=en-us:Tempest-Bid (CB&I)
TRANSP:OPAQUE
UID:11235811@******rel.com
X-MICROSOFT-CDO-BUSYSTATUS:BUSY
X-MICROSOFT-CDO-IMPORTANCE:1
X-MICROSOFT-CDO-INTENDEDSTATUS:BUSY
END:VEVENT
END:VCALENDAR

Step 3 – Find the rules that matter

My good luck with my Join.Me calendar invitations are that they have a lot of text and a number of attendees. When taking a look at the source text, it became clear that more steps are required then concatenating text.

  1. Lines are Folded (RFC 5545 3.1 Content Lines)
  2. Date/Time are presented as ISO 8601 Format
  3. Time Zones Matter

The iCalendar.org website provides a Validator tool.

3.1 Lines Are Folded

JSON and XML structures identify field name and data. The vCalendar text does not. Rooted firmly in the early days of network computers, it uses the format of the text to discriminate data from not-data. To that end, lines are folded.

  • A line ends with carriage return/linefeed [CRLF, concat(chr(10), chr(13)), etc].
  • A line must not be longer than 75 bytes
  • If a line is longer, cut it off with a CRLF, start a new line with a horizontal tab [HTAB, chr(9)]

This is discussed here:

To preserve your original CRLF you’ll need to replace them with ‘\n’. I observed that the comma character [chr(44)] was also escaped as ‘\,’

So before folding your lines, escape your CRLF and commas.

DESCRIPTION:You have been invited to a join.me online meeting \n\nJoin the 
	meeting: https://join.me/************ \n\nOn a computer\, use any browser. 
	Nothing to download. \nOn a phone or tablet\, launch the join.me app and e
	nter meeting code: *********** \n\nJoin the audio conference: \nDial a pho

Here is a little snippet of code to start with:

function escape_text (
	P_STRING	in clob
) return clob
as
	l_return		clob;
begin
	-- escape the comma character
	l_return := replace(P_STRING, chr(44), '\,');
	l_return := replace(l_return, chr(10), '\n');
	l_return := replace(l_return, chr(13), '');
	return l_return;
end escape_text;

3.2 Date and Time in ISO 8601 Format

This date format is familiar to those who use Oracle PL/SQL to exchange data with JSON. A few quick tricks and Bob’s your uncle:

function ISO_8601 (
		P_DATE		in timestamp,
		P_TIMEZONE	in varchar2
		) return varchar2
as
	l_timestamp		timestamp;
	l_iso_8601		varchar2(60);
begin
	-- convert the date/time to UTC/Zulu/GMT
	select 
		cast(P_DATE as timestamp with time zone) at time zone 'UTC' 
		into
		l_timestamp
	from dual;
	-- convert the format to ISO_8601/JSON format
	if l_timestamp is not null then
		l_iso_8601 := to_char(l_timestamp, g_ISO8601_format);
	else
		l_iso_8601 := null;
	end if;
	return l_iso_8601;
end iso_8601;

Note that the package has a constant for the date format as shown:

  g_ISO8601_format	constant varchar2(30) := 'YYYYMMDD"T"HH24MISS"Z"';

3.3 Time Zone Matter

There is a lot of blah-blah-blah in the protocol about time zones. The easiest solution is to just use UTC. Convert your times to Zulu and be done with it.

select 
		cast(P_DATE as timestamp with time zone) at time zone 'UTC' 
into
		l_timestamp
from dual;
package tree

Step 4 – Write Some Code

First, I don’t like looking at code with a bunch of concatenated strings. So instead, I use substitution strings. I’ll argue you can make updates and changes with less effort too. The only real challenge in finishing the effort is deciding how to handle your list of attendees. That will clearly be data driven, each line of attendees will have to be folded as you build your list. With the list of attendees appropriately formatted, a quick substitution will land them into your calendar invitations (ics text file).

I took the good/source vCalendar text and converted it and put it at the top of the package body.

create or replace package body ical_pkg
as
  g_ISO8601_format    constant varchar2(30) := 'YYYYMMDD"T"HH24MISS"Z"';
  crlf				        constant varchar2(2) := chr(13) || chr(10);
	tab                 constant varchar2(1) := chr(9);
	vcal                constant varchar2(4000) :=
q'[BEGIN:VCALENDAR
PRODID:-//Oracle APEX//Outlook 16.0 MIMEDIR//EN
VERSION:2.0
METHOD:REQUEST
X-MS-OLK-FORCEINSPECTOROPEN:TRUE
BEGIN:VEVENT
#ATTENDEE_LIST#
CLASS:PUBLIC
#CREATED#
#DESCRIPTION#
#DTEND#
#DTSTAMP#
#DTSTART#
#LAST_MODIFIED#
#LOCATION#
#ORGANIZER#
PRIORITY:5
SEQUENCE:2
#SUMMARY#
TRANSP:OPAQUE
#UID#
X-MICROSOFT-CDO-BUSYSTATUS:BUSY
X-MICROSOFT-CDO-IMPORTANCE:1
X-MICROSOFT-CDO-INTENDEDSTATUS:BUSY
END:VEVENT
END:VCALENDAR]';
	
function escape_text ( ...

I know that the code is not fully functional, they are just hints to encourage exploration.

function event_invite(
	P_EVENT_PK		in number
	) return clob
as
	r_event        bid_event_v%ROWTYPE;
	r_asset        bid_asset_v%ROWTYPE;
	l_select_count number;
	l_ics          clob;
...
begin
	select sessiontimezone into l_timezone from dual;
	select * into r_event from bid_event_v where event_pk = P_EVENT_PK;
	l_ics            := vcal;
	l_created        := ical_pkg.iso_8601(localtimestamp, l_timezone);
	l_created        := 'CREATED:' || l_created;
	l_last_modified  := ical_pkg.iso_8601(localtimestamp, l_timezone);
	l_last_modified  := 'LAST-MODIFIED:' || l_last_modified;
	l_dtstamp        := ical_pkg.iso_8601(localtimestamp, l_timezone);
	l_dtstamp        := 'DTSTAMP:' || l_dtstamp;
	l_location       := 'LOCATION:Tempest-Bid';
	l_organizer      := 'ORGANIZER:CN="Tempest-Bid":mailto:do_not_reply@storm-petrel.com';
	
...

	l_attendees			:= ical_pkg.attendees(P_EVENT_PK);
...

	l_ics		:= replace(l_ics, '#ATTENDEE_LIST#', l_attendees);
	l_ics		:= replace(l_ics, '#CREATED#', l_created);
	l_ics		:= replace(l_ics, '#DESCRIPTION#', l_description);
...

return (l_ics);
end event_invite;

-/qed

Convert RGB to Hex with PLSQL

Fun with Colors

The fun with colors carries us back to Crayola’s and refrigerator art. One thing I liked about my ole Crayola crayons is that they each came with their name stamped on the side: brick red, mulberry, and pine green. With a very simple table structure, we Convert RGB to Hex with PLSQL. With small effort, you can display these colors in your applications. So why not have a color table? Then you too can flow color name to hex or RGB colors on demand within an Oracle APEX application.

Color Table

You don’t need much. I opted to store the RGB values and a name. The resultant table has five fields:

  • Color primary key
  • Color Name
  • Red Value (0-255)
  • Green Value (0-255)
  • Blue Value (0-255)
CREATE TABLE "BLOG_COLOR" (
  "COLOR_PK"    NUMBER(11,0), 
  "COLOR_NAME"  VARCHAR2(60), 
  "COLOR_RED"   NUMBER(3,0), 
  "COLOR_GREEN" NUMBER(3,0), 
  "COLOR_BLUE"  NUMBER(3,0)
  );

Convert RGB to Hex with PLSQL

Converting red, green, blue color scheme to hex using Oracle PL/SQL is a formula. The first step is to explore how the to_char() function converts a decimal number to a hexadecimal number.

The formula is: to_char(15,’XXX’). Give it a go in your favorite PL/SQL interface.

select to_char(15,'XXX') red from dual;

Result? F

To go the other way around: hex to decimal?

select to_number('F','XX') red from dual;

Display colors through HTML is often easier with six-character long representation of the color value.

Firebrick is RGB(178, 34, 34) or #B22222. Hex B2 is 178. And hex value for decimal 22 is 34.

Hexadecimal colors are really concatenations of 3 hex values: 2 for red, 2 for green, and 2 for blue. The firebrick in hex is B2 – 34 -34. Keeping track of your digits is important. Darkgreen which is RGB(0,100,0) must be represented as #006400 in hex. The digits are significant. So you’ll need to trim spaces and add your padding.

select lpad(trim(to_char(15,'XXX')),2,'0') red from dual;

Read more about lpad at Oracle’s site. Here we are stating that the result must be 2 characters long and be padded with zeros.

select
    '#' ||
  	lPad(trim(to_char(:red,'XXX')),2,'0')  ||
  	lPad(trim(to_char(:green,'XXX')),2,'0')  ||
  	lPad(trim(to_char(:blue,'XXX')),2,'0')  HEX_COLOR
  from dual;

For my values, I used 178, 34, 34 (firebrick red). My query yielded: ‘#B22222’.

Back to APEX

Colors in APEX

The image shown is a snippet of a color table viewed as a classic report in Oracle Application Express. As shown above, the hex value for the color is result of a formula within the query (calculated column). The sample column is a blending of two tricks. I primed my query with a simple Font Awesome shape to display my color swatch. And then I used two column in my HTML formatting for the classic report column.

HTML Format Report Column

Just to finish it off, here is the query.

select
	color_name "Color",
	color_red "Red",
	color_green "Green",
	color_blue "Blue",
	'#' ||
  	lPad(trim(to_char(color_red,'XXX')),2,'0')  ||
  	lPad(trim(to_char(color_green,'XXX')),2,'0')  ||
  	lPad(trim(to_char(color_blue,'XXX')),2,'0')  HEX_COLOR,
	'fa-square' square
from tg_colors

Conclusion

Converting RGB to Hex with PLSQL may not be as much fun as a box of Crayola crayons and big pieces of paper. But it is better than stepping outside in a blustery and snowy May 15th!

-/qed

Synchronize Sequence with Primary Key

Background

Whilst migrating to Oracle 12c, it is clear that we will still have primary keys populated by sequences for years to come. When I pull production data over to our development server, I dread the steps required to synchronize the old sequences to the updated tables. Using the expdp / impdp, moving the tables has become routine and risk free. Then on Monday morning after a data refresh into dev, our applications hit a hard stop with a duplicate entry into a field with a unique index (oh, like a primary key).

Context

The standard here is that tables contain a prefix corresponding to their application and that sequences, indices and related objects contain the table name, as shown:

  • table: tt_employees
  • Sequence: tt_employees_seq
  • Primary key: tt_employees_pk
  • Trigger: tt_employees_trig

This permits us to take a few short cuts in managing the database objects. It means that we can write queries to explore our structures dynamically.

Synchronize Sequence with Primary Key

Initially, I relied on a query to identify sequence / primary key mismatches. After a while, it just wasn’t enough. Picking up techniques from Gokhan Atil, I took the process further down the pitch.

create or replace procedure update_sequence 
------------------------------------------------------------------
--
-- cmoore 25OCT15
-- explores the primary keys and sequences 
-- and identifies any that need updating. 
-- Designed to run after an import from production
-- 25feb2016 - added alter sequence 
------------------------------------------------------------------
as
  l_table_name        varchar2(50);
  l_primary_key       varchar2(50);
  l_sql               varchar2(4000);
  l_sequence_name     varchar2(4000);
  l_max               number;
  l_last_seq_number   number;
  l_select_count      number;
  l_prefix            varchar2(20) := 'TT_%';
  l_schema            varchar2(20) := 'STORMDEV';
  l_increment_by      number;
  l_sequence          varchar2(60);
  l_temp              number;
  
begin
for i in (
  select
    table_name
  from
    all_tables
  where table_name like l_prefix 
  order by table_name
  ) loop

  -- Does a primary key exist?
  SELECT count(cols.column_name)
  INTO l_select_count
  FROM all_constraints cons, all_cons_columns cols
  WHERE cols.table_name = i.TABLE_NAME
  AND cons.constraint_type = 'P'
  AND cons.constraint_name = cols.constraint_name
  AND cons.owner = l_schema
  and rownum = 1
  ORDER BY cols.table_name, cols.position;
	
	-- if a record exists, then there is a primary key
  if l_select_count = 1  then

    SELECT cols.table_name, cols.column_name
    INTO l_table_name, l_primary_key
    FROM all_constraints cons, all_cons_columns cols
    WHERE cols.table_name = i.TABLE_NAME
    AND cons.constraint_type = 'P'
    AND cons.constraint_name = cols.constraint_name
    AND cons.owner = l_schema
    and rownum = 1
    ORDER BY cols.table_name, cols.position;
    
      dbms_output.put( 'Table: ' );
      dbms_output.put_line( i.table_name);
	-- find the largest primary key value in the table
      l_sql := 'select max( #KEY# ) from #TABLE#';
      l_sql := replace(l_sql,'#KEY#',l_primary_key);
      l_sql := replace(l_sql,'#TABLE#',l_table_name);
      execute immediate l_sql into l_max;
			
      dbms_output.put('Max PK: ' ||  to_char(l_max));
      l_sequence := l_table_name || '_SEQ';
      
      -- find the last sequence number issued
	l_sql := 
	q'[select last_number from all_Sequences where sequence_owner = '#SCHEMA#' and sequence_name like '#SEQUENCE#']';
      l_sql := replace(l_sql,'#SEQUENCE#',l_sequence);
      l_sql := replace(l_sql,'#SCHEMA#',l_schema);

	-- note: little error checking going on...
      execute immediate l_sql into l_last_seq_number;
      dbms_output.put_line( '  Last Seq: ' || to_char(l_last_seq_number));
			
	-- sequence is less than the most recent primary key entry, so fix it
      if l_last_seq_number < l_max then
        l_increment_by := trim(to_char(l_max - l_last_seq_number + 1));
        dbms_output.put_line( ' ** adjusting sequence by: ' || to_char(l_increment_by));
				
	-- tell the sequence to jump by delta between max(pk) and last_seq_number
        l_sql := 'alter sequence #SEQUENCE# increment by #INCREMENT_BY#';
        l_sql := replace(l_sql,'#SEQUENCE#',l_sequence);
        l_sql := replace(l_sql,'#INCREMENT_BY#',l_increment_by);
        execute immediate l_sql;
				
	-- request the nextval from the sequence
        l_sql := 'select #SEQUENCE#.nextval from dual';
        l_sql := replace(l_sql,'#SEQUENCE#',l_sequence);
        execute immediate l_sql into l_temp;
				
	-- reset the sequence increment by to the normal of 1
        l_sql := 'alter sequence #SEQUENCE# increment by 1';
        l_sql := replace(l_sql,'#SEQUENCE#',l_sequence);
        execute immediate l_sql;

      end if; -- sequence less than key value
  else -- no primary key exists
      dbms_output.put( 'Table ' );
      dbms_output.put( i.table_name);
      dbms_output.put_line( ' no key found ' );
  end if; -- select count = 1
end loop;
end update_Sequence;

-/qed