Tuesday, June 11, 2019

Nice bug in Oracle 12.2.0.1 affecting function NUMTODSINTERVAL

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

select numtodsinterval(11505,'SECONDS') from dual;

This simple query killed my sqldeveloper session.
I mistakenly wrote SECONDS instead of SECOND and boom!

I ignore if the same happens with the plurals of the other units.

I wonder what kind of parameter checking is done in this function...

Thursday, May 16, 2019

JSON get_Date() method always discards time portion

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.


In case you wonder why you don't see the time portion when you call the method get_Date() on a JSON object:

-- tested on Oracle 12.2.0.1.0 
set serveroutput on
declare
  l_job_description   JSON_OBJECT_T;
  l_job_params        JSON_ARRAY_T;
  l_job_arr_elem      JSON_OBJECT_T;
begin
  l_job_description := JSON_OBJECT_T.parse(
'{
  "parameters" : [
                  {"name":"p_date", "type":"date", "value":"2019-12-31T12:01:59"},
                  {"name":"p_dat2", "type":"date", "value":"2019-12-31T12:01:59"}
                 ]
 }');
  l_job_params := l_job_description.get_Array('parameters');
  
  if l_job_params is not null then

    if l_job_params.get_Size > 0 then
    
      for i in 0..l_job_params.get_Size - 1
      loop
  
        l_job_arr_elem := JSON_OBJECT_T(l_job_params.get(i));
        case l_job_arr_elem.get_String('name')
        when 'p_date' then
          dbms_output.put_line(to_char(l_job_arr_elem.get_Date('value'),'YYYY-MM-DD HH24:MI:SS'));
        when 'p_dat2' then
          dbms_output.put_line(to_char(l_job_arr_elem.get_Timestamp('value'),'YYYY-MM-DD HH24:MI:SS'));
        end case;
  
      end loop;
    
    end if;

  end if;

end;
/
 
2019-12-31 00:00:00
2019-12-31 12:01:59


Method get_Timestamp() will come to the rescue.

Thursday, February 07, 2019

Tales of mystery and imagination of DELETE RETURNING INTO

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.



Am I the only one who finds very odd this statement in the documentation of
DELETE ... RETURNING INTO?

"returning_clause
This clause lets you return values from deleted columns, and thereby eliminate the need to issue a SELECT statement following the DELETE statement."

I mean, what do you really expect to find AFTER issuing a DELETE?

The editor must have been copying and pasting the text from the INSERT RETURNING INTO...

This mistake is present in all the versions of SQL Reference Manual, 18c included, to this date.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles