3 Replies Latest reply on May 28, 2015 10:15 AM by Nancy Wang -Oracle

    SqlDeveloper 4.1EA2 has wrong sync package&body suggestion

    user10588126

      Hi, All!

      Another good shot

      SqlDeveloper 4.1EA2 has wrong sync package&body suggestion.

      In package spec I have:

       

        PROCEDURE pi_service_provider(ret_code         OUT INTEGER,

                                      ret_msg          OUT VARCHAR2,

                                      m_dep_id         OUT departments.dep_id%TYPE,

                                      m_sp_rec         t_sp_rec,

                                      m_sp_address_rec t_address_rec,

                                      m_sp_phone1      firm_contacts.fc_data%TYPE,

                                      m_sp_phone2      firm_contacts.fc_data%TYPE,

                                      m_sp_phone3      firm_contacts.fc_data%TYPE,

                                      m_sp_phone4      firm_contacts.fc_data%TYPE,

                                      m_sp_email       firm_contacts.fc_data%TYPE,

                                      m_sp_www         firm_contacts.fc_data%TYPE,

                                      m_commit         CHAR := 'Y');

       

      In body I have:

        PROCEDURE pi_service_provider(ret_code         OUT INTEGER,

                                      ret_msg          OUT VARCHAR2,

                                      m_dep_id         OUT departments.dep_id%TYPE,

                                      m_sp_rec         t_sp_rec,

                                      m_sp_address_rec t_address_rec,

                                      m_sp_phone1      firm_contacts.fc_data%TYPE,

                                      m_sp_phone2      firm_contacts.fc_data%TYPE,

                                      m_sp_phone3      firm_contacts.fc_data%TYPE,

                                      m_sp_phone4      firm_contacts.fc_data%TYPE,

                                      m_sp_email       firm_contacts.fc_data%TYPE,

                                      m_sp_www         firm_contacts.fc_data%TYPE,

                                      m_commit         CHAR := 'Y') AS

          TYPE t_all_languages IS TABLE OF languages%ROWTYPE;

          TYPE t_fc_tab IS TABLE OF firm_contacts%ROWTYPE;

       

          var_all_languages t_all_languages;

          var_ret_code      PLS_INTEGER;

          var_ret_msg       VARCHAR2(2000);

          var_fir_id        firms.fir_id%TYPE;

          var_fn_id         firm_names.fn_id%TYPE;

          var_fc_id         firm_citizenships.fc_id%TYPE;

          var_location_id   locations.location_id%TYPE;

          var_aof_id        address_of_firms.aof_id%TYPE;

          var_ba_id         banking_accounts.ba_id%TYPE;

          var_ban_id        banking_accounts.ban_id%TYPE;

          var_fvh_id        firms_vat_hist.fvh_id%TYPE;

          var_dep_id        departments.dep_id%TYPE;

          var_dn_id         department_names.name%TYPE;

          var_dep_type_id   dep_type.dep_type_id%TYPE;

          var_dr_id         dep_recvs.dr_id%TYPE;

          var_da_id         dept_s_addresses.da_id%TYPE;

          c_work_phone_naid CONSTANT PLS_INTEGER := 3; /*Тип контакта - рабочий телефон*/

          c_email_naid      CONSTANT PLS_INTEGER := 6; /*Тип контакта - email*/

          c_www_naid        CONSTANT PLS_INTEGER := 7; /*Тип контакта - www*/

          var_fc_tab t_fc_tab;

          var_fc1_id firm_contacts.fc_id%TYPE;

       

          PROCEDURE refresh_def_pa_res_types(mm_dep_id IN departments.dep_id%TYPE,

                                             mm_sp_rec IN t_sp_rec) AS

          BEGIN

            DELETE FROM default_values t

             WHERE t.dep_id = mm_dep_id

               AND t.field_name IN ('DEF_PA_TYPE', 'DEF_RES_TYPE');

            INSERT INTO default_values

              (field_name, dep_id, field_type, field_value, attribut_description)

              SELECT 'DEF_PA_TYPE',

                     mm_dep_id,

                     'N',

                     to_char(mm_sp_rec.pa_type_id) || ' V_PA_TYPE PA_TYPE_ID',

                     'Тип лицевого счёта'

                FROM dual

              UNION ALL

              SELECT 'DEF_RES_TYPE',

                     mm_dep_id,

                     'N',

                     '' || rt.restype_id,

                     'Тип измеряемого ресурса для счётчика'

                FROM resourse_type rt, pa_type pt

               WHERE rt.pa_type_type = pt.pa_type_type

                 AND pt.pa_type_id = mm_sp_rec.pa_type_id

                 AND rownum < 2;

          END refresh_def_pa_res_types;

       

        BEGIN

          ret_code := 0;

          ret_msg  := NULL;

      ..........................

        EXCEPTION

          WHEN OTHERS THEN

            ret_code := -21000;

            ret_msg  := substr('<PKG_DEPARTMENT.PI_SERVICE_PROVIDER> ' ||

                               rtrim(fs_err_mag(ret_code)) || ':' || SQLERRM,

                               1,

                               2000);

        END pi_service_provider;

       

      Suggestion on sync from SqlDeveloper:

       

      sync1.png

      sync2.png

      Sql_developer is confused because of the built-in procedure parameters