6 Replies Latest reply on Aug 12, 2020 12:54 PM by thatJeffSmith-Oracle

    Procedure VARCHAR2 input with "." (dot) in the value

    schmittcw

      I noticed in a procedure I was working on today that if I pass in a value that has a dot in it for a VARCHAR2 input parameter, it does not add quotes around the value in the generated PL/SQL:

      SQL Developer - Default Screen.png

      SQL Developer - Issue Screen.png

       

      This is a problem for my use case, as the input in question is a timestamp string, which in the default SQL developer format has dots between the hour, minute, and second values.

       

      Sure, I can still make it work if I modify the generated PL/SQL in the window before hitting OK to run it, but that's not really an acceptable repeatable case. Or I could modify the SQL Developer NLS settings to not use dots between those values, but I can't ask everyone in my office to change their SQL Developer settings just to accommodate this one stored procedure.

       

      Has anyone else noticed this? Has this been reported as an issue? I am running the most recent version of SQL Developer (20.2.0.175, Build 175.1842), but I also had tried 18.4.0 (what I started working on this on before jumping to the new version hoping for better results).

        • 1. Re: Procedure VARCHAR2 input with "." (dot) in the value
          Glen Conway

          Well, I cannot say for certain there is no bug in SQL Developer with regard to your case, but you really should be very careful not to rely too much on Oracle's implicit datatype conversions.

           

          Here is an Oracle Magazine blog by Tom Kyte explaining why: https://blogs.oracle.com/oraclemagazine/on-implicit-conversions-and-more

           

          Look for "I consider all these implicit conversions to be bugs in the developed code, for the following reasons:"

           

          Cheers

          • 2. Re: Procedure VARCHAR2 input with "." (dot) in the value
            thatJeffSmith-Oracle

            it's worse, the plsql has a date input, but the data type is a varchar

            • 3. Re: Procedure VARCHAR2 input with "." (dot) in the value
              Glen Conway

              It looks like SQL Developer's Run PL/SQL dialog does not offer a Timestamp datatype in the drop-down list, and only supports a Date format sans the hh:mi:ss portion.  So one might say the OP was "forced" to use a VARCHAR datatype.

               

              I tested on 20.2 with my NLS Date Format preference  set to rrrr-mm-dd hh24:mi:ss and was pleased to note that trying to put in a date with the dd-mon-rrrr format produces an error:

              Capture.JPG

              When I test again using the NLS Date Format in my preferences

              then I get a nice message saying that Oracle never gets to the hh:mi:ss portion of the date:

              Capture2.JPG

              Leaving off the hh:mi:ss portion in the test data in the Run PL/SQL dialog, then I do see the NLS Date Format from the preferences:

              Capture4.JPG

              So I think SQL Developer behaves quite reasonably in this case, but perhaps some improvement can be made to better support dates with time components.

               

              Edit:  Anyway, given the hazards of implicit conversion (and not being certain of default NLS settings for each SQL Developer user), it is clearly the OP's job to code the stored procedure to deal with Date/Time content in a VARCHAR.

              • 4. Re: Procedure VARCHAR2 input with "." (dot) in the value
                Glen Conway

                OK, one more comment.

                 

                Now I see more deeply into what the OP is doing / could do.  The lack of Timestamp in the New Procedure dialog may not be a problem since the procedure DDL can be edited directly after creation to specify a Timestamp datatype for the parameter.  Then, however, the Run PL/SQL dialog disables the Input Value field.  Still not an insurmountable problem, as the PL/SQL Block can be edited directly to include the Timestamp value.  But then, of course, one must also enclose the Timestamp value in single-quotes, as the OP complains about.

                 

                One kludge solution that "kind of works" if, like the OP, we use a VARCHAR for the DateTime string is just concatenating single quotes around param3 -> "t_text := '''' || param3 || ''''

                 

                Then we get this:

                Capture5.JPG

                With a result of

                Capture6.JPG

                So, as the poem goes, "...there is no joy in Mudvillemighty Casey has struck out."

                 

                Cheers

                • 5. Re: Procedure VARCHAR2 input with "." (dot) in the value
                  schmittcw

                  To be clear, in the stored procedure itself I'm doing the conversion to a date. The VARCHAR2 part is just for ease of entry when running it. And hence my problem.

                   

                  And this doesn't just affect my use case. Any VARCHAR2 input where the user puts in a dot will cause this result in SQL Developer, and therefore error:

                  SQL Developer - Issue Screen 2.png

                  • 6. Re: Procedure VARCHAR2 input with "." (dot) in the value
                    thatJeffSmith-Oracle

                    I've logged a bug - you should be able to use a '.' in your varchar2 input, AND you should have the ability to init a timestamp input using the grid as well.