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.
Generating XML in PL/SQL
At work we have historically been using concatenation for generating XML Data. I stumbled upon these XML functions the other day, and thought it was worth sharing. They seem to be the proper, and I’m sure more efficient, way to generate XML using Oracle PL/SQL. I’m dumbfounded that our developers didn’t use this method, but who am I to judge!
Oracle Documentation:
http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB1600
Easier to Understand Guide:
http://allthingsoracle.com/generating-xml-from-sql-and-pl-sql-part-1/
http://allthingsoracle.com/generating-xml-from-sql-and-plsql-part-2/
Example Query for Delivery Lines
Should produce XML for a Delivery with its lines, where lines contain the Item Number, Description, Shipped Quantity, and Requested Quantity.
SELECT XMLSERIALIZE (
DOCUMENT (SELECT XMLELEMENT (
"EXTRACT",
(SELECT XMLAGG (
XMLELEMENT (
"DELIVERY",
XMLELEMENT ("DELIVERY_ID",
DELIVERY_ID),
XMLELEMENT (
"LIST_LINE",
(SELECT XMLAGG (
XMLELEMENT (
"LINE",
XMLFOREST (
msib.segment1 AS "ITEM_NUMBER",
XMLCDATA ( MSIBTL.DESCRIPTION) AS DESCRIPTION,
NVL ( WDD.SHIPPED_QUANTITY, 0) AS "SHIPPED_QUANTITY",
WDD.REQUESTED_QUANTITY AS "REQUESTED_QUANTITY",
wdd.delivery_detail_id)))
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_system_items_b msib,
mtl_system_items_TL msibTL
WHERE wda.delivery_id = wnd.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.inventory_item_id = msib.inventory_item_id
AND msib.organization_id = WND.ORGANIZATION_ID
AND wdd.inventory_item_id = msibTL.inventory_item_id
AND msibTL.organization_id = WND.ORGANIZATION_ID
AND MSIBTL.LANGUAGE = 'US'))))
AS XML
FROM wsh_new_deliveries wnd
WHERE delivery_id = :delivery_id))
FROM DUAL) AS CLOB
VERSION 1.1 INDENT)
AS xmlserialize_doc
FROM DUAL;
Sending ZPL to a Label Printer Using PL/SQL
Oracle’s WMS package has some label printing capabilities, but the functionality is quite poor, and requires knowledge of XML Label printing.
XML Label printing, generally, is the ability to send an XML file to a label printer, where the XML file contains the label format to use, and the data/variables to print on the label. This allows you to have one label “template” which can be reused for printing similar data. Printing a shipping label for example. The label format never changes, but the data does. The standard solution requires a ZPL Template to be stored on a Zebra printer’s flash memory. That ZPL template can be coded by hand, or by using Zebra Designer software. One drawback to this solution is that templates must be downloaded to every printer which will be used. Centralized label template storage would be ideal.
There are several third party solutions that bolt onto the Oracle functionality and allow for a centralized label template, but they also have their own drawbacks, and can cost several hundred thousand dollars to implement, in addition to the ongoing maintenance and server costs. Even with the third party solutions, the user is still left with quite a few deficiencies with the Oracle WMS feature set.
There is an alternative to the template centralization problem which I think is ideal. Store the templates on the server which will be generating the print requests. Instead of having the Zebra printer or third party add on tool merge the XML with the ZPL template, have the same server that is generating the label print requests merge the template/date.
I’ve come up with a simple PL/SQL script which allows a programmer to send a string of text to a label printer through the network using TCP/IP protocol. It uses the same function that Oracle uses to send the XML to the printer when printing without a third part tool, but instead of sending XML, one can send ZPL. This script can easily be added to a procedure that splices data with a ZPL template.
The strange thing about this function is that it has multiple outputs and cannot be used in a SELECT statement like most functions. I’m not sure why Oracle decided to use a function rather than a procedure, but here’s how you can run it:
DECLARE
l_return_msg VARCHAR2(3000);l_printer_status VARCHAR2(3000);l_return VARCHAR2(3000);l_zpl CLOB;l_printer_ip VARCHAR2(20);l_printer_port VARCHAR2(10);BEGINl_zpl :=’^XA^FO50,300^A0N,125,125^FDTEST^XZ’; –String to send to printerl_printer_ip :=’192.168.1.10′; –IP Address of printerl_printer_port :=’9100′;l_return := INV_PRINT_REQUEST.SEND_XML_TCPIP(p_ip_address => l_printer_ip, p_port => to_char(l_printer_port), p_xml_content => l_zpl, x_return_msg => l_return_msg, x_printer_status => l_printer_status);END;
Oracle WMS Cycle Count Bug
We recently completed a Physical Inventory using Oracle WMS Cycle Count functionality and some custom programming. It was a arguably a success. I’ll post something about that in the future, hopefully, but here is something that is really aggravating me at the moment.
Two of the biggest problems we had appeared to be bugs with seeded forms/functionality. We had two different symptoms, that seemed like seeded (built in/out of the box) functionality was broken, and it took us quite some time to identify the root cause.
- The Approve Cycle Counts form would not show us a portion of the cycle count entries
- The counts were in the database
- The counts were in our report
- The counts would not show up on the form
- Recounts would not be given to users
- Initial counts would be made
- Recounts would be manually queued to users using the Warehouse Control Board
- As soon as the user would go to the telnet Directed Cycled Count Tasks screen, the task would go directly back to pending
- The user would never see the task to work on
I logged two separate SRs with Oracle. Both made little progress for several weeks.
Luckily, one of our developers identified the commonality between the records. There was an EXPORT_FLAG on the MTL_CYCLE_COUNT_ENTRIES table which was set to 1 for the offending records. I updated the two SRs with these details.
Oracle Value Set: Allow Only Alphanumeric Values
We have a problem at work which required us to only allow alphanumeric values in a DFF. No spaces, punctuation, or special characters. Here is what I came up with:
Function (WordPress will change quotes to smart quotes, you will need to change back)
FND PLSQL “declare
l_value varchar2( 150 ) := :!value ;
l_valid NUMBER := NULL ;
BEGIN
SELECT (LENGTH(TRIM(TRANSLATE(REPLACE(l_value,’ ‘,’.’), ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’, ‘ ‘))))
INTO l_valid
FROM dual;
IF (l_valid IS NOT NULL) THEN
fnd_message.set_name( ‘FND’, ‘FND_GENERIC_MESSAGE’ ) ;
fnd_message.set_token( ‘MESSAGE’, ‘Value must be alpha-numeric’ );
fnd_message.raise_error ;
END IF ;
END;
“
Regular Expression Version:
FND PLSQL “declare
l_value varchar2( 150 ) := :!value ;
BEGIN
IF REGEXP_SUBSTR(l_value, ‘^[a-zA-Z0-9]+$’) IS NULL THEN
fnd_message.set_name( ‘FND’, ‘FND_GENERIC_MESSAGE’ ) ;
fnd_message.set_token( ‘MESSAGE’, ‘(‘||l_value||’) must be alphanumeric.’);
fnd_message.raise_error ;
END IF ;
END;
“
Continuing Poor Oracle Support
I communicate with Oracle support regularly. Here are some of the recent response I have gotten from them.
Example 1
This is an example from a severity 1 SR. We are unable to drop a picked LPN/pallet in the warehouse. The SR was originally severity 2, but because we had received no constructive responses for a shipment that wouldn’t ship for 3 days, we escalated it to severity 1.
From Development
———————————–
The LPN ##### is showing in context 5 so looks like the cartonization_id
stamping of this using custom program might have caused this. The LPN that
got used for transfer is in status defined but not used. At this point the
best step right now would be to unload and remove the allocations from the
Transact Move Orders form and backorder the line. A datafix would essentially
do the same and then require the customer to re-release/pick load and drop
the lines.
.
Pl. try the action plan to backout and cancel the allocations from UI and let
us know the results. If issue persists pl. get the details of the error
customer is facing when trying to backout and we can review accordingly.
- This message was sent from Oracle Development to Oracle Support. Oracle Support then copied/pasted the message to pass it on to us.
- LPN Context 5 is used on all LPNs that were used in cartonization. We have been live for 1.5 years and have been doing this without a problem. It seems we (the customer) know about the software the Oracle does
- Why are they abbreviating “please” to “Pl.”? That seems lazy and unprofessional to me.
- They are asking us to backorder and re-release the whole delivery, essentially giving up on the problem.
- By giving up, we must put material back into the warehouse, and repick it. Causing us to use our resources 3x normal usage to pick an order. Luckily this delivery is only one LPN/Pallet.
- If you can’t tell, their internal system puts arbitrary new lines into the text. And to force a new line, they must put a “.” in so that the new line isn’t truncated. How antiquated is there support system? They market themselves as being a technology company.
ED HAYES 