Oracle Application Express (APEX)
How do I prevent ORA-01461 when inserting column
Apex 4.2 Oracle 11.2 When users paste text longer than 4000 characters into a text column they get "ORA-01461: can bind a LONG value only for insert into a LONG column" when they try to save the data. Which does not help them much. Putting in a validation (length < 4000) works fine if data already exists for that column in the database, but for inserts it seems to be ignored. Can anyone tell be how I can/should handle the fact that the text has too many characters (or point me to a reference)? Plan B is to convert all varchar(4000)s to CLOB. Seems a bit extreme, especially as we really want the users to limit their input to 4000 characters. The meaningless error message is the actual problem. cheers~t
teedub wrote: Apex 4.2 Oracle 11.2 When users paste text longer than 4000 characters into a text column they get "ORA-01461: can bind a LONG value only for insert into a LONG column" when they try to save the data. Which does not help them much.What item type is being used?Putting in a validation (length < 4000) works fine if data already exists for that column in the database, but for inserts it seems to be ignored.How is this validation implemented?Can anyone tell be how I can/should handle the fact that the text has too many characters (or point me to a reference)? I'm seeing no issues with a standard PL/SQL Expression validation using: length(:p1_text_item) <= 4000 Complexities may arise depending on the language(s) and character sets used in the application and database. What language is used by the application? What language is the data? What are the results of the following query? select * from nls_database_parameters
Thankyou for your interest fac586. I don't think that nls is an issue but I have pasted the details below. I am using text only to test the functionality. The items are basically vanilla textarea. I have pasted one example below along with the validation. The page uses automatic row processing (DML). To make the problem happen I need to1. add new row (create),2. paste in more that 4000 characters,3. then SAVE. The validation works fine if the row already exists, even if you paste in a chunk of text greater than 4000. Thank you again ~t NLS_RDBMS_VERSION22.214.171.124.0NLS_NCHAR_CONV_EXCPFALSENLS_LENGTH_SEMANTICSBYTENLS_COMPBINARYNLS_DUAL_CURRENCY$NLS_TIMESTAMP_TZ_FORMATDD/MON/RR HH12:MI:SSXFF AM TZRNLS_TIME_TZ_FORMATHH12:MI:SSXFF AM TZRNLS_TIMESTAMP_FORMATDD/MON/RR HH12:MI:SSXFF AMNLS_TIME_FORMATHH12:MI:SSXFF AMNLS_SORTBINARYNLS_DATE_LANGUAGEENGLISHNLS_DATE_FORMATDD/MON/RRNLS_CALENDARGREGORIANNLS_NUMERIC_CHARACTERS.,NLS_NCHAR_CHARACTERSETAL16UTF16NLS_CHARACTERSETWE8MSWIN1252
teedub wrote: The validation works fine if the row already exists, even if you paste in a chunk of text greater than 4000. The validation is conditional on the SAVE button being pressed. It's likely that a different button/request is bring used to submit the page when adding a new row. Change the condition so that the validation is executed whenever required.
Thank you fac586. You were spot on. Now works perfectly. Couldn't see the forest for the trees. ~t
How to get other column values of a checked row?
delete all column containt null in my table in oracle
Send images (BLOB) from Android app to APEX RESTful Web Service in Oracle Cloud?
How to select multiple values the columns of Interactive reports ?
Apex 5.0.3 Get an error when try to publish a translated application
TAG Cloud plugin bug how to report ?
Disable a textbox based on regex expression
Error Not Found While Accessing Procedure Through URL
How to make two select list interact ORACLE APEX
Remove/reduce group by or control break column headings in interactive report ?
How to add print button to classic report to send barcode to barcode printer
How to make tabular form item read only but we can set value programatically
Procedure call results in timeout when called from APEX but runs fine from SQL Developer
Translate Inline Validation Errors
custom Authentication Schemes in apexea.oracle.com