9 Replies Latest reply on Jun 18, 2013 3:41 PM by 1005774

    receiving ora-01461 when trying to insert

    1005774

      Hello,

       

             I have a form with multiple rows of data, i wrote a loop statement that inserts data into another table.  When i test the statement from SQL prompt, everything works with no problems.  however, when i added to the save button, i am receiving the Ora-01461 error.  why is that?  the description from what i find doesn't seem to fit with what i'm doing.  any thoughts?  I'm using Oracle Forms 10g and writing to an 11g database.

       

      thank you,

       

      steven


        • 1. Re: receiving ora-01461 when trying to insert
          CraigB

          Steven,

          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.

           

          Craig...

          • 2. Re: receiving ora-01461 when trying to insert
            1005774

            Hi Craig,

             

                    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.

             

            thank you,

             

            steven


            • 3. Re: receiving ora-01461 when trying to insert
              CraigB

              Steven,

              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.


              Craig...

              • 4. Re: receiving ora-01461 when trying to insert
                1005774

                Hi Craig,

                 

                     thank you for taking the time here!  here are my two tables and the code that i'm utilizing.

                 

                source table:

                sl_3_preface_bullet  varchar2(2000);

                sl_3_preface_date_input varchar2(50);

                sl_3_bullet_nbr  number;

                sl_3_sub_cat_nbr number;

                 

                destination table:

                sl_3_idn  varchar2(11);  --this column is added from user input.

                sl_3_preface_bullet varchar2(2000);

                sl_3_preface_date_input varchar2(50);

                sl_3_bullet_nbr  number;

                sl_3_sub_cat_nbr number;

                 

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

                 


                • 5. Re: receiving ora-01461 when trying to insert
                  1005774

                  commit_form;  --commit the form first so that any changes will be applied to the database, prior to being copied over to destination table.

                   

                  declare

                       cursor c_bullet is

                            select SL_3_BULLET_NBR, SL_3_PREFACE_BULLET, SL_3_PREFACE_DATE_INPUT, SL_3_SUB_CAT_NBR

                            from PARKER.SL_3_PREFACE_DEFAULTS;

                   

                  v_bullet_nbr      PARKER.TEMP_PREFACE_BULLET.SL_3_BULLET_NBR%TYPE;

                  v_bullet              PARKER.TEMP_PREFACE_BULLET.SL_3_BULLET%TYPE;

                  v_date_input     PARKER.TEMP_PREFACE_BULLET.SL_3_DATE_INPUT%TYPE;

                  v_sub_cat          PARKER.TEMP_PREFACE_BULLET.SL_3_SUB_CAT_NBR%TYPE;

                  v_sl3_idn           PARKER.TEMP_PREFACE_BULLET.SL_3_IDN%TYPE;

                   

                  error_msg varchar2(300);

                   

                  begin

                       v_sl3_idn := 'SL-3-11653A'  -- for testing purposes, will be filled from a global variable that the user inputs.

                  open c_bullet;

                  loop

                  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);

                  end if;

                  commit;

                  end loop;

                  close c_bullet;

                  end;

                  • 6. Re: receiving ora-01461 when trying to insert
                    CraigB

                    Steven,

                    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.

                     

                    Craig...

                    • 7. Re: receiving ora-01461 when trying to insert
                      1005774

                      version 10.1.2.0.2

                      • 8. Re: receiving ora-01461 when trying to insert
                        CraigB

                        Steven,

                        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.

                         

                        Craig...

                        • 9. Re: receiving ora-01461 when trying to insert
                          1005774

                          Hi Craig,

                           

                                 thank you very much.

                           

                          sincerely,

                           

                          steven