The "ORA-01461: can bind a LONG value only for insert into a LONG column" is pretty specific. One thing you need to keep in mind when testing SQL outside of Forms is that the PL/SQL Engine in Forms is not the same as in the one in the database. The engine in forms is not as current as the database even if you are using the same release of both softwares (eg: Forms 11g and RDBMS 11g). Consequently, it is entirely possible for a SQL statement to work in the database and fail in Forms.
I suggest you look at your at your Data Source and the table you're inserting into and confirm that you are not dealing with data types that could qualify as LONG. I would specifically look at your data source to ensure you are mapping from compatible data types. Oracle will try to implicitly cast data types to the correct data type, but it is not always successful so you may need to explicitly cast (eg; TO_CHAR or TO_NUMBER, etc) one or more of the columns in your data source.
Thank you for the response. so what you telling me is, even if the source table and destination table are exactly the same, that because of the way forms handles the actual data, it may be trying to convert a text field set at varchar2(2000) to a long value? so, within the form should i be specific with to_char? because during my declaration i call the fields and format them with a %TYPE at the end.
No, I simply am pointing out the fact that the PL/SQL Engine in Forms does not match that of the database and this is something you much keep in mind when testing a SQL statement outside of Forms. Without seeing your table definition and your insert statement, I can't really formulate an idea of what is causing the issue for you. It could be something as simple as having an incompatible Item Type specified in Forms (eg; Table column is VARCHAR2(4000) and Forms item is CHAR(2000)) but this is just speculation. I really need to see how your table is defined and the INSERT statement you're using.
thank you for taking the time here! here are my two tables and the code that i'm utilizing.
sl_3_idn varchar2(11); --this column is added from user input.
field types in Forms 10g:
sl_3_preface_date_input; text item, char 50 byte
sl_3_bullet_nbr; text item, number, max length = 4
sl_3_sub_cat_nbr; text item, number, max length = 4
sl_3_bullet; text item, char 2000 byte -- this is the field that is causing the issue with the insert statement.
text_item9; text item, char 11 byte -- this column feeds sl_3_idn of varchar2(11) in the database. user input.
FORM displays 10 records, but they can scroll over the 22 records that are in the table.
code to follow...
commit_form; --commit the form first so that any changes will be applied to the database, prior to being copied over to destination table.
cursor c_bullet is
select SL_3_BULLET_NBR, SL_3_PREFACE_BULLET, SL_3_PREFACE_DATE_INPUT, SL_3_SUB_CAT_NBR
v_sl3_idn := 'SL-3-11653A' -- for testing purposes, will be filled from a global variable that the user inputs.
fetch c_bullet into v_bullet_nbr, v_bullet, v_date_input, v_sub_cat;
exit when c_bullet%notfound;
if (v_bullet_nbr <= 30) then
insert into PARKER.TEMP_PREFACE_BULLET (sl_3_preface_bullet, sl_3_preface_date_input, sl_3_bullet_nbr, sl_3_sub_cat_nbr)
values (v_bullet, v_date_input, v_bullet_nbr, v_sub_cat);
What is your Forms version (eg; 10.1.2.0.2 not 10g R2)? This could just be a bug in the Forms Builder that is fixed by a patch, but I need to know your Forms version in order to find out.
There is a bug in Forms 10.1.2.0.2 related to this issue. To fix this, you will need upgrade to Forms version 10.1.2.3.x using patch 5983622. You will need a My Oracle Support (MOS) account to get the patch.
thank you very much.