13 Replies Latest reply on May 30, 2018 3:05 PM by rp0428

    Package body not visible in sqldeveloper 18.1

    JeanYves Bernier

      Hi all,

      I've just created a function in sqldeveloper  Version  18.1.0.095 wich is part of a package  :

       

          FUNCTION get_apms_rpm (
              p_armo_id   IN NUMBER,
              p_rpm_h_l   IN VARCHAR2
          ) RETURN NUMBER AS
              v_rpm1   NUMBER;
              v_rpm2   NUMBER;
          BEGIN
              
              BEGIN
                  WITH apms_xml AS (
                      SELECT
                          amps_armo_id,
                          amps_polarity_xml
                        FROM
                          (
                              SELECT
                                  amps_armo_id,
                                  amps_polarity,
                                  amps_rpm
                                FROM
                                  arem_motor_polarity_spec
                               WHERE
                                  amps_armo_id   = p_armo_id
                          )
                              PIVOT XML ( MAX ( amps_rpm )
                                  FOR ( amps_polarity )
                                  IN ( ANY )
                              )
                  ) SELECT
                      extractvalue(amps_polarity_xml,'/PivotSet/item[1]/column[2]') ,
                      extractvalue(amps_polarity_xml,'/PivotSet/item[2]/column[2]')
                    INTO
                      v_rpm1,
                      v_rpm2
                      FROM
                      apms_xml;
              END;
              IF
                  v_rpm2 IS NULL
              THEN
                  v_rpm1   := NULL;
              ELSE
                  CASE
                      p_rpm_h_l
                      WHEN 'H' THEN
                          RETURN v_rpm1;
                      WHEN 'L' THEN
                          RETURN v_rpm2;
                      ELSE
                          RETURN NULL;
                  END CASE;
              END IF;
          END get_apms_rpm;
      

       

      When I compile an try to debug it  I get this error :

      and also I can not see the package body in the explorer

      Note that when  i do :

      select PKAREM_FANSLCTR.GET_APMS_RPM(66,'H') ,
             PKAREM_FANSLCTR.GET_APMS_RPM(66,'L')  
      from dual;
      

      the function works and returns me 985 for H and 491 fo  L

       

       

      When I put some part of the code (the with amps_xml part)  in comment it works again :

          FUNCTION get_apms_rpm (
              p_armo_id   IN NUMBER,
              p_rpm_h_l   IN VARCHAR2
          ) RETURN NUMBER AS
              v_rpm1   NUMBER;
              v_rpm2   NUMBER;
          BEGIN
              
              /*BEGIN
                  WITH apms_xml AS (
                      SELECT
                          amps_armo_id,
                          amps_polarity_xml
                        FROM
                          (
                              SELECT
                                  amps_armo_id,
                                  amps_polarity,
                                  amps_rpm
                                FROM
                                  arem_motor_polarity_spec
                               WHERE
                                  amps_armo_id   = p_armo_id
                          )
                              PIVOT XML ( MAX ( amps_rpm )
                                  FOR ( amps_polarity )
                                  IN ( ANY )
                              )
                  ) SELECT
                      extractvalue(amps_polarity_xml,'/PivotSet/item[1]/column[2]') ,
                      extractvalue(amps_polarity_xml,'/PivotSet/item[2]/column[2]')
                    INTO
                      v_rpm1,
                      v_rpm2
                      FROM
                      apms_xml;
              END;*/
              IF
                  v_rpm2 IS NULL
              THEN
                  v_rpm1   := NULL;
              ELSE
                  CASE
                      p_rpm_h_l
                      WHEN 'H' THEN
                          RETURN v_rpm1;
                      WHEN 'L' THEN
                          RETURN v_rpm2;
                      ELSE
                          RETURN NULL;
                  END CASE;
              END IF;
          END get_apms_rpm;
      

       

      and the body is visible again

       

      What is wrong with my function or is it an sqldeveloper bug ?

      Is there a workaround ?

       

      Regards

      Jean-Yves

        • 1. Re: Package body not visible in sqldeveloper 18.1
          thatJeffSmith-Oracle

          Sounds like a parser bug - what happens if you publish same function as a standalone pl/sql object?

          1 person found this helpful
          • 2. Re: Package body not visible in sqldeveloper 18.1
            JeanYves Bernier

            Hi Jeff,

            when I created the same function  as standalone pl/sql object it works fine

            Jean-Yves

            • 3. Re: Package body not visible in sqldeveloper 18.1

              When I compile an try to debug it  I get this error :

              Ok - so tell us what that error says - this is an English forum.

               

              1. there is no need for the 'begin ... end' around the WITH clause so why do you  have it? Remove the extra markers and post the test results.

               

              2. that function is NOT listed in the package spec you posted so is private and can NOT be accessed outside the package - so what other code is calling it? And you can't debug it directly since it is doesn't have a public interface that allows you to call it. - so how are you trying to 'debug' it?

              • 4. Re: Package body not visible in sqldeveloper 18.1
                JeanYves Bernier

                So I put my sqldeveloper in English

                1- Removed the extra begin end

                results are the same

                when trying to run

                 

                2 - function get_amps_rpm is of course in the package spec

                FUNCTION get_amps_rpm (
                    p_armo_id   IN NUMBER,
                    p_rpm_h_l   IN VARCHAR2
                ) RETURN NUMBER;
                END PKAREM_FANSLCTR;
                

                 

                 

                so how are you trying to 'debug' it : simply via sqldeveloper 

                • 5. Re: Package body not visible in sqldeveloper 18.1
                  Mike Kutz

                  The extractValue() function is deprecated.

                  https://docs.oracle.com/cloud/latest/db112/SQLRF/functions061.htm#SQLRF06173

                   

                  I'm wondering if the OP is on 12.2+ and the new "pragma deprecate" flag (which should be on extractValue() function) is being pushed upwards.  (ie GET_APMS_RPM() is also flagged as "deprecated").

                  https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/DEPRECATE-pragma.html#GUID-D2DAD89D-C537-4666-A8A7…

                   

                  JeanYves Bernier - rework your code to use XMLTable() instead of extractValue() and see if that helps.

                   

                  My $0.02

                   

                  MK

                  1 person found this helpful
                  • 6. Re: Package body not visible in sqldeveloper 18.1
                    Vadim Tropashko-Oracle

                    Reproduced, bugged as 28094727, fixed in 18.2.

                    1 person found this helpful
                    • 7. Re: Package body not visible in sqldeveloper 18.1
                      JeanYves Bernier

                      Hi Mike,

                      testing by replacing extract value with xmltable

                      WITH apms_xml AS (
                                      SELECT
                                          amps_armo_id,
                                          amps_polarity_xml
                                        FROM
                                          (
                                              SELECT
                                                  amps_armo_id,
                                                  amps_polarity,
                                                  amps_rpm
                                                FROM
                                                  arem_motor_polarity_spec
                                               WHERE
                                                  amps_armo_id   = p_armo_id
                                          )
                                              PIVOT XML ( MAX ( amps_rpm )
                                                  FOR ( amps_polarity )
                                                  IN ( ANY )
                                              )
                                  ) SELECT 
                                           rpm."rpm1",
                                           rpm."rpm2"
                                           INTO
                                           v_rpm1,
                                           v_rpm2
                                  FROM
                                      apms_xml,
                                      xmltable('/PivotSet'
                                        passing amps_polarity_xml
                                        columns "rpm1" number path '/PivotSet/item[1]/column[2]',
                                                "rpm2" number path '/PivotSet/item[2]/column[2]')
                                       rpm;
                      

                       

                      results give the same data as expected but the when debugging i get still "Empty package PKAREM_FANSLCTR definition (no public members)." and the package body is not visible in the explorer

                       

                      Regards

                      Jean-Yves

                      • 8. Re: Package body not visible in sqldeveloper 18.1
                        Vadim Tropashko-Oracle

                        Jean, this bug is fixed for 18.2. In the meantime, as workaround, please run the package (not package body).

                        1 person found this helpful
                        • 9. Re: Package body not visible in sqldeveloper 18.1
                          JeanYves Bernier

                          Thanks Vadim,

                          I will do so tomorrow morning and keep you informed for the results.

                          As mentionned by  Mike Kutz  I will stay with the xmltable option instead of extractvalue

                           

                          Jean-Yves

                          • 10. Re: Package body not visible in sqldeveloper 18.1

                            2 - function get_amps_rpm is of course in the package spec

                            1. FUNCTIONget_amps_rpm(
                            2. p_armo_idINNUMBER,
                            3. p_rpm_h_lINVARCHAR2
                            4. )RETURNNUMBER;
                            5. ENDPKAREM_FANSLCTR;

                             

                             

                            Sorry - but we can only go by what you POSTED - and this is what you posted

                            and also I can not see the package body in the explorer

                            That 'get_amps_rpm' function is NOT listed in that package spec and that is EXACTLY what the exception is telling you. There is no PUBLIC member with the name you are trying to use.

                             

                            Maybe you are looking at a different system - or you posted the wrong thing.

                            • 11. Re: Package body not visible in sqldeveloper 18.1
                              Vadim Tropashko-Oracle

                              ...or  this member has been cropped from the image

                               

                              Here is test case:

                               

                               

                              create or replace PACKAGE BUGFORUM AS

                                FUNCTION get_apms_rpm ( 
                                      p_armo_id   IN NUMBER, 
                                      p_rpm_h_l   IN VARCHAR2 
                                  ) RETURN NUMBER ;

                              END BUGFORUM;

                               

                              create or replace PACKAGE BODY bugforum AS

                                  FUNCTION get_apms_rpm (
                                      p_armo_id   IN NUMBER
                                     ,p_rpm_h_l   IN VARCHAR2
                                  ) RETURN NUMBER AS
                                      v_rpm2   NUMBER;
                                  BEGIN
                                      SELECT
                                          1
                                      INTO v_rpm2
                                      FROM
                                          (
                                              SELECT
                                                  1 amps_armo_id
                                                 ,2 amps_polarity
                                                 ,3 amps_rpm
                                              FROM
                                                  dual
                                          )
                                              PIVOT XML ( MAX ( amps_rpm )
                                                  FOR ( amps_polarity )
                                                  IN ( ANY )
                                              );

                                  END get_apms_rpm;

                              END bugforum;

                              • 12. Re: Package body not visible in sqldeveloper 18.1
                                JeanYves Bernier

                                rp0428 a écrit:

                                 

                                2 - function get_amps_rpm is of course in the package spec

                                1. FUNCTIONget_amps_rpm(
                                2. p_armo_idINNUMBER,
                                3. p_rpm_h_lINVARCHAR2
                                4. )RETURNNUMBER;
                                5. ENDPKAREM_FANSLCTR;

                                 

                                 

                                Sorry - but we can only go by what you POSTED - and this is what you posted

                                and also I can not see the package body in the explorer

                                 

                                That 'get_amps_rpm' function is NOT listed in that package spec and that is EXACTLY what the exception is telling you. There is no PUBLIC member with the name you are trying to use.

                                 

                                Maybe you are looking at a different system - or you posted the wrong thing.

                                As Vadim says : ..or  this member has been cropped from the image

                                 

                                The screenshot above was only to show that the package body is not visible, If the  'get_amps_rpm' function was not listed in the package spec  the select mentioned above will not give a result, it will return ORA-00904: "PKAREM_FANSLCTR"."GET_AMPS_RPM": invalid identifier

                                Note that when  i do :

                                 

                                 

                                1. select PKAREM_FANSLCTR.GET_APMS_RPM(66,'H') , 
                                2.        PKAREM_FANSLCTR.GET_APMS_RPM(66,'L')   
                                3. from dual; 

                                the function works and returns me 985 for H and 491 fo  L

                                 

                                As mentioned by Vadim this is a bug and is fixed for 18.2. In the meantime, as workaround, please run the package (not package body)

                                Regards

                                Jean-Yves

                                • 13. Re: Package body not visible in sqldeveloper 18.1

                                  Vadim Tropashko-Oracle wrote:

                                   

                                  ...or this member has been cropped from the image

                                   

                                  Here is test case:

                                  That test case works just fine for me

                                  Version 18.1.0.095

                                  Build 095.1630

                                  If you found and fixed a bug then good - but I can't reproduce the problem using the code you posted and the version above.