Thanks for the help. It works !
I created two system variables in my compile script:
Now I get an another error "ora-01461 can bind a long value only for insert into a long column" because some of my columns use VARCHAR2(4000 CHAR) which have a limit of 4000 BYTES !
I was not aware of this limit. Here is the explanation of the problem I face: http://stackoverflow.com/questions/5230346/char-semantics-and-ora-01461
I will try, either to fix the length of ITEM to VARCHAR2(2000) In my forms, either to use CLOB with FORMS.
The first solution should avoid the error because I work with European characters which generally require 2 BYTES. So even if the user enters 2000 "é" which is 2-bytes length in UTF8, that should do the job.
The second solution is new for me and I'm not sure FORMS will do the job correctly. I will investigate on the web.
An another solution, could be to use a trigger to check lengthB of new data and raise a custom exception catched/managed inside forms.
But first I will try to force/specify the length semantics = BYTE at FORMS ITEM level for VARCHAR2(4000).
I saw that FORMS, in this case, will automatically limit the value to 4000 BYTES.
I will give feedback of what I do.
I will try, either to fix the length of ITEM to VARCHAR2(2000)
This will only partially fix your problem, there are characters which can have more then 2 bytes.
If the values you'd like to insert don't exceed the 4000 BYTE limit this should actually work. In some tables we have the same situation; however I am not sure if those tables are accessed via standard forms block behaviour.
anyway; to conclude:
- your database column is VARCHAR2(4000 CHAR)?
- you either specified CHAR data length semantics on your forms item or set it to NULL and compiled with NLS_LENGTH_SEMANTICS=CHAR?
if you specify a data length semantics on the item property this item won't be affected by setting the environment variable at compile time.
did you also add
to the default.env?
This shouldn't affect your PL/SQL code or your items as they have the data length semantics fixed at compile time; however I vaguely remember having a problem with NLS_LENGTH_SEMANTICS at runtime with forms and it won't hurt you if you set it.
Yes my database column is varchar2(4000 CHAR) and NLS_LENGHT_SEMANTICS=CHAR in DB.
In the forms "Data length semantics" is specified as NULL, so it takes the NLS_LENGTH_SEMANTICS specified in the environment variable at compile time.
I did a test by forcing/specifing the "Data Length Semantics" to BYTE in my form for a text item with VARCHAR2(4000).
It works perfectly! Forms automatically limits the length in BYTE so it don't exceed 4000 BYTE and avoid the error "ora-01461 can bind a long value only for insert into a long column".
Now I will modify the "Data Length Semantics" for all my forms using JDAPI.
For the NLS_LENGTH_SEMANTICS=CHAR in default.env, I will do some tests to check if it's really needed even if it wont' hurt.
Thanks for your help.
I wouldn't change the data length semantics property of all items, as most certainly it will work if you have columns << varchar2(4000). If it is the same as in PL/SQL where forms assumes the maximum of 4 bytes for each character when you have an output variable of type varchar2 you should be save with items below varchar2(1000).
Just another trap I encountered:
create or replace procedure get_data(ovData out varchar2) is begin ovData := lpad('a', 32767, 'a'); end; /
and in forms
declare outvar varchar2(32767); begin get_data(outvar); end;
(compiled with NLS_LENGTH_SEMANTICS=CHAR which makes your outvar a varchar2(32767 CHAR)) will result in a numeric or value error once the called procedure returns more then 8191 characters (*NOT* bytes!); however this
declare outvar varchar2(32767 BYTE); begin get_data(outvar); end;
will work. The reason is simply because forms doesn't know what data is returned from the procedure and simply assumes the maximum of 4 bytes for each character. I guess the same will apply to forms items.