5 Replies Latest reply on Sep 28, 2019 4:24 AM by Gaz in Oz

    Same SQL Statement executes successfully in SQL*Plus but not in SQL Developer

    MK_KCC_uk

      Hi folks,

       

      I am facing issue with below delete statement which executes successfully in SQL*Plus but throw below error when I try to execute in SQL Dveloper (version 19.2.1.247.2212) against oracle database 12.1.0.2

      delete from per_all_assignments_f where assignment_id = ***** and effective_start_date = to_date('01-Mar-2019','DD-Mon-YYYY');

      Error starting at line : 1 in command -
      delete from per_all_assignments_f where assignment_id = ***** and effective_start_date = to_date('01-Mar-2019','DD-Mon-YYYY')
      Error report -
      ORA-01403: no data found
      ORA-06512: at "APPS.PERALLASSIGNMENTSF_43D_DYT", line 64
      ORA-04088: error during execution of trigger 'APPS.PERALLASSIGNMENTSF_43D_DYT'

       

      The same delete statement successfully executes in SQL*Plus without any trigger issue.
      Even after disabling the reported trigger, it complains about one another trigger.

       

      Any small help will be highly appreciated.

       

      Thanks

        • 1. Re: Same SQL Statement executes successfully in SQL*Plus but not in SQL Developer
          thatJeffSmith-Oracle

          We'd have to see your triggers to have anything other than a guess

          • 2. Re: Same SQL Statement executes successfully in SQL*Plus but not in SQL Developer
            MK_KCC_uk

            Thanks for your reply.

            Below is the trggers' code, but this is a standard code from oracle as this is a part of EBS 12.1 install:

             

            create or replace TRIGGER PERALLASSIGNMENTSF_43D_DYT AFTER DELETE ON PER_ALL_ASSIGNMENTS_F FOR EACH ROW DECLARE
              /* Local variable declarations */
              l_business_group_id            NUMBER;
              l_legislation_code             VARCHAR2(30);
              l_mode  varchar2(80);

            --
            BEGIN
            /*
              ================================================
              This is a dynamically generated database trigger
              ================================================
                        ** DO NOT CHANGE MANUALLY **         
              ------------------------------------------------
                Table:  PER_ALL_ASSIGNMENTS_F
                Action: Delete
                Date:   14/05/2018 10:59
                Name:   PER_ALL_ASSIGNMENTS_F_ARD
                Info.:  Incident Register trigger on deletion of assignments.
              ================================================
            */
            --
              l_mode := pay_dyn_triggers.g_dyt_mode;
              pay_dyn_triggers.g_dyt_mode := pay_dyn_triggers.g_dbms_dyt;
            IF NOT (hr_general.g_data_migrator_mode <> 'Y') THEN
              RETURN;
            END IF;
              /* Initialising local variables */
              l_business_group_id := :old.business_group_id;
              --
              SELECT legislation_code
              INTO   l_legislation_code
              FROM per_business_groups WHERE business_group_id = l_business_group_id;
              --
              /* Is the trigger in an enabled functional area */
              IF paywsfgt_pkg.trigger_is_not_enabled(
                p_event_id          => 43,
                p_legislation_code  => l_legislation_code,
                p_business_group_id => l_business_group_id,
                p_payroll_id        => NULL
              ) THEN
                RETURN;
              END IF;
              --
              /* Global component calls */
              pay_continuous_calc.per_all_assignments_f_ard(
                p_assignment_id                => :old.assignment_id,
                p_business_group_id            => l_business_group_id,
                p_effective_start_date         => :old.effective_start_date,
                p_legislation_code             => l_legislation_code
              );
              --
              /* Legislation specific component calls */
              --
              /* Business group specific component calls */
              --
              /* Payroll specific component calls */
              --
              pay_dyn_triggers.g_dyt_mode := l_mode;
            EXCEPTION
              WHEN OTHERS THEN
                hr_utility.set_location('PERALLASSIGNMENTSF_43D_DYT',ABS(SQLCODE));
                pay_dyn_triggers.g_dyt_mode := l_mode;
                RAISE;
              --
            END;

            • 3. Re: Same SQL Statement executes successfully in SQL*Plus but not in SQL Developer
              thatJeffSmith-Oracle

              That code calls even more code... somewhere in that stack is your answer.

               

              Something about your SQLDev session is different than your SQLPlus session...we're not doing anything weird other than using JDBC.

              • 4. Re: Same SQL Statement executes successfully in SQL*Plus but not in SQL Developer
                Gaz in Oz

                Check your NLS settings in sqldev and compare them with the settings in sqlplus. Certain Apps objects can be "language" sensitive.

                For example, run the following in sqlplus and sqldev:

                SQL> select sys_context('userenv', 'lang') lang, sys_context('userenv', 'language') language from dual;

                 

                LANG       LANGUAGE

                ---------- -------------------------

                GB         ENGLISH_AMERICA.AL32UTF8

                 

                SQL>

                To set the userenv 'lang' context value to GB in sqldev  goto

                Tools -> Preferences... -> {+}-Database -> NLS

                and change the "Language:" from "AMERICAN" ("US") to "ENGLISH" ("GB").

                Try changing the language in your sqldev to match what is in your sqlplus session.

                 

                Explanation of the lang and language userenv context values:

                https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#SQLRF06117

                • 5. Re: Same SQL Statement executes successfully in SQL*Plus but not in SQL Developer
                  Gaz in Oz

                  ...or perhaps there's a login.sql or glogin.sql in effect for sqlplus that sets your org_id?

                  And you are logging into the same database with the same credentials?...