Archive
SOLVED: Numeric or value error in Oracle APEX Interactive Grid
I have been working with Oracle APEX framework for work. I keep getting this error when saving data in an interactive grid:
APEX INTERACTIVE GRID ORA-06502: PL/SQL: numeric or value error: character to number conversion error
The application has been working for months, and then it breaks. I have no idea why. The error is happening when updating a Varchar field, so it’s certainly not table data type problem, even though, the error would lead you to believe that.
After hours, even days, of troubleshooting and only able to fix the problem by rebuilding the interactive grid, I found the root cause.
Under in the APEX development interface, for the page that is having the problem, in the Processing section, there is a process with type Interactive Grid – Automatic Row Processing (DML) which processes row updates when data is changed and saved by the user. Under the settings there’s an options Prevent Lost Updates which is intended to prevent two users from saving the same record differently. I turned this off, and it’s working now.
There is obviously a bug in Oracle’s code:
- I created a new record, then updated that same record, and received the error. There’s no way that record was opened by anybody else.
- If this is the “feature” doing it’s job, and making sure the same record can’t be edited twice by different users, the error message is extremely poor, frankly, its so unrelated, it can’t possibly be the intended error when an conflicting update is detected.
I was thinking of logging an Service Request with Oracle, but two problems here, this isn’t really a supported application, as it’s “free” with the database. And also, the Oracle support page isn’t working; lovely. It’s been broken for at least two days.
Solution: BI Publisher QR Codes
I saw a post on StackOverflow asking How to use variable in chart.apis.google link for QR Code and I was quite intrigued. I didn’t think it could be done at first. But then I figured it out.
I do know that BI Publisher has built in Chart support, so my first thought was to just use that. But BI Publisher charting cant generate QR Codes. None the less, I linked to BI Publisher Report Designers guide to explain those details if anyone stumbled upon this post looking for actual charts.
My second thought was to follow a blog entry that an Oracle developer posted about using QR Codes in BI Publisher. It mentioned making a java class, and using an IDAutomation QR Font. It was quite complex. This is probably the method to go after if you want all the data to remain internal to the Oracle server in the enterprises data-center. I posted the details to that too.
Finally, I remember using dynamic images at Emerson for insertion of Casting images into Work-orders/Shop-packets. I figured why not give it a shot. I created a quick XML test file, and a simple RTF file. I use the concat (concatenate) function in BI Publisher to combined the Google Charts API for QR codes and the variable I wanted encoded.
Test XML
<test_header>
<test_row>
<row_num>1</row_num>
<value>TESTA</value>
</test_row>
<test_row>
<row_num>2</row_num>
<value>TESTB</value>
</test_row>
</test_header>
Test RTF Template
RTF Template
Image ALT TEXT
url:{concat(‘https://chart.apis.google.com/chart?cht=qr&chs=500×500&choe=UTF-8&chld=H&chl=’,value)}
[when copy/pasting from this page, replace the x in the URL with one hand-typed.]
My first attempt failed because of a space in my test data. If you have spaces or special characters in the data you want encoded in the QR code, make sure you URL encode the data that you want to pass into the Google API URL. Oracle allows you to do this with the UTL_URL function. Also note: this post is being made with WordPress, the single quotes are improperly being replaced with smart quotes and/or HTML entities… use care if copying and pasting, and make sure to use the single quote as per screen shot.
Example use of UTL_URL.ESCAPE
select rownum row_num, utl_url.escape(record_value) value from test_table
Once I removed the space from my test data, it worked! I was shocked how easy it could be.
Test Output
PDF Output
Other Considerations
One last thing to note, if you expect this to work on your enterprise server/network/data-center, you’ll probably have to open up the firewall.
Updating Realtek Drivers for PFSense on a ZOTAC ZBox
A long while back I built a ZOTAC Zbox (ZBOX-CI323NANO-U) to run PFSense and act as my router. For the most part, it has worked pretty well. The features are great, without any of the gimmicky stuff you’d find on a consumer router. It’s also a lot more upgradable than a consumer router.
One problem though, on occasion I’d have to restart the ZBOX when the internet would stop working. The display I had connected to the PFSense machine said this during a failure:
re1: watchdog timeout
Each time the error occurred, I would hunt around the internet for a few minutes to find a solution, but I was never able to find one. I gave up for a while. But recently I took a deeper dive.
I found some pages blaming the default network adapter drivers and how they don’t work well with realtek adapters. I also found this page announcing new realtek drivers. I’ve dabbled with Linux a bit, but I’d still consider myself pretty novice. It was pretty difficult for me to get the drivers copied over to the machine, and then installed.
Here’s the process I followed to get them installed:
Fixing my Zyxel NWA1123-AC
My home network is pretty advanced. I have a PFSense home-built router (based on Zotac ZBOX CI323) that connects to my modem, several smart switches (Netgear JGS524E), and 2 wireless Access Points. The two access points are both made by Zyxel; an NWA1123-AC and an NWA1123-ACv2. I thought it would be prudent to upgrade the firmware of both devices this week but ran into a problem.
Since both need different firmware, I first downloaded the firmware for the v2 device, and then did the v1 device. Somehow I managed to download the NWA1121-NI firmware for the v1 device, and then applied it. As I was doing some configuration on the device, I saw I was missing the 5 Ghz settings, which lead me to figuring out I had flashed it with the wrong firmware. I thought it would be easy to revert back since I was able to go one way, but that proved wrong! The device wouldn’t accept the correct firmware.
I did some hunting around the internet, gave up quick, and submitted a technical support request. Zyxel got back to me the following day saying I would have to RMA the device, sending it back to them, assuming it was in warranty. After some following correspondents, it was determined that I was out of warranty/support. I turned back to the internet.
I found, on Zyxel’s website (PDF), they had directions on how to have the device pull firmware from a TFTP server before the router fully booted. However, there were some critical steps missing.
- There’s no serial port on the AP; WHAT?
- How do I unzip a bin file? And how do I unzip what comes out of that?
- What software do I use to TFTP?
- What software do I use for a terminal?
Zyxel support insisted that this KB article on their site did not apply to my AP problem, and there was no way I could recover the device without sending it in to them.
I did some more hunting and found someone over at the OpenWRT.org site posted the internal parts of the NWA1123-AC AP, as well as some serial port details. I also found a page for serial port specifics saying I needed a USB TTL adapter. The same page had details on the pins to connect the adapter to on the AP (see: Router with serial port / header / pins). Turns out Amazon and my local MicroCenter both carried a USB to TTL adapter, for use with RaspberryPi devices. I biked over to MicroCenter, and picked one up.
Once I got home, opened up my AP, connected everything, downloaded and set up Tera Term and TFTPD64, and starting going through the KB article. I used 7zip to extract the contents of the bin file, and the contents of the subsequent file. I ended up with the files shown in the KB article, so even though 7zip said there was extra contents that wasn’t extracted, I figured I was on the right track.
Everything seemed to work great, and got my device flashed with the correct firmware! Sadly, Zyxel’s poor design required me to clear my browser cache to get the AP’s config page working correctly, but if that was the only hiccup, I wasn’t too angry, and I had been stupid enough to flash the wrong firmware on the AP in the first place.
Access Point saved from certain death!
Amazon Alexa Skill – Electric Price
I was sick of opening up my phone to check the ComEd hourly price of electric supply, so I decided to make an Alexa app for my Amazon Echo!
It took about 2 hours. It was strange I had to set up both an Alexa skill and the AWS Lamda function, then link the two. I ended up copying most of the code from someones reference code on checking stock prices. Changed the regular expression function which found the stock price to a substring of the ComEd API returned text. Works pretty well! Only problem I’ve had is when the ComEd API is down. If it happens too much, I guess I’ll have to update it with some error handling.
Alexa Skill: Electric Price
Ask: “Alexa, ask Electric Price for Current Price.”
The logo is crap, I know. And I couldn’t use the ComEd company name in any of the program details/functions, so its a bit generic. But pretty good for my first try if I do say so myself!
ED HAYES 