-
1. Re: Problem with update with function in Sql Developer 18.4
please share some code that demonstrates the issue you are trying to describe
-
2. Re: Problem with update with function in Sql Developer 18.4
Is the test case shown in Semicolon issue with merge statement involving a query with an inline pl/sql function similar to your issue?
-
-
4. Re: Problem with update with function in Sql Developer 18.4
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
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>
Register and Participate in Oracle's online communities. Learn from thousands of experts, get answers to your questions and share knowledge with peers.