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_VERSION12.1.0.2.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

Related Links

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
Dynamic page
Translate Inline Validation Errors
custom Authentication Schemes in apexea.oracle.com

Categories

ORACLE DEV
Oracle Provider for OL...
Downloads Issues
Projects
EBS on Linux
Integration - Oracle BAM
Database (Korean)
Install (Korean)
Proc (Korean)
Installation>MS Window...
Retail
Data Sourcing
WebLogic Server - JDBC
Communications Service...
weblogic.developer.int...
weblogic.developer.int...
weblogic.developer.int...
weblogic.developer.int...
weblogic.developer.int...
weblogic.developer.int...
BEA SALT
I(18)N & Localization
WebLogic Communication...
Ensemble
Primavera Contractor U...
Smart View
BEA Workshop (Chinese)
小憩一会
行业专区
dev2dev Bugs Report ((...
Sun Java Studio Creator
Algorithms
Java Plug-In
Signed Applets
Systems Development an...
DTrace
Event Handling
Debugging Tools and Te...
Sun Web Developer Pack...
JDeveloper&ADF (Chinese)
Grid Engine
E-mail, Calendar, & Co...
Java System Mobile Ent...
SQLとPL/SQL
R Technologies
WebCenter Sites
Software as a Service ...
Siebel (Chinese)
Java 8 Questions
Topliners
Oracle Marketing Cloud...
Benelux Eloqua User Group
Public Broadcasting
Markies: Best Internat...
Search Engine Marketing
CBI Topliners Group
Oracle Marketing Cloud...
JDBC & UCP
OpenWorld & JavaOne La...
Java Community Process
Responsys UK User Foru...
Coming Soon - Oracle A...
SQLcl
Korea Engineered Syste...
Java.net Forge Sunset
Cloud Accelerate
Tell Us Your Story!
Bare Metal Cloud Servi...
Trade Promotions
APEX Gruppe
SalesCloudExtensions
Brazil - Eloqua User G...
Database Application D...
Database Security - Ge...
Berkeley DB
数据库
JD Edwards World Gener...
WebLogic Server - General
weblogic.community.gen...
weblogic.aqualogic.dat...
bea.guardian
Communications Service...
Primavera Archived For...
Primavera Risk Analysis
dev2dev - BEA China
Servers General Discus...
Solaris Archived Forums
SQL and PL/SQL (Portug...
Imagine How

Resources

Encrypt Message



code
soft
python
ios
c
html
jquery
cloud
mobile