In 2023, I announced the launch of the ➡️ APEX Developer Blogs ⬅️ Website. This website aggregates blog posts with content relevant to Oracle APEX Developers and provides a one-stop shop for searching and subscribing to blog posts related to Oracle APEX Development.

This post will provide insights into the tech behind the website and some of the features of APEX & ORDS that made it possible.

You are viewing: Which Is True Of Blogs Apex

The app allows users to register their blog easily:

The user is taken through a simple Wizard, where they select their blogging platform and enter a few details about their blog. The App calls the feed related to the blog to default the Blog Name and Author (see below). Once registered, the blog is added to the blogs table in a Pending status. I get notified and can elect to accept the blog (assuming it is relevant to APEX Developers).

The method for obtaining the blog author, title, etc, from the Blog differs based on the platform.

Hashnode

Hashnode is a modern, easy-to-use blogging platform. It also provides a comprehensive Graph API, which was recently re-vamped and provides unprecedented ease of access to all of the blogs hosted by Hashnode.

• API Docs

• API Playground

The following code shows how I call the Hashnode API to get details about a specific blog. I start by declaring a constant in the PL/SQL package with JSON that fetches information about a Publication on Hashnode. The constant includes a substitution variable #HOSTNAME#, which I can later replace with the actual publication URL.

– Constant with GC_PUB_QUERY CONSTANT VARCHAR2(32000) := q’# {“query”:”query Publication {n publication(host:”#HOSTNAME#”) {n id,n title,n descriptionSEO,n url,n author{name, profilePicture},n displayTitle n} n}”,”variables”:{}} #’;

Then, I call a function that substitutes #HOSTNAME# with the Blog URL provided by the user, call the Hashnode API using APEX_WEB_SERVICE, and parse the response. JSON_TABLE offers us a quick and easy method for parsing the response.

FUNCTION hashnode_publication (p_blog_url IN VARCHAR2) RETURN cnba_blogs%ROWTYPE IS l_pub_response CLOB; l_pub_query VARCHAR2(32000); lr_blog cnba_blogs%ROWTYPE; l_domain VARCHAR2(100); BEGIN – Extract the Domain for the Blog. l_domain := apex_string_util.get_domain (p_blog_url); – Replace the #HOSTNAME# part of the payload with the domain. l_pub_query := REPLACE(GC_PUB_QUERY, ‘#HOSTNAME#’, l_domain); – Call the Hasnode API. l_pub_response := apex_web_service.make_rest_request (p_url => ‘https://gql.hashnode.com/’, p_http_method => ‘POST’, p_body => l_pub_query); IF apex_web_service.g_status_code <> 200 THEN raise_application_error (-20001, ‘Error Fetching Hashnode Publication’); END IF; – Parse the Response from Hashnode and Populate a PL/SQL record – with details of the Blog. BEGIN SELECT jt.title , jt.url , jt.author , jt.author_pic , COALESCE(jt.display_title, jt.description_seo, ‘No Description’) INTO lr_blog.title , lr_blog.blog_url , lr_blog.blog_owner , lr_blog.avatar_url , lr_blog.description FROM JSON_TABLE(l_pub_response, ‘$.data.publication’ COLUMNS (title VARCHAR2(500) PATH ‘$.title’, url VARCHAR2(500) PATH ‘$.url’, author VARCHAR2(500) PATH ‘$.author.name’, author_pic VARCHAR2(500) PATH ‘$.author.profilePicture’, display_title VARCHAR2(4000) PATH ‘$.displayTitle’, description_seo VARCHAR2(4000) PATH ‘$.descriptionSEO’ )) jt; EXCEPTION WHEN OTHERS THEN raise_application_error (-20000, ‘This does not seem to be a Hashnode Blog. Please double check your platform and the URL.’); END; – Return the record type with details about the Blog. RETURN lr_blog; END hashnode_publication;

As you can see from the code above, several useful APEX PL/SQL APIs like apex_string_util and apex_web_service, together with native JSON parsing with JSON_TABLE make this process easy.

RSS/Atom/WordPress/Blogger

All other blogging platforms utilize some kind of RSS or Atom Feed. This forces us to take a trip back to the world of XML parsing 😱.

The scripts below show how I extract the Blog title, description, etc., for these other blogging platforms using XMLTABLE to parse the XML response and APEX_WEB_SERVICE to get the content of the Blog feed.

– RSS, Blogger, WordPress SELECT bl.* FROM XMLTABLE(XMLNAMESPACES(‘http://purl.org/dc/elements/1.1/’ AS “dc”), ‘/*/channel’ PASSING XMLTYPE(apex_web_service.make_rest_request(p_url => :BLOG_URL, p_http_method => ‘GET’)) COLUMNS last_build_date VARCHAR2(50) path ‘lastBuildDate’, last_build_date2 VARCHAR2(50) path ‘pubDate’, language_code VARCHAR2(10) path ‘language’, language_code2 VARCHAR2(10) path ‘dc:language’, generator VARCHAR2(50) path ‘generator’, title VARCHAR2(500) path ‘title’, blog_link VARCHAR2(500) path ‘link’, description VARCHAR2(500) path ‘description’, image_url VARCHAR2(500) path ‘image/url’ ) bl; – Atom SELECT bl.* FROM XMLTABLE( XMLNAMESPACES(‘http://www.w3.org/2005/Atom’ AS “ns0”), ‘ns0:feed’ PASSING XMLTYPE(apex_web_service.make_rest_request(p_url => :BLOG_URL, p_http_method => ‘GET’)) COLUMNS title VARCHAR2(255) PATH ‘ns0:title’, description VARCHAR2(500) PATH ‘ns0:subtitle’, generator VARCHAR2(500) PATH ‘ns0:generator’ ) bl;

Once a blog is registered, I have an APEX Automation scheduled to run every evening. This fetches any newly published blog posts. The diagram below illustrates how this is done.

1. An APEX Automation runs every evening. The automation loops through all active blogs.

2. The APEX Automation checks for new blog posts not already in the database for each active blog.

3. For Hashnode, I call the Hashnode API again. The Hashnode API makes searching for and fetching the content of blog posts a breeze 🍃. Fetching the content of XML-based platforms like RSS and Atom is more challenging (more on this below).

4. Any new blog posts are added to a table in the Oracle database.

5. Once all the new posts have been captured, the Automation performs post-processing on each post. This involves calculating a word count, calculating reading time, generating a plain text version of the post (stripping HTML), etc.

6. Finally, I call Chat GPT, passing the first 4,000 characters of the post along with a prompt asking Chat GPT to determine if the new post is relevant to Oracle APEX Developer (more on this below).

7. If Chat GPT deems the new post irrelevant to Oracle APEX Developers, then the post is marked as excluded. Excluded posts are not indexed and do not show up on the APEX Developer Blogs App.

Fetching Blog Posts

Like blogs, fetching blog posts differs based on the platform.

Hashnode Posts

The Hashnode APIs make fetching blog posts easier and provide a richer dataset. To call the Hashnode APIs from PL/SQL, I follow a similar approach as I do for Blogs. I create constants that contain a template for the Graph QL query I want to execute and then substitute the actual value just before I call the Hashnode APIs.

At a high level, my code does the following:

• Query the feed from the Blog and return just the post URL and Slug. This returns posts from the blog, starting with the latest.

• I check to see if I have each post in the feed in the database. If I do not have the post, I query a second Hashnode API to get the post content.

• As soon as I encounter a post that I have in my DB, I stop processing the feed.

This approach means I don’t have to return the entire blog content until I know I need it. It also means that most times when I check the feed, I already have the first post in the local DB, so I can stop processing immediately. This makes processing the Hashnode API very fast.

Read more : Which Street Fighter Character Are You

I use the JSON below as a template for Querying a Blog’s feed (GC_HN_FEED_QUERY) and then getting the blog’s content (GC_HN_POST_QUERY).

GC_HN_FEED_QUERY CONSTANT VARCHAR2(32000) := q’# {“query”:”query Publication {n publication(host:”#HOSTNAME#”) {n posts (first: 20 #AFTER#){n edges{n node {n title,n slugn }n }n pageInfo {n endCursorn hasNextPagen }n }n }n}”,”variables”:{}} #’; GC_HN_POST_QUERY CONSTANT VARCHAR2(32000) := q’# {“query”:”query Publication {n publication(host:”#HOSTNAME#”) {n post (slug:”#SLUG#”){n url,n coverImage {url},n author{name}, n brief,n readTimeInMinutes,n publishedAt,n content {html,text}n }n }n}”,”variables”:{}} #’;

RSS/Atom/WordPress/Blogger Posts

All of these platforms return a similar but slightly different XML feed (don’t get me started on standards). This section lists the SQL statements I use to process blog posts from these platforms.

– RSS Feed SELECT ai.* FROM xmltable( XMLNAMESPACES(‘http://purl.org/dc/elements/1.1/’ AS “dc”), ‘/*/channel/item’ PASSING XMLTYPE(apex_web_service.make_rest_request(p_url => :BLOG_URL, p_http_method => ‘GET’)) COLUMNS guid VARCHAR2(500) path ‘guid’, date_published VARCHAR2(500) path ‘pubDate’, title VARCHAR2(500) path ‘title’, creator VARCHAR2(255) path ‘dc:creator’, author VARCHAR2(255) path ‘author’, link VARCHAR2(500) path ‘link’, description CLOB path ‘description’, cover_image VARCHAR2(500) path ‘cover_image’ ) ai; – Atom Feed SELECT TO_UTC_TIMESTAMP_TZ(bp.date_published) date_published , TO_UTC_TIMESTAMP_TZ(bp.date_updated) date_updated , bp.guid , bp.title , bp.author , NVL(bp.link, bp.link_alt) link , bp.description , bp.content FROM XMLTABLE(XMLNAMESPACES(‘http://www.w3.org/2005/Atom’ AS “ns0”), ‘ns0:feed/ns0:entry’ PASSING XMLTYPE(apex_web_service.make_rest_request(p_url => :BLOG_URL, p_http_method => ‘GET’)) COLUMNS guid VARCHAR2(255) PATH ‘ns0:id’, date_published VARCHAR2(50) PATH ‘ns0:published’, date_updated VARCHAR2(50) PATH ‘ns0:updated’, title VARCHAR2(500) path ‘ns0:title’, author VARCHAR2(255) path ‘ns0:author/ns0:name’, link VARCHAR2(500) path ‘ns0:link[@rel eq “alternate”]/@href’, link_alt VARCHAR2(500) path ‘ns0:link[1]/@href’, description VARCHAR2(500) path ‘ns0:summary’, content CLOB path ‘ns0:content’ ) bp – Blogger Feed SELECT ai.* FROM xmltable(‘/*/channel/item’ PASSING XMLTYPE(apex_web_service.make_rest_request(p_url => :BLOG_URL, p_http_method => ‘GET’)) COLUMNS guid VARCHAR2(500) path ‘guid’, date_published VARCHAR2(500) path ‘pubDate’, title VARCHAR2(500) path ‘title’, author VARCHAR2(255) path ‘author’, link VARCHAR2(500) path ‘link’, description CLOB path ‘description’, cover_image VARCHAR2(500) path ‘cover_image’ ) ai; – WordPress Feed SELECT ai.* FROM xmltable( XMLNAMESPACES(‘http://purl.org/dc/elements/1.1/’ AS “dc”, ‘http://purl.org/rss/1.0/modules/content/’ AS “content”), ‘/*/channel/item’ PASSING XMLTYPE(apex_web_service.make_rest_request(p_url => :BLOG_URL, p_http_method => ‘GET’)) COLUMNS guid VARCHAR2(500) path ‘guid’, creator VARCHAR2(255) path ‘dc:creator’, date_published VARCHAR2(500) path ‘pubDate’, title VARCHAR2(500) path ‘title’, link VARCHAR2(500) path ‘link’, content CLOB path ‘content:encoded’ ) ai;

Using Chat GPT to Assess Relevance

One problem I have is that, occasionally, people like to post about things not directly related to their blog. Don’t get me wrong, I think this is great, and I encourage everyone to step outside their comfort zone.

Previously, I did this manually. I would log in to an Admin App every few days and mark posts as excluded. This moved them to another table and excluded them from the Website and search. I recently introduced code to call an Open AI Completions API to assess the post’s relevance to Oracle APEX Developers.

JSON Mode

Introducing Open AI calls into your code relies on your ability to parse and interpret the response into specific fields. This is made possible using JSON Mode when calling the Open AI API. This forces the API to return JSON. If you craft your prompt carefully, you can get JSON back with consistent fields. This makes it easy for you to consume.

The Prompt

I am sure you have already heard that the right prompt can make a huge difference to the quality of the response you get from Open AI. As of Jan 2024, this is the prompt I am using, but I am sure this will evolve.

You are an Oracle Technology blog analyzer assistant. Your goal is to determine if the blog post is related to Oracle Technologies. You will receive the content of a blog post and analyze it. Score the blog post on a scale of 0-5 indicating how relevant the post is to this comma separated list of topics: Oracle Application Express (APEX), Oracle REST Data Services (ORDS), Oracle Cloud Infrastructure (OCI), Oracle SQL (SQL), Oracle PL/SQL (PLSQL). Return a JSON object with each topic and its score. Use the value part of the topic name inside () as the topic JSON field name. A score of 0 should indicate not relevant at all an 5 indicates extremely relevant.

The Code

Armed with an Open AI API Key I can call Open AI using the PL/SQL code below.

This function generates JSON to send to Open AI:

• p_model is the Open AI Model the API should use

• p_system_msg is the prompt we want to send with our request.

• p_user_msg is the content of the blog post (I only pass the first 4,000 characters to limit the number of tokens I consume).

FUNCTION ai_payload_json (p_model IN VARCHAR2, p_system_msg IN VARCHAR2, p_user_msg IN VARCHAR2) RETURN CLOB IS l_payload_obj json_object_t := json_object_t(); l_format_obj json_object_t := json_object_t(); l_message_obj json_object_t := json_object_t(); lt_messages json_array_t := json_array_t (); BEGIN – Build JSON Payload for Open AI Call. l_format_obj.put(‘type’, ‘json_object’); l_payload_obj.put(‘model’, p_model); l_payload_obj.put(‘response_format’, l_format_obj); l_message_obj.put (‘role’, ‘system’); l_message_obj.put (‘content’, p_system_msg); lt_messages.append (l_message_obj); l_message_obj.put (‘role’, ‘user’); l_message_obj.put (‘content’, p_user_msg); lt_messages.append (l_message_obj); l_payload_obj.put(‘messages’, lt_messages); – Convert Payload JSON to CLOB. RETURN (l_payload_obj.to_clob()); END ai_payload_json;

This procedure takes the payload generated by the above function and calls Open AI:

PROCEDURE call_open_ai (p_json_payload IN CLOB, x_response OUT CLOB) IS BEGIN – Set Header to tell Open AI to return JSON. apex_web_service.set_request_headers (p_name_01 => ‘Content-Type’, p_value_01 => ‘application/json’, p_reset => true); – Call the Open AI Completions API. x_response := apex_web_service.make_rest_request (p_url => ‘https://api.openai.com/v1/chat/completions’, p_http_method => ‘POST’, p_body => p_json_payload, p_transfer_timeout => 30, p_credential_static_id => ‘OPEN_AI_TEST’); IF apex_web_service.g_status_code <> 200 THEN raise_application_error (-20000, ‘Call to Open AI API Failed’); END IF; END call_open_ai;

If all goes well, I get a response like this:

{ “id”: “chatcmpl-8j6p4677Yq4HYQGlDYJzozo1gZTVt”, “object”: “chat.completion”, “created”: 1705761678, “model”: “gpt-3.5-turbo-1106”, “choices”: [{ “index”: 0, “message”: { “role”: “assistant”, “content”: “{n “APEX”: 5,n “ORDS”: 0,n “OCI”: 0,n “SQL”: 4,n “PLSQL”: 5n}” }, “logprobs”: null, “finish_reason”: “stop” }], “usage”: { “prompt_tokens”: 1102, “completion_tokens”: 40, “total_tokens”: 1142 }, “system_fingerprint”: “fp_aaa20cc2ba” }

The JSON we are looking for is embedded in the content field from the assistant response.

{ “APEX”: 5, “ORDS”: 0, “OCI”: 0, “SQL”: 4, “PLSQL”: 5 }

This gives me a clean set of data that I can use to determine whether to exclude a particular blog post.

The website can search the content of all the blog posts that have been captured by the process above.

Oracle Text

The engine behind the Search is Oracle Text. The UI for the search is embedded in an APEX Search Configuration. The search configuration references an Oracle Text Index Function:

I have also customized the Result Row template to improve the UI and reference the score returned by Oracle Text.

The Oracle Text Index function looks like this:

FUNCTION blog_end_user_search (p_search IN VARCHAR2) RETURN VARCHAR2 IS c_xml constant varchar2(32767) := ‘<query><textquery><progression>’ || ‘<seq> #SEARCH# </seq>’ || ‘<seq> ?#SEARCH# </seq>’ || ‘<seq> #SEARCH#% </seq>’ || ‘<seq> %#SEARCH#% </seq>’ || ‘</progression></textquery></query>’; l_search varchar2(32767) := p_search; BEGIN – remove special characters; irrelevant for full text search l_search := REGEXP_REPLACE( l_search, ‘[<>{}/*!$?:;+]’, ” ); – Escape _ l_search := REPLACE( l_search, ‘_’, ‘_’ ); RETURN REPLACE( c_xml, ‘#SEARCH#’, l_search ); END blog_end_user_search;

The Oracle Text Index uses the following procedure to allow users to search over the title and the blog content:

PROCEDURE blog_index (p_rowid IN ROWID, x_xml IN OUT NOCOPY CLOB) IS CURSOR cr_blog_post IS SELECT title , plain_content FROM cnba_blog_posts WHERE rowid = p_rowid; lr_blog_post cr_blog_post%ROWTYPE; BEGIN OPEN cr_blog_post; FETCH cr_blog_post INTO lr_blog_post; CLOSE cr_blog_post; x_xml := x_xml || TO_CLOB(‘<BLOG_POST>’); x_xml := x_xml || TO_CLOB(‘<TITLE>’) || TO_CLOB(lr_blog_post.title) || TO_CLOB(‘</TITLE>’); x_xml := x_xml || TO_CLOB(‘<CONTENT>’) || TO_CLOB(lr_blog_post.plain_content) || TO_CLOB(‘</CONTENT>’); x_xml := x_xml || TO_CLOB(‘</BLOG_POST>’); END blog_index;

Another key feature of the website is the ability for users to subscribe to a weekly digest email. This email lists all the APEX Developer-related posts from the past week.

The process works as follows:

• Sign-Up

  • Users provide their email and receive an email with a URL and token.

  • The token allows the user to access a page where they can verify their subscription.

  • Users can re-enter their email and get a new token anytime, allowing them to unsubscribe.

• Weekly email

  • An APEX Automation runs every week to send an email to all subscribers.

  • The apex_mail.send PL/SQL API is used to send the email. In my case, apex_mail uses the OCI Email Delivery Service to send the email.

  • I use APEX Email Templates to format the email.

As well as offering a UI to search APEX Developer blogs, I also offer two feeds:

• REST Feed Powered by Oracle REST Data Services (ORDS)

  • REST Feed URL

  • Because ORDS is easy to use, this feed was created with a single SELECT statement and exposed as a REST API. It even comes with comprehensive filtering capabilities using ORDS filtering Syntax.

• Atom Feed

  • Atom Feed URL

  • ORDS also powers the Atom feed, but the content is generated using the following code.

PROCEDURE atom_feed (p_page_num IN NUMBER) IS l_response CLOB; l_entries CLOB; l_offset NUMBER; l_latest_post_utc VARCHAR2(100) := get_latest_post_date; BEGIN – Calculate Offset. IF NVL(p_page_num,0) <= 0 THEN l_offset := 0; ELSE l_offset := (p_page_num * GC_ATOM_PAGE_SIZE) – GC_ATOM_PAGE_SIZE; END IF; – Build the Start of the XML for the Atom Feed using a Constant. l_response := TO_CLOB(REPLACE(GC_ATOM_XML_START, ‘#UPDATED#’, l_latest_post_utc)); – Add the Blog Posts for the current page. WITH posts AS (SELECT cbpv.post_id , cbpv.published_date , cbpv.post_title title , cbpv.summary , cbpv.link , cbpv.author , TO_CHAR(SYS_EXTRACT_UTC(cbpv.published_date),’YYYY-MM-DD”T”HH24:MI:SS”Z”‘) updated FROM cnba_blog_posts_v cbpv ORDER BY cbpv.published_date DESC, post_id OFFSET l_offset ROWS FETCH FIRST GC_ATOM_PAGE_SIZE ROWS ONLY) SELECT xmlagg (xmlelement (“entry”, xmlelement(“title”, title), xmlelement(“link”,xmlattributes(link AS “href”)), xmlelement(“updated”, published_date), xmlelement(“author”,xmlelement(“name”, author)), xmlelement(“summary”, summary), xmlelement(“id”, link) )).getclobval() INTO l_entries FROM posts ORDER BY published_date DESC, post_id; – Complete the Response. l_response := l_response || l_entries || TO_CLOB(‘</feed>’); – Set Mime Header and return response. owa_util.mime_header(‘application/atom+xml’); apex_util.prn ( p_clob => l_response, p_escape => false ); END atom_feed;

I want to finish this post by showing you an end-user view of the main features of the APEX Developer Blogs Website.

Home Page

The home page shows you the last 15 blog posts and a list of the Blogs on the platform.

You can search for content across all of the captured blog posts using an Oracle Text Search.

Blog Search

The About Page provides some background information and links to an Atom Feed and a REST API where you can access all of the blog posts captured by this site.

About Page

All Blog Posts Report

The All Blog Posts Report (accessible from the home page) provides an APEX Interactive Report showing all the blog posts captured by the website.

Trends

The Trends page shows stats like the most active bloggers and how many posts.

Email Subscription

Finally, the subscribe page allows you to sign up for a weekly digest email with all the APEX Developer-related blogs discovered during the week.

This section highlights some of the stats related to the APEX Developer Blogs Website. These stats were captured on Jan 23rd, 2024.

Most Popular Blog Platform for APEX Developers

Hashnode is by far the most popular platform for APEX Developers.

Blogs and Posts

Page Views

Email Subscribers

The Oracle Developer Blogs website is hosted on the OCI APEX Application Development Service. This is the same platform where I run my internal applications and demos.

I had a lot of fun building the APEX Developer Blogs Website and learned a lot while doing so. These kinds of side projects allow me to try out APEX Features (and other technologies) that I would not often need to use in my day-to-day job. Hopefully, the APEX community also finds the site useful.

Source: https://t-tees.com
Category: WHICH