Follow Christina Moore Oracle Blog on Feedspot

Continue with Google
Continue with Facebook


Christina Moore Oracle Blog by Christina Moore - 11M ago
PLSQL Markdown to HTML

I have posted a PLSQL Markdown to HTML package on my github site (https://github.com/cmoore-sp/plsql-markdown-2-html).


There are times with Oracle APEX when a development team may want to provide users with the ability to host modest HTML sections. Using Markdown is a whole lot friendlier than HTML. The package will return an HTML formatted CLOB after parsing the markdown annotations.

The aim is to allow an application user to draft a quick post/message or email then let this format it to HTML.

Limitations CLOB Size

I took advantage of the apex_util.string_to_table to help read through the CLOB. Thereby limiting the size to 32K. This allows the process to kick start without being bogged down. If we need to expand, we can.

Nested Lists

I did not write a process for nesting lists. Given the intended use and audience, it seemed like a bridge too far. If we (collectively) need it, we can write it in.


The post PLSQL Markdown HTML appeared first on Oracle Blog .

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Christina Moore Oracle Blog by Christina Moore - 11M ago

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.


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.


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

What 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.


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
create or replace function zip_blobs return number

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

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 (
	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

insert into tg_store_blob (


) values (



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

The post PLSQL Compress BLOB appeared first on Oracle Blog .

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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.


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)


The post Oracle PL/SQL AWS S3 appeared first on Oracle Blog .

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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 (https://github.com/cmoore-sp/pl-sql_nexmo_sms)

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.


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 :=
					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 :=
					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:

	-- validate the parameters
	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
			 p_url              => l_url
			,p_http_method      => 'GET'
			,p_wallet_path		=> g_wallet_path
			,p_wallet_pwd		=> g_wallet_pwd
APEX Application

I wiped 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.

  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.

The post Oracle PL/SQL SMS and MFA appeared first on Oracle Blog .

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
By Stevie Dickerson, Storm Petrel
Problem Statement

Exporting then importing an Oracle APEX 5.1 application with a newer-style JET report generated an error indicating a problem with a plug in.


Our team at Storm Petrel were excited to implement new APEX 5.1 features into our applications, so once our development server was upgraded to APEX 5.1, we immediately got to work updating our legacy charts to JET charts.

The charts themselves are beautiful and more modern than the previous APEX charts. We were really excited about the change and our production server was the next to be upgraded. Then…a problem happened.

I ran into a problem taking an application from our development server to our production server for early testing. This was the first application that contained APEX 5.1 features, specifically JET charts.

Problem Summary

When exporting then importing an APEX 5.1 application that includes a JET chart, an error message appears that reads:


The error backtrace from our error log does not give much more information


----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FFF15455870       935  package body APEX_050100.WWV_FLOW_ERROR
00007FFF15455870      1003  package body APEX_050100.WWV_FLOW_ERROR
00007FFF15455870      1395  package body APEX_050100.WWV_FLOW_ERROR
00007FFF15455870      1484  package body APEX_050100.WWV_FLOW_ERROR
00007FFF154E9A48      2122  package body APEX_050100.WWV_FLOW_PLUGIN
00007FFF154E9A48      3004  package body APEX_050100.WWV_FLOW_PLUGIN
00007FFF173BF108      4533  package body APEX_050100.WWV_FLOW
00007FFF173BF108      5951  package body APEX_050100.WWV_FLOW
00007FFF157995A0         2  anonymous block

Problem Confirmation

Mark the JET chart regions as “NEVER”. If you load the page without issues, then you are likely.

Problem Workaround/Resolution

After receiving assistance from the awesome staff at Oracle APEX, we found that we were hitting bug #25403748 – plug-in ajax request fail if component id is negative which has already been fixed in the upcoming APEX 5.1.1 patchset.

Until the APEX 5.1.1 release, the workaround to avoid the error message is:

  1. When exporting an application that includes JET charts, make sure that
  2. “Export with Original IDs” = No
  3. Import the application into the target environment like normal, then Voila!

No error messages.

Stevie Dickerson

The post APEX_ERROR_CODE – APEX.ERROR.INTERNAL REQUEST – PLUGIN appeared first on Oracle Blog .

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Christina Moore Oracle Blog by Christina Moore - 1y ago
Oracle APEX Accessibility

VPAT, Section 508, WCAG all deal address improving access to software. Oracle APEX has the tools, developers should include the techniques. What do we need steer closer and close to the various guidelines that deal with improving Oracle APEX accessibility?

  1. Why write this blog in January 2017
  2. Let’s look at some of the references, definitions and link to literature.
  3. A few quick steps in Oracle APEX
Why write this now?

Oracle APEX 5.1 was released in the recent month. The 5.1 release take a number of strides towards greater compliance with accessibility laws and guidelines. I was in the process of having to change a series of icons and links. This upgrade gave Storm Petrel the opportunity to improve our posture and position on these standards. I am making the changes anyway. It is all copy/replace anyway. Adding the extra bits is good for the world and cost us nothing.

Links and Lit

The terms and definitions for “accessibility” are captured in a variety of acronyms in the United States. Some reference specific US law.

Term: VPAT

Definition: Voluntary Product Accessibility  Template

Link: https://www.state.gov/m/irm/impact/126343.htm

A VPAT is a vendor-generated statement that provides information on how a vendor’s products confirm to the Section 508 standards. For software developers who develop applications used by federal, state, or local governmental agencies, compliance is required. Compliance is likely required by all sorts of quazi-governmental organizations too. And compliance is likely required by any customer who is a governmental contractor. Such regulations often flow through contracts.

Term: Section 508

Definition: Section 508 of the Rehabilitation Act of 1973

Link: https://www.section508.gov/content/learn/laws-and-policies

In 1998, Congress amended the Rehabilitation Act of 1973 to require Federal agencies to make their electronic and information technology (EIT) accessible to people with disabilities. The law (29 U.S.C. § 794 (d)) applies to all Federal agencies when they develop, procure, maintain, or use electronic and information technology. Under Section 508, agencies must give disabled employees and members of the public access to information that is comparable to access available to others. The United States Access Board discusses the Section 508 law and its responsibility for developing accessibility standards for EIT to incorporate into regulations that govern Federal procurement practices.

Term: WCAG 2.0

Definition: Web Content Accessibility Guidelines

Link: https://www.w3.org/TR/WCAG20/

Web Content Accessibility Guidelines (WCAG) 2.0 covers a wide range of recommendations for making Web content more accessible. Following these guidelines will make content accessible to a wider range of people with disabilities, including blindness and low vision, deafness and hearing loss, learning disabilities, cognitive limitations, limited movement, speech disabilities, photosensitivity and combinations of these. Following these guidelines will also often make your Web content more usable to users in general.

Term: ADA

Definition: Americans with Disabilities Act

Link: https://www.ada.gov/pubs/ada.htm

Oracle and APEX

Oracle does evaluate APEX for compliance with these laws and publishes a VPAT. The link for the 2015 VPAT statement (which is based on APEX 5.0.1) is here: http://www.oracle.com/us/corporate/accessibility/templates/t2-5214.html

A Few Steps Icon and Links

I love the modern looking links that include icons. On their own, they are not compliant with the rules described above. Screen readers need something to read: “eff-ay-pencil” is not helpful. A reader that says “edit” is a bit more helpful.

Improve your icon links with alt text and even titles.

<i  alt="Edit" title="Edit"></i>
<i  alt="Copy" title="Copy"></i>
<i  alt="View" title="View"></i>

Screen readers will read the business that follows ‘alt’. A nice short action verb describing the action is more helpful.

Color Contrasts

Within the Theme Roller, there are small check marks next to the color pairs. These check marks tell part of the WCAG story. The check indicates that the color contrast is minimally passing. Click on the Check Mark, and you will get a description of the Color Contrast Information. The more A’s you get the better you are: AAA has better contract than AA.

The Universal Theme seems to have AAA ratings. When we, developers, get all fancy, we may go astray.


The post APEX Accessiblity appeared first on Oracle Blog .

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Synchronize APEX RESTful Services

Now that we depend on RESTful services for generating all of our reports, a new challenge exists when promoting applications from development to production. How do we synchronize APEX RESTful service? This step has become mission critical now that all of our reports are generated with APEX Office Print (AOP).

At Storm Petrel, we run one server for development and one server for production. With SQL Development and other tools, keeping comparing the database is straight forward. When we promote an APEX release to production, we tick through a basic checklist: check packages, check table structures, move APEX app. Not too bad.

APEX RESTful services are not “in the database”, therefore SQL Developer is blind to them.

APEX RESTful services are not “in an application”, therefore exporting an application ignores them.

We need a tool to help us.

It won’t take long to guess, that we will use PL/SQL, APEX, and RESTful services.

Tool Kit

Without going into APEX, how do you find APEX RESTful services? They are in public synonym and views that the team at Oracle provide for us. You can explore this views with three queries:

Select * from apex_rest_resource_handlers;

Select * from apex_rest_resource_modules;

Select * from apex_rest_resource_parameters;

Select * from apex_rest_resource_templates;

Build View

The first exploration involves playing with data, for that we built a view based on this query:

	h.workspace		"workspace", 
	t.module_name	"module_name",
	m.uri_prefix		"uri_prefix",
	h.uri_template		"uri_template",
	h.method		"method",
	h.require_https	"require_https",
	h.source			"source",
	h.last_updated_on	"updated",
	h.last_updated_by	"updated_by",
	h.created_by		"created_by",
	h.created_on		"created"
from apex_rest_resource_handlers h
left join apex_rest_resource_templates t on
	t.template_id = h.template_id
left join apex_rest_resource_modules m on
	t.module_id = m.module_id
order by h.workspace, t.module_name, m.uri_prefix, h.uri_template;

The data in here mimics what you see in the APEX RESTful services.

Compare the screenshot above with the data below:

Synchronize APEX RESTful Services

With the need to compare data from two servers, it made sense to create a RESTful service based on the same data that shows in the query from above. So that is what we did:

How to Compare

With a few hours, effort in a PL/SQL package, you can query the RESTful services from two servers. In our case, we called them simply, LEFT and RIGHT. You could use an Oracle JSON table and run queries from there, or just spin through the data that you have. We opted to spin through the data that we fetched from the two URLs (left and right).

We wrote three functions:

select apex_rest_pkg.template_count_match from dual;
select apex_rest_pkg.uri_mismatch from dual;
select apex_rest_pkg.query_mismatch from dual;

In the first case, we know if the number of URI templates match. While not super useful, it is a bit of a guide.

The second query gives us a formatted CLOB that details all of the APEX RESTful services that are missing on the “right” server.

The third query compares the JSON queries within the RESTful services. If there is a mismatch, it tells us the name of the module, URI prefix, and URI template. We always copy the literal text from the development server and paste it to the production server.


There are some fine free JSON comparison tools on line. You can certainly paste your data there.

I do rather wish that the SQL Developer database comparison tool has a stupid tool for comparing the values within a source and destination table. It is out of the scope of the tool, and if it were there someone would try to grind through 100,000 records. We are programmers, so writing code that fetches data, compares it, and react  constitute fundamental skills. If you need help or the code, catch me on twitter @cmoore_sp, I’ll ship it to you. I don’t mind.

Next Steps

In Oracle APEX, we can add a push button, display the results in a text block and even email them to the developer.


The post Synchronize APEX RESTful Services appeared first on Oracle Blog .

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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.

PRODID:-//Microsoft Corporation//Outlook 16.0 MIMEDIR//EN
ATTENDEE;CN="Barnibus A Rubble";RSVP=FALSE:mailto:barney.rubble@fakeemail.com
ATTENDEE;CN="Christina Moore";RSVP=FALSE:mailto:not.here@storm-petrel.com
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
LOCATION:join.me/*******\, see conference numbers in the invitation
ORGANIZER;CN="Christina Moore via join.me":mailto:scheduler@****.me
SUMMARY;LANGUAGE=en-us:Tempest-Bid (CB&I)
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, 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: https://icalendar.org/iCalendar-RFC-5545/3-1-content-lines.html

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
	l_return		clob;
	-- 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
	l_timestamp		timestamp;
	l_iso_8601		varchar2(60);
	-- convert the date/time to UTC/Zulu/GMT
		cast(P_DATE as timestamp with time zone) at time zone 'UTC' 
	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);
		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.

		cast(P_DATE as timestamp with time zone) at time zone 'UTC' 
from dual;

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
  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) :=
PRODID:-//Oracle APEX//Outlook 16.0 MIMEDIR//EN
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
	r_event						bid_event_v%ROWTYPE;
	r_asset						bid_asset_v%ROWTYPE;
	l_select_count		number;
	l_ics							clob;
	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;


The post Calendar Invitations appeared first on Oracle Blog .

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Oracle APEX_APPLICATION synonyms are worth exploring. As is often said, APEX is written in APEX. That means means that any APEX developer can undertake using some of the tools that the APEX development teams left for us in this tool kit. Let’s take a look and find a few uses for the Oracle public synonyms that lay at our fingertips.

Public Synonyms

The first step is finding them. With SQL Developer, I use filters on Public Synonyms. In the illustration, I show a filter for NAME LIKE APEX_%

Filter Public Synonyms for APEX_%

Best Targets

Many (or most) of these synonyms are views pulling data from multiple tables in the APEX home schema. These are relatively safe to explore because a developer can not update tables through these views.

select * from apex_applications;
select * from apex_application_tables;

What are the best targets? Start with familiar and identifiable data such as all applications, then take a look at the pages for applications.

The APEX Team Development data are available here as well:

How, Why, Where to Use?

There are ample opportunities to take advantage of the data within the tables. You can use query responses within APEX to regulate behavior of an application. Alternatively, you can query the data externally to evaluate the consistency of development work performed. The ambitious amongst us might plot to put together their own “advisor” tool set or capture activity logs as part of a quality assurance process.

Synonyms in an Application

A few examples to kick things off… Let’s envision a region defined on an Oracle APEX Global Page. I want it to display only when the page mode is “Normal”. I don’t want to waste space on dialogue boxes.

With my region, I add a “Rows Returned” condition with the following query:

select page_id
from apex_application_pages
where application_id = :APP_ID
and page_id = :APP_PAGE_ID
and page_mode like 'Normal%'
Ad-Hoc Advisor

If a team wants to explore the consistency of their work, a few queries may help. I have used it to find old button CSS from APEX 4. In the example below, I want to discover how many pages do not have help text:

from apex_application_pages 
where application_id = 101 
	and (
	help_text is null or
	help_text like 'No help is available for this page.'

You can do the same for page access protection, pages requiring authentication, or button styles, or page item styles. Pretty much anything within your application, you can see if your approach has been consistent.

Are all of my cancel buttons grey?

Are all of my save buttons hot?

Here is an example for seeing how complete the help text is for items…

from apex_application_Page_items
where application_id = 101
	and display_as <> 'Hidden'
	and display_as <> 'Display Only';
Quality Assurance Tools

The Oracle APEX development team provides us a view called APEX_DEVELOPER_ACTIVITY_LOG. This view provides insights on all development efforts within APEX. I have spent month and months exploring these data with the question: How do I improve the quality management process within APEX. Part of the answer sits here.

If I want to link:

  • Applications
  • to Releases
  • to Bug/Features
  • to coding efforts

Then part of my answer is in this view: APEX_DEVELOPER_ACTIVITY_LOG. (The other part exists as database changes that I can get from database views). Like a plastic shovel on a beach, I can now scoop all of the changes, additions, deletions made by developers into a table. I can link that activity with bugs or release data and the developer responsible. I can go further and use these data to develop a test plan. I pack these granular bits into my bucket and tip out structures that assist a development team. It is not just a change log. It is not just Big Brother watching, or another token in a Blame-Game of who buggered what. It become a means of linking building and supporting an application with the release notes, the change log, the test plan. It is a building block in robust software development.

At Storm Petrel, we’ve started, but that is it.

What Next?

Please ask me what would make these synonyms/views more powerful?

First, to really work with these data we do need a primary key, that unique think that allows use to link local data to these behind-the-scenes data. While some data are inherently unique, some just are not. The APEX_DEVELOPER_ACTIVITY_LOG is one such view. If you copy data from this view to a local table, the process of linking back is cumbersome.

Second, I have an easy time envisioning a packaged application that brings Team Development tools and developer activities together such that the application and its related data are portable. We lost such stuff when we moved from 11g to 12c. Team Development data was lost when we moved from 4.x to 5.x. And we are developers, we “improving” stuff (whether or not it actually improved!). We’re looking at this potential from the edge. APEX is a awesome tool.

The post Oracle APEX_APPLICATION Synonyms appeared first on Oracle Blog .

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Christina Moore Oracle Blog by Christina Moore - 1y ago

While I love Oracle Application Express, I have experienced frustration with generating stunning printed material. APEX Office Print (AOP) has simultaneously expanded our team’s capabilities while reducing our effort. That’s a tool! Rather, that is the definition of a great tool. Our ancestors used antlers to dig in the dirt, then someone came along with the steel spade. I feel the same way about AOP. In a previous entry, we touched on the structure of the SQL needed to prepare the data for AOP. Three months later, I still refer back to this blog for quick answers and structures. In this blog, we will explore AOP Text Formatting.

In the recent months, our team has:

  1. Created data-rich reports with grouping, sub-totals, running totals all formatted in MS Excel and handed back as PDF
  2. Developed text-rich reports that generate glorious MS Word documents — Easy and affordable database driven Word documents
  3. Added both conditional formatting and conditional data to reports

For one of our commercial applications (Tempest-Bid), we create proposals. Customers use our software to respond to government requests for proposals. These are highly structured documents with hundreds (thousands) of building blocks, often involve multiple technical writers, dozens of resumes, and an output that must be perfect. For the purpose of this example and blog, we are simplifying the proposal down. We use the US Constitution as a sample of the process. And for this blog, we’ll show it being assembled in Oracle APEX and AOP.


We are tool-builders. We pride ourselves on great support, but… we really don’t want to have to bill a customer because they want to change a footer, a font, or some other formatting within a report. Let’s give that power back to the user. AOP now let’s us share this with our customers. Want Arial 12? Go for it! Want narrower margins? Help yourself!

Not answers that have been routinely available within APEX.

In this entry, we will focus on

  • Sharing Template management with the customer
  • Linking AOP query with Templates

Our report menus are data driven (dynamic list). The basics are the source page (menu page) and the destination page (APEX page hosting the report). Incorporating AOP to this process means adding the fields needed to support the AOP plugin. Recently, we added a separate child table for report templates.  We can load templates from our menu, identifying templates as “Global” or customers can add their own templates, which are then identified as “Private”.

AOP is also data driven. The data for the AOP report derives from a query. But so does the identification of the template. This means that you can provide a select list of available templates to your users. To get there, we needed a means of managing templates.

Open the collapsible region to review, edit, or add new templates to a report.


When writing an AOP query, we adopt the same thinking we use on Interactive Reports. What are ALL of the options that the user may want? What fields should we prepare for them? What formatting is required? The challenge for us is to anticipate needs down the road.


And we need to prepare a template with full instructions on it for the user. We call this (in the data and in the image above) “Example”. We put all of the fields with definitions. We put instructions and coaching.

Preparing CLOBs

There is a trick when managing text in CLOBs. The data exchange between Oracle and AOP is done in JSON. The hand off strips the carriage return/line feed that maintains the integrity of paragraphs and lists. The guys at AOP look for a \n in the data, then substitute the CR/LF. It is for us to  prepare the data with the \n.

  ,replace(replace(sn.snippet_clob, chr(10), ''), chr(13), ' \n ')                "snippet"

This replace statement pulls the CR/LF — chr(10) chr(13) — from the data and lays in the \n as required by AOP.

AOP Template for Users (in 41 seconds) - YouTube
The Output

The following image is a report generated from data within Oracle, managed by APEX and “printed” by AOP into MS Word.


While the example shown uses three fields, there are more available for the user on this report. Because we are curious geeks, here is the underlying query:

	'file1' as "filename",
  cursor ( select
     cursor (
  ,c.client_name                "client"
  ,p.proposal_name              "proposal"
  ,r.name                       "require_name"
  ,r.description                "require_description"
  ,r.wbs_sequence               "wbs_sequence"
  ,r.sequence_order             "require_sequence"
  ,r.proposal_section           "proposal_section"
  ,r.proposal_section_title     "section_title"
  ,(select first_name || ' ' || last_name from bid_asset where asset_pk = sn.submit_asset_fk and rownum = 1) "submit_first_last"
  ,(select last_name || ', ' || first_name from bid_asset where asset_pk = sn.submit_asset_fk and rownum = 1) "submit_last_first"
  ,sn.TITLE                       "snippet_title"
  ,replace(replace(sn.snippet_clob, chr(10), ''), chr(13), ' \n ')                "snippet"
  ,sn.APPROVED                    "approved"
  ,to_char(sn.CREATED_ON,'MM/DD/YYYY')  "created_on"
  ,sn.CREATED_BY                        "created_by"
  ,to_char(sn.UPDATED_ON,'MM/DD/YYYY')  "updated_on"
  ,sn.UPDATED_BY                        "updated_by"
  ,r.outline_sequence                   "outl_seq"
from bid_require_snippet sn
left join bid_client c on
  c.client_pk = sn.client_fk
left join bid_proposal p on
  p.proposal_pk = sn.proposal_fk
left join bid_require r on
  r.require_pk = sn.require_fk
where nvl(sn.archived,'N') <> 'Y'
  and nvl(sn.approved,'N') = 'Y'
  and sn.proposal_fk = :G_PROPOSAL_PK
order by r.outline_sequence, r.proposal_section 
    ) as "d"
 from dual ) as "data"
from dual
Example Template

With users being able to download templates, modify them, upload modifications or new ones, we wanted to provide instructions. The image below illustrates one of these “examples”.


The post AOP Text Formatting appeared first on Oracle Blog .

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview