5 Replies Latest reply on Feb 14, 2019 5:11 PM by Glen Conway

    Problem with update with function in Sql Developer 18.4

    3623736

      Hi,

      I try to use inline plsql function in update in 18.4 and i found problem.

      When i write ';' at the end of statement i get ORA-00933 sql error. Without ';' update goes well.

       

      In others developers tools toad for example, update works ok with or without ';'

        • 1. Re: Problem with update with function in Sql Developer 18.4
          thatJeffSmith-Oracle

          please share some code that demonstrates the issue you are trying to describe

          • 3. Re: Problem with update with function in Sql Developer 18.4
            3623736

            Yes Glen, my case is similar.

            • 4. Re: Problem with update with function in Sql Developer 18.4
              3623736

              Hi Jeff,

               

              test code  below.

              create table tmp_test( test_ts timestamp );

               

              insert into tmp_test values( to_timestamp('2019-02-14 12:13:14.123', 'yyyy-mm-dd hh24:mi:ss.ff') );

               

              update /*+ with_plsql */ tmp_test

              set test_ts = (with

                                       function to_ts (p_str in varchar2) return timestamp

                                       as

                                            v_ret timestamp := null;

                                       begin

                                            begin

                                                 v_ret := to_timestamp(p_str, 'yyyy-mm-dd hh24:mi:ss.ff');

                                                 exception

                                                      when others then null;

                                            end;

                                            return v_ret;

                                       end;

                                       select to_ts('2019-01-01 23:23:23.123') from dual

                             )

              ;

              • 5. Re: Problem with update with function in Sql Developer 18.4
                Glen Conway

                Looks like a bug to me.  It is interesting how even in SQLcl the final semicolon does not list out from the edit buffer:

                SQL> update /*+ with_plsql */ tmp_test

                  2  set test_ts = (with

                  3                           function to_ts (p_str in varchar2) return timestamp

                  4                           as

                  5                                v_ret timestamp := null;

                  6                           begin

                  7                                begin

                  8                                     v_ret := to_timestamp(p_str, 'yyyy-mm-dd hh24:mi:ss.ff');

                  9                                     exception

                10                                          when others then null;

                11                                end;

                12                                return v_ret;

                13                           end;

                14                           select to_ts('2019-01-01 23:23:23.123') from dual

                15                 )

                16  ;

                17  .

                 

                SQL> l

                  1  update /*+ with_plsql */ tmp_test

                  2  set test_ts = (with

                  3                           function to_ts (p_str in varchar2) return timestamp

                  4                           as

                  5                                v_ret timestamp := null;

                  6                           begin

                  7                                begin

                  8                                     v_ret := to_timestamp(p_str, 'yyyy-mm-dd hh24:mi:ss.ff');

                  9                                     exception

                10                                          when others then null;

                11                                end;

                12                                return v_ret;

                13                           end;

                14                           select to_ts('2019-01-01 23:23:23.123') from dual

                15*                )

                SQL> /

                 

                Error starting at line : 1 in command -

                update /*+ with_plsql */ tmp_test

                set test_ts = (with

                                         function to_ts (p_str in varchar2) return timestamp

                                         as

                                              v_ret timestamp := null;

                                         begin

                                              begin

                                                   v_ret := to_timestamp(p_str, 'yyyy-mm-dd hh24:mi:ss.ff');

                                                   exception

                                                        when others then null;

                                              end;

                                              return v_ret;

                                         end;

                                         select to_ts('2019-01-01 23:23:23.123') from dual

                               )

                ;

                Error at Command Line : 16 Column : 1

                Error report -

                SQL Error: ORA-00933: SQL command not properly ended

                00933. 00000 -  "SQL command not properly ended"

                *Cause:

                *Action:

                SQL>

                whereas in sqlplus all is well:

                SQL> update /*+ with_plsql */ tmp_test

                  2  set test_ts = (with

                  3                           function to_ts (p_str in varchar2) return timestamp

                  4                           as

                  5                                v_ret timestamp := null;

                  6                           begin

                  7                                begin

                  8                                     v_ret := to_timestamp(p_str, 'yyyy-mm-dd hh24:mi:ss.ff');

                  9                                     exception

                10                                          when others then null;

                11                                end;

                12                                return v_ret;

                13                           end;

                14                           select to_ts('2019-01-01 23:23:23.123') from dual

                15                 )

                16  ;

                17  .

                SQL> l

                  1  update /*+ with_plsql */ tmp_test

                  2  set test_ts = (with

                  3                           function to_ts (p_str in varchar2) return timestamp

                  4                           as

                  5                                v_ret timestamp := null;

                  6                           begin

                  7                                begin

                  8                                     v_ret := to_timestamp(p_str, 'yyyy-mm-dd hh24:mi:ss.ff');

                  9                                     exception

                10                                          when others then null;

                11                                end;

                12                                return v_ret;

                13                           end;

                14                           select to_ts('2019-01-01 23:23:23.123') from dual

                15                 )

                16* ;

                SQL> /

                 

                1 row updated.

                 

                SQL>