How Generative AI can save you time programming menial tasks

 To paraphrase the rock band Staind, It's been a while since I blogged.  And since every tech blog these days is talking about AI, I figured I'd join the bandwagon.

If you're a novice to AI, the first thing you always want to do is protect any sensitive data.  Unless, your company has privatized their LLM, never send any PII (Personally Identifiable Information) to a public LLM.

With my Google One membership, I have access to Gemini Pro, so that's the provider I've been using to assist with my coding.   I'm currently using Oracle Database 19c with Oracle APEX 24.2.

Where I started this was when I had to re-write an integration to a REST API.  The payload is in JSON.  As any one can tell you, one of the most time consuming tasks is parsing JSON because every payload is different, it's a standard that everyone uses slightly differently.   So, I took the test payload and told Gemini "For the attached JSON file, write a pl/sql parser.  Assume an Oracle 19c database and ensure that the parser can be used for any data within this JSON structure".  Within seconds, a pl/sql procedure was presented to me, with the following notes

One of the main issues with the JSON payload provided is that each element could potentially have children and each child can have their own children (and repeat forever).. The first iteration didn't handle this, so I had to tweak my prompts slightly to get the exact results I wanted.  

While the initial code would work, it wasn't the most efficient use of parsing.. It used JSON_TABLE, so I simply asked it to use JSON_QUERY

JSON Payloads typically contain much more info than needed, so I was able to prompt Gemini to only give me the data I required.  Once the code was done, I tested and received an error.. So I told Gemini the error, and it corrected it.

Any other errors I received, I sent and it fixed quickly.. clearly it doesn't compile and test :).  I even was able to tell it to re-write code using my system's coding standards and the code was generated instantly.  

THE MOST IMPORTANT THINGs TO REMEMBER WHEN USING AI TO CODE ARE REVIEW AND TEST!! AI can make mistakes, you need to ensure the codes works exactly as you need it to, and ensure it's written to your standards.

Using Gemini to process these files was a major time saver to me, and allowed me to focus on other things.  Learning the correct way to prompt Gemini is key to delivering the desired results.  What could have taken me several hours to write, took under an hour (with testing an error handling).

How Do I initialize/deploy data to production using Liquibase?

 I encountered a situation recently where I added a field to a table, and needed to update data due to that field.

I realized manually doing this when I deployed was obviously a bad idea, so I did some research into how liquibase works, and my solution was pretty easy using this method 

But, I had some challenges

1. How do I ensure that my script won't get overwritten when someone is working on a separate branch?
2. How do I ensure that my script will only be run once and not multiple times?

Here is my solution:

We have a requirement that all branches be named after the support/dev ticket # you're working on.   So, for argument sake, let's pretend I'm working on Ticket 1234, and my branch is called Ticket_1234.

Our back-end liquibase changelogs are stored in GIT in CHANGELOGS\DB folder. 

There are 4 steps I did

1. Created a folder within my CHANGELOGS\DB called SCRIPTS
2. Added a line at the end of my controller.xml
   
   <include file="scripts.xml"/>
3. To ensure uniqueness, our format for a script file is script_<ticket#>_<script#>.sql.  So in this example, my first script would be script_1234_1.sql.  If I made a second one, script_1234_2.sql, etc., and we save the script in the SCRIPTS directory
4. Created a new file called scripts.xml.  We match the changeset ID # to the script #, so when liquibase runs it, it stores it in the DBCHANGELOG table, and this way, we won't encounter any duplications when deploying.  Sample File Below
   
   
   <?xml version="1.0" encoding="UTF-8"?>

   <databaseChangeLog

     xmlns="http://www.liquibase.org/xml/ns/dbchangelog"

     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

     xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog

                         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">

    

     <changeSet author="scott" id="script_1234_1">

           <sqlFile dbms="!h2, oracle, mysql"

               encoding="UTF-8"

               endDelimiter="/"

               path="SCRIPTS/script_1234_1.sql"

               relativeToChangelogFile="true"

               splitStatements="true"

               stripComments="true"/>

     </changeSet>

   </databaseChangeLog>

Happy Coding!

Discipline, Patience and Confidence or How I Learned to Stop Worrying and Love Git and Liquibase

It's been a while since I updated for 2 reasons

1. I'm extremely lazy with posting and keeping up with things
2. I really haven't done anything that warranted a blog post

That all changed a few months ago, but before we get into that, let me bring you up to speed.  

I've been working with the Oracle Database since 2004.  I co-created an application for the mortgage industry utilizing Oracle Application Express (then HtmlDB version 4) and Oracle Database 10g.  We didn't use source control at all with that application, and honestly, at that time, I wasn't even sure what it was.

My next career path took me to an ISV with an Apparel ERP.   We were using MS Visual Sourcesafe on our code base, but we didn't really have a great way of promoting code in an easy way.  We also had hundreds of customers with customization and custom code that really made auto-deployment extremely difficult.   I also wasn't the best with remembering to check-in/check-out code, and had to be reminded frequently.

For the last several years, I've been a member of a small team working on replacing a legacy system.  Our application is an "evolutionary" approach to replacement. 

Sometimes during this process, I'd feel more like a firefighter than a developer.  Fixing errors as quickly as possible.   I'd rush changes out to production without remembering to update development or doing proper testing.  This would work for the immediate, but with the next update, bam that fix would be lost for obvious reasons.

Obviously, this is not a great way to work.   We had been mandated by management to enforce version control, and deployment logging.  As, we were using some Oracle Cloud products, we were given free access to Oracle's Visual Builder Studio Cloud Service.  This provided us with a hosted GIT service similar to Github.   So, I proceeded to set us up with GIT for managing our code base.

Honestly, understanding GIT terminology was a bit difficult for me.  "Checking Out" has a completely different meaning on SourceSafe than it does in GIT.   And, I wasn't too familiar with the command-line interface, so I began using the GIT integration in Oracle SQL Developer.   However, I didn't understand branches and merges, so all I was doing was making a change, and committing it to the Master, just for basic source control.  The other issue was that the other developers on the team weren't using GIT, so the potential for disaster was real.

Obviously, there was a need for change... So, earlier this year, I was poking around on Twitter (I refuse to call it "x"), and saw this post from SQL Product Manager Jeff Smith 

Having nothing to lose, I sent an email asking for some assistance with CI/CD (Continuous Integration/Continuous Deployment).   Within 30 minutes, Jeff replied to me, and introduced me to Zachary Talke, another Product Manager on the Database Tools team.   Zach setup a video call with my development team, and demonstrated Liquibase to us, and how we can use it to promote our changes.

Of course, as things always do, I got busy for a few months, so didn't pick this up again for 2 more months.   And, by then, I had forgotten everything I had learned.  Zach had sent me a few Workshops that I'm linking here, which were very useful

• SQLcl Liquibase Concept Overview Workshop
• Deploying a Full Stack Development Project With SQLcl Liquibase Workshop

So, I did what everyone does when they need to learn something, they go on YouTube.  I found a pretty detailed video specifically regarding Oracle APEX and Liquibase that I also highly recommend.

• Continuous Integration & Delivery (CI/CD) for Oracle Database and APEX Development

During the setup for our implement, I had many questions arise that looked weird to me.  Zach and Jeff would typically reply to emails within 1 to 2 business days, which is a great turn-around for basically "free support"

Our process while it seems complicated, actually became fairly simple

• Ticket must be created in our separate ticketing system
• Branch name must be ticket_<ticket_number>
• all DDL types (packages,views,functions,triggers,etc) must be saved as .sql files in the file system and commited on save
• LIQUIBASE changelogs must be generated for all objects to move between DBS (and committed to GIT)
• If new additions, update changelog.xml to include any new liquibase changelogs
• End-User Testing must be done on separate test system
• Once testing is complete, merge request must be made on cloud
• Merge can only be done after another developer and manager approve request
• Only way to deploy to production is via Liquibase

Here are some helpful Liquibase command

• lb generate-db-object -object-type <OBJECT_TYPE> -object-name <NAME> -ovf
• replace <OBJECT_TYPE> with PACKAGE_SPEC,PACKAGE_BODY,VIEW,TABLE,ETC
• replace <NAME> with the name of the object
• -ovf is for overwriting files..this will update an existing file instead of creating a new one
• lb generate-apex-object -applicationid <ID#>-skipexportdate -exporiginalids -split -ovf -runalways
• replace <ID#> with your applicationid
• -skipexportdate will not add an export date in your files, making sure only updated ones have changes
• -exporiginalids will emit ids as they were when app was imported, again to only update files that have changed
• -split will split the application into several files so you have 1 file per page, and will only update that page instead of the whole app
• -ovf same as above
• -runalways - whenever you export apex, it has the same changelog ID INSTALL_ID#.. this will ensure the app will run no matter if the changelog id is in the table

Before going live, I created a new branch for setup and did an "LB generate-schema" from our production system.. this created a changelog for every object in the schema.   I then did a datapump export/import from production to our live and test systems.   Once the import was done.. to ensure everything was "in sync" ... I performed lb -changelog-sync -chf controller.xml... this updated the databasechangelog table to ensure that all changelogs in live were applied to test and dev, this way only future changelogs would be affected

As of now, our system stability has increased significantly... I no longer fear deploying software.  I can view where mods are without having to read code, and just check the commit logs.  We have full auditability for deployment, which is great.

Here are a few challenges I still face

• I have subscribed my APEX plugin settings to a master app, but when I install my app from dev->live, it takes the plugin settings, and then I have to unsubscribe, change the settings, and re-subscribe
• There seems to be inconsistent behavior when I change a page that has user saved Interactive Reports and Interactive Grids.  I'm trying to isolate the exact situation here to report it, but I have had a few reports of saved reports/settings disappearing
• We are doing all our liquibase commands manually and have to type them in.. I want to investigate a full automatic deployment system to simplify this process

I also need to give a shout out to my friend Mark Wrynn.  He's the one that hired me 20 years ago, and shaped my career with Oracle.  His advice and help was invaluable specifically on the GIT side.

Separating Production and development apps

I had an issue the other day where one of my users logged into a development app accidentally instead of production.

So, I wanted something to let the user know they weren't in production, but not something I'd have to change when I migrated the app to production.

My development and test apps share a domain name, my production is on a separate domain.

Javascript has a helpful function that returns your host name "window.location.hostname"

So, I created a dynamic "onLoad" action on my login page.. with a true action of "Execute Javascript"

2 lines below are all that were needed (replace hostnaame and text as needed)

if (window.location.hostname == "hostname.com")
    window.alert("This is the development system.  Any changes made here will not be made in live.");

Simple and efficient.

Cheers,
Scott

Dynamic Interactive Reports

Within our ERP package, we have what are called "Viewers".. these are basically, a list of Views we've created to allow customers to view data and download to excel.  On Apex, I needed a way to dynamically show these in interactive reports.  One of the issues I was facing, is that interactive reports require Static SQL, not functions returning SQL.

I found this site which came in real handy for achieving what I wanted

http://www.oracleapplicationexpress.com/tutorials/71

One limitation that was hard though was the 50 column maximum.. however, thinking in Web based verses client based.... loading a report with more than 50 columns will be extremely slow.

Building a mobile site in Oracle Application Express

So.. it's been quite a while since I've blogged.. yeah I'm a little lazy, but I finally found a topic to blog about.

The technology world is a constantly changing place. Our current ERP system is written in Oracle Forms and Reports. While that works for a good percentage of customers, there are some that are ready to explore the new. Tablet/Mobile Processing is the current "trend" in technology. Our customers were looking for a lightweight solution that integrates with our product for salespeople.

We had a few criteria:

1. Ease of Use
2. Multiple Hardware/OS Capability
3. Look and Feel

Due to the Multiple Hardware/OS, we did not feel like creating an "app store" app, so we decided to go with the web app approach. I have been using Apex for about 7 years now, and I decided to look into using this to get what I needed.

Doing some web searches, let me to the mobile site for the Philadelphia Cricket League this is a site done in Apex using the iWebkit framework. Reading the documentation I began experimenting.

To start, I chose Apex Theme 10 at first, but then switch to 13 as my starting point. I extracted the iWebkit and saved it in the images folder for Apex.

I chose the No Tabs page as my default and edited my page layout and added tags for the iwebkit stylesheet and javascript so that it can be called.

<html lang="&BROWSER_LANGUAGE." xmlns="http://www.w3.org/1999/xhtml" xmlns:htmldb="http://htmldb.oracle.com">

<head>

<meta content="yes" name="apple-mobile-web-app-capable" />

<meta content="text/html; charset=iso-8859-1" http-equiv="Content-Type" />

<meta content="minimum-scale=1.0, width=device-width, maximum-scale=0.6667, user-scalable=no" name="viewport" />

<meta http-equiv="content-type" content="text/html; charset=utf-8">

<meta name = "viewport" content = "width=device-width">

<meta name = "format-detection" content = "telephone=no">

<link rel="apple-touch-icon" href="#IMAGE_PREFIX#A2000.ICO"/>

<link href="#IMAGE_PREFIX#iwebkit/css/style.css" rel="stylesheet" type="text/css" />

<script src="#IMAGE_PREFIX#iwebkit/javascript/functions.js" type="text/javascript">

</script>

<link rel="stylesheet" href="#IMAGE_PREFIX#iwebkit/sw/spinningwheel.css" type="text/css" media="all" />

<script type="text/javascript" src="#IMAGE_PREFIX#iwebkit/sw/spinningwheel-min.js?v=1.4"></script>

<link rel="stylesheet" href="#IMAGE_PREFIX#themes/theme_13/theme_4_0.css" type="text/css" />

#HEAD#

<title>#TITLE#</title>

</head>

<body #ONLOAD#><noscript>&MSG_JSCRIPT.</noscript>#FORM_OPEN#<a name="PAGETOP"></a>

I also changed the body to have the default look and feel I want

<!--<table summary="" cellpadding="0" cellspacing="0" border="0" width="100%">

<tr>

<td valign="top"><a id="t13Logo2" href="#">#LOGO#</a><br />#REGION_POSITION_06#</td>

<td width="100%" valign="top">#REGION_POSITION_07#</td>

<td valign="top">#NAVIGATION_BAR#<br />#REGION_POSITION_08#</td>

</tr>

</table>

<div id="t13BreadcrumbTop"> </div>

<table class="t13Layout" cellpadding="0" cellspacing="0" border="0" summary="" width="100%">

<tr>

<td class="t13BreadcrumbRegion"><div class="t13Breadcrumbs">#REGION_POSITION_01#<span id="t13Customize">#CUSTOMIZE#</span></div></td>

</table> -->

<div id="topbar">

<div id="title">A2000 Mobile</div>

<div id="leftnav"><a href="f?p=&APP_ID.:1:&APP_SESSION.">

<img alt="home" src="#IMAGE_PREFIX#iwebkit/images/home.png" /></a></div>

</div>

<a name="SkipRepNav"></a>

<div id="t13MessageHolder">#SUCCESS_MESSAGE##NOTIFICATION_MESSAGE##GLOBAL_NOTIFICATION#</div>

<div class="t13BodyMargin">

<table summary="" cellpadding="0" cellspacing="0" border="0" height="70%">

<tr>

<td valign="top" width="100%">#BOX_BODY##REGION_POSITION_02##REGION_POSITION_04#</td>

<td class="t13ColumnSep"><div class="t13ColumnSep"><br /></div></td>

<td valign="top"><div style="float:right;">#REGION_POSITION_03#</div></td>

</tr>

</table>

</div>

I then used this template on all my pages.

iWebkit works entirely in unorganized lists, which are all pretty well documented in their documentation... Apex works with Table Structure... how to overcome this?

To do "reports" was pretty simple. I used a pl/sql Region to dynamically output html in the format I wanted

htp.p('<div id="content"><span class="graytitle">Choose Color</span><ul class="pageitem">');

for rec in (select cc.color_no,nvl(cc.color_descr,c.descr) col_descr

from color_style cc

inner join color_mast c

on cc.color_no = c.color_no

where cc.style = :p7_style

and cc.active = 'Y'

order by color_no)

loop

htp.p('<li class="menu">');

htp.p('<a href="f?p=&APP_ID.:2:&APP_SESSION.::NO:RP,2:P2_STYLE,P2_COLOR_NO,P2_LAST_PAGE:' || :P7_STYLE ||',' || REC.COLOR_NO ||',7">');

htp.p('<span class="name">' || REC.COLOR_NO || ' - ' || REC.col_DESCR || '</A></span>');

htp.p('<span class="arrow"</span></li>');

end loop;

htp.p('<li class="menu"><a href="f?p=&APP_ID.:14:&APP_SESSION."><span class="name">Back</span></a></li>');

htp.p('</ul></div>');

Outputting form elements was a little trickier... What I needed to do here was have the elements in 1 Region, and setting all elements in the same column/row

Then to add the list elements, I used the pre-element and post-element properties to get the correct format

I hope this helps you with your Mobile development until Apex comes out with a full mobile layout.

My first database post

Wow, since I do everything that Mark does (http://mwrynn.blogspot.com/) I've decided to start my own Oracle Blog. This will discuss my experiences dealing with Oracle database, Forms/Reports, and ApEx. I plan to write more when I have some time.

-Scott