ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when compiling a Job

Question

               DECLARE
               X           NUMBER := 960004;
               user_name   VARCHAR2 (30);
            BEGIN
               SELECT USER INTO user_name FROM DUAL;

               EXECUTE IMMEDIATE 'alter session set current_schema = EC_ADMIN';

               BEGIN
                  SYS.DBMS_JOB.iSUBMIT (
                     job         => X,
                     what        => 'DECLARE
               p_error_text                VARCHAR2 (4000);
               p_exec_status               VARCHAR2 (50);
               l_error_source_s   CONSTANT VARCHAR2 (30) := ''JOB# 960004'';
               l_sql_point_n               NUMBER;
               l_err_text_s                VARCHAR2 (4000);


---cursor to fetch the inventory_item_id, organization_id----

               CURSOR c_system_item
               IS
            SELECT inventory_item_id, organization_id
              FROM xx_mtl_system_items_b
            WHERE tc_processed_status = ''N''
            UNION
            SELECT xmic.inventory_item_id, xmic.organization_id
              FROM xx_mtl_item_categories xmic
            WHERE     EXISTS
                          (SELECT 1
                             FROM xx_mtl_system_items_b xmsib
                            WHERE     xmsib.organization_id = xmic.organization_id
                                  AND xmsib.inventory_item_id = xmic.inventory_item_id)
                   AND tc_processed_status = ''N''
            UNION
            SELECT xemsieb.inventory_item_id, 1 AS organization_id
              FROM xx_ego_mtl_sy_items_ext_b xemsieb
            WHERE     EXISTS
                          (SELECT 1
                             FROM xx_mtl_system_items_b xmsib
                            WHERE xmsib.inventory_item_id = xemsieb.inventory_item_id)
                   AND tc_processed_status = ''N''
                   AND attr_group_id = 591;

------------------cursor ends-----------


            BEGIN
               FOR system_item_rec IN c_system_item
               LOOP
                  BEGIN
                     l_sql_point_n := 10;
                     xx_mtl_system_items_b_proc (system_item_rec.inventory_item_id,
                                                 system_item_rec.organization_id,
                                                 p_error_text,
                                                 p_exec_status);
                     l_sql_point_n := 20;

                     IF p_exec_status = ''SUCCESS''
                     THEN
                        l_sql_point_n := 30;

                        UPDATE xx_mtl_system_items_b
                           SET tc_processed_status = ''Y'', tc_processed_date = SYSDATE
                         WHERE inventory_item_id = system_item_rec.inventory_item_id
                               AND organization_id = system_item_rec.organization_id;

                        l_sql_point_n := 40;

                        UPDATE xx_mtl_item_categories
                           SET tc_processed_status = ''Y'', tc_processed_date = SYSDATE
                         WHERE inventory_item_id = system_item_rec.inventory_item_id
                               AND organization_id = system_item_rec.organization_id;

                        l_sql_point_n := 41;

                        UPDATE XX_EGO_MTL_SY_ITEMS_EXT_B
                           SET tc_processed_status = ''Y'', tc_processed_date = SYSDATE
                         WHERE inventory_item_id = system_item_rec.inventory_item_id
                               AND attr_group_id = 591;




                     ELSIF p_exec_status = ''FAILURE''
                     THEN
                        l_sql_point_n := 50;

                        UPDATE xx_mtl_system_items_b
                           SET tc_processed_status = ''E'', tc_processed_date = SYSDATE
                         WHERE inventory_item_id = system_item_rec.inventory_item_id
                               AND organization_id = system_item_rec.organization_id;

                        l_sql_point_n := 60;

                        UPDATE xx_mtl_item_categories
                           SET tc_processed_status = ''E'', tc_processed_date = SYSDATE
                         WHERE inventory_item_id = system_item_rec.inventory_item_id
                               AND organization_id = system_item_rec.organization_id;

                        l_sql_point_n := 61;                   

                        UPDATE XX_EGO_MTL_SY_ITEMS_EXT_B
                           SET tc_processed_status = ''E'', tc_processed_date = SYSDATE
                         WHERE inventory_item_id = system_item_rec.inventory_item_id
                               AND attr_group_id = 591;                   

                     END IF;

                     l_sql_point_n := 70;
                     COMMIT;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        ROLLBACK;
                        l_err_text_s :=
                              ''Error Ocured In : ''
                           || l_error_source_s
                           || '' At SQL Point : ''
                           || l_sql_point_n
                           || '' ERROR : ''
                           || SUBSTR (SQLERRM (SQLCODE), 1, 250);

                        xx_log_dbms_error (''E'',
                                           ''Inventory_Item_Id'',
                                           system_item_rec.inventory_item_id,
                                           NULL,
                                           l_err_text_s,
                                           USER,
                                           l_error_source_s);
                  END;
               END LOOP;
            END;',
                     next_date   => SYSDATE,
                     interval    => 'SYSDATE + 45/1440',
                     no_parse    => FALSE);
                  SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is: ' || TO_CHAR (x));

                  EXECUTE IMMEDIATE 'alter session set current_schema = ' || user_name;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     EXECUTE IMMEDIATE 'alter session set current_schema = ' || user_name;

                     RAISE;
               END;

               COMMIT;
            END;
            /

Gives the Error
[Error] Execution (26: 1): ORA-01461: can bind a LONG value only for insert into a LONG column ORA-06512: at line 142

inventory_item_id, organization_id both are of datatype number in xx_mtl_system_items_b & xx_mtl_item_categories

inventory_item_id is of datatype number in xx_ego_mtl_sy_items_ext_b and organization_id is not present in xx_ego_mtl_sy_items_ext_b

Although oracle corp does own mysql ab, it does not mean that you should tag oracle product related questions as mysql! — Shadow
Possible duplicate of ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying — Chrisrs2292
there is a select query in the cursor, when i run this same code just removing the job code, it works fine.. only inside the job this error is thrown — bushra
Is it necessary to manually allocate job_no to the DBMS_JOB ? Just declare X as X NUMBER; and remove the assignment value and try. — Sameer Mirji

Andrew Wolfe Andrew Wolfe · Accepted Answer · 2018-04-02T03:43:02

You are submitting a 6300-character string to DBMS_JOB.SUBMIT. Depending on the Oracle version, that can be too long. Oracle sometimes sees a long string like that and does an automatic conversion to LONG, which is an abhominable beast of a data type. The usual limit for a varchar2 is 4000. I think that's what killed you.

ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when compiling a Job

1 Answers