6 Replies Latest reply on Sep 12, 2017 2:47 PM by rp0428

    Type conversion error in "Execute PL/SQL" wizard

    sblandin1973

      Hello,

       

      I often use the Execute PL/SQL wizard provided by SQL Developer to rapidly invoke certain stored procedures.

       

      The wizard usually does a great job converting the expression to the proper data type when assigning value to input parameters.

       

      Today however I've noticed an improper conversion. When I write the vale 2.5 for a varchar2 input parameter, in the PL/SQL block it does not get enclosed in single quotes.

       

      You can see the described bahaviour in the following screenshot

      ExecutePlSqlError.PNG

       

      Can I safely assume that the numeric value 2.5 for PSTEP_CODE will be converted to the string '2.5'?

        • 1. Re: Type conversion error in "Execute PL/SQL" wizard
          Gary Graham-Oracle

          Implicit conversion will take place, but it is never a good idea to rely on it.

           

          For example, taking the sample SCOTT schema where table DEPT has a DEPTNO NUMBER(2, 0) column, if I run the following...

          create or replace PROCEDURE INSERT_DEPT
          (
            P_DEPTNO IN VARCHAR2
          , P_DNAME  IN VARCHAR2
          , P_LOC    IN VARCHAR2
          ) AS
          BEGIN
            INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (P_DEPTNO, P_DNAME, P_LOC);
          END INSERT_DEPT;
          

          with a value of 7.7 for DEPTNO, it rounds it up to 8...

          ImplicitConversionOfNumberToVarchar2.jpg

          Exactly what you would expect, but a little surprising if you are not paying attention.  In your case, with the equivalent of DEPTNO actually being a VARCHAR2, the inserted value will remain as 7.7.

          • 2. Re: Type conversion error in "Execute PL/SQL" wizard

            Implicit conversion will take place, but it is never a good idea to rely on it.

            I read OPs question as about why sql dev put the value of 2 for PPHASE_CODE in quotes for a varchar2 but did NOT add quotes for the value of 2.5 for PSTEP_CODE.

             

            Both parameters are defined as VARCHAR2 so why is Sql dev treating them differently?

            • 3. Re: Type conversion error in "Execute PL/SQL" wizard
              Gary Graham-Oracle

              Both parameters are defined as VARCHAR2 so why is Sql dev treating them differently?

              In the OP's screen shot, we see two different spots where 2 and 2.5 are referenced.

              1. Parametri:  where those variables are shown as being VARCHAR2 but without single quotes

              2.  Blocco PL/SQL:  where the first is quoted, but not second.

               

              What is happening here?  It could just depends on where the OP enters the parameters!  Here is what I see using 17.2...

               

              If the OP types the desired values over the NULL values in the PL/SQL block, the Parameters section is updated to reflect changes to the PL/SQL block, but any enclosing single quotes for VARCHAR2 are ignored.  If the OP types the values in the Parameters Input Value column, the PL/SQL Block is updated accordingly, but any typed single quotes are duplicated in Input Value and appear as 3 consecutive single quotes in the PL/SQL block.  In addition, if Run PL/SQL is repeated, any values entered the previous time (in the same session) are remembered and automatically displayed.

               

              So it looks like [If] the OP entered[s] values in the PL/SQL block inconsistently, PHASE_CODE with quotes and STEP_CODE without, then SQL Developer updated[s] the Parameters section consistently stripping off the quotes.  But that is not what the OP says happened.  Instead...

              When I write the vale 2.5 for a varchar2 input parameter, in the PL/SQL block it does not get enclosed in single quotes.

              so this seems to be a case of SQL Developer encountering a decimal fraction and deciding not to quote the value placed into the PL/SQL block.   This is indeed a bug, with the parser probably better off acting dumb and just treat it as a string.  By entering something like 4.5.2 instead of 2.5, we get this error dialog...

              ImplicitConversionErrorCondition.jpg

              • 4. Re: Type conversion error in "Execute PL/SQL" wizard

                Interesting! Good detective work.

                • 5. Re: Type conversion error in "Execute PL/SQL" wizard
                  sblandin1973

                  Thanks for the bug analisys.

                   

                  It was exactly the issue I was experiencing,

                   

                  Instead of relying on implicit conversion, I should remember to edit the PL/SQL block and not the parameter list.

                   

                  A plus for the 4.5.2 test case. In another project we have to manage textual data in the form for example '0098230.d032'. The wizard fails to parse this string too.

                  • 6. Re: Type conversion error in "Execute PL/SQL" wizard

                    A plus for the 4.5.2 test case. In another project we have to manage textual data in the form for example '0098230.d032'. The wizard fails to parse this string too.

                    The wizard is just a shortcut way to produce the anonymous block WHEN IT DOESN'T EXIST.

                     

                    If the block exists just edit it - no need or point to editing the settings in the wizard when it is the block that gets executed.