• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Category Archives: ORACLE 18c

ORACLE 18c – TO_UTC_TIMESTAMP_TZ

29 Saturday Feb 2020

Posted by troroblog in BUG, ORACLE 18c

≈ Leave a comment

Tags

ORACLE, SQL

TO_UTC_TIMESTAMP_TZ takes a ISO8601 formatted date string and converts it to a TIMESTAMP WITH TIMEZONE datatype.

According to documentation this is the ebnf of to_utc_timestamp_tc

to_utc_timestamp_tz

The varchar parameter may be either a date (only) or a date with time combination. The format of the parameter is:

Date only:

YYYY-MM-DD

Date / time:

YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]]][Z|(+|-)hh:mm]

Example

with data (d) as (select '2020-02-28T21:32:52'       from dual union all
                  select '2020-02-28T21:32:52+02:00' from dual union all
                  select '2020-02-28T19:32:52Z'      from dual union all
                  select '2020-02-28T19:32:52+00:00' from dual union all
                  select '2020-02-28T21:32:52.12679' from dual union all
                  select '2020-02-28')
select to_utc_timestamp_tz(d) as converted
  from data;

CONVERTED 
---------------------------------------------
28.02.2020 21:32:52.000000000 GMT
28.02.2020 21:32:52.000000000 +02:00
28.02.2020 19:32:52.000000000 GMT
28.02.2020 19:32:52.000000000 GMT
28.02.2020 21:32:52.126790000 GMT
28.02.2020 00:00:00.000000000 GMT

6 rows selected.

DEFAULT RETURN VALUE ON CONVERSION ERROR

As most of the other conversion functions documentation says that TO_UTC_TIMESTAMP_TZ supports the on conversion error clause too. But when trying to use it you will get an error.

with data (d) as (select '2018-10-26T21:32:52'       from dual union all
                  select '2018-10-26T21:32:52+02:00' from dual union all
                  select '2018-10-26T19:32:52Z'      from dual union all
                  select '2018-10-26T19:32:52+00:00' from dual union all
                  select '2018-10-26T21:32:52.12679' from dual)
select to_utc_timestamp_tz(d default null on conversion error) converted
  from data;

ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause: 
*Action:
Error at Line: 11 Column: 30

Documentation Bug

I have filed an SR with Oracle Support regarding this error. It has been classified as documentation bug:

DOC 30936692 – TAKE OUT “[ DEFAULT RETURN_VALUE ON CONVERSION ERROR ]” OPTIONAL CLAUSE FROM TO_UTC_TIMESTAMP_TZ

and will be removed from future (>20) documentations.


Conclusion

Somehow I hopped, that Oracle would add the missing functionality concerning the conversion error handling to the TO_UTC_TIMESTAMP_TZ function rather than declaring it as a documentation bug. 

Not having a decent way to handle exceptions (besides building a user defined function with an exception handler) will keep me away from using this function.

Package Initialization – Changes between ORACLE 11g and ORACLE 12c

16 Wednesday May 2018

Posted by troroblog in ORACLE 11g Release 2, ORACLE 18c

≈ Leave a comment

Tags

ORACLE, ORACLE 12c Release 1, ORACLE 12c Release 2, PL/SQL

The package initialization block

When a session references a package item, the package is instantiated and initialized. Initializing means:

  • assigning values to public constants (constants defined in the package specification or body outside of packaged procedures and functions)
  • assigning values to public variables if values are specified in variable declaration
  • execute the initial block of the package body  
CREATE OR REPLACE PACKAGE test_init IS
   PROCEDURE show_values;
END test_init;
/

CREATE OR REPLACE PACKAGE BODY test_init IS
   g_before_error VARCHAR2(30) := 'NOT INITIALIZED';
   g_after_error  VARCHAR2(30) := 'NOT INITIALIZED';
   g_test         VARCHAR2(5);

   PROCEDURE show_values IS
   BEGIN
      sys.dbms_output.put_line('Before : ' || g_before_error);
      sys.dbms_output.put_line('After  : ' || g_after_error);
   END show_values;

-- INIT BLOCK
BEGIN
   g_before_error := 'INITIALIZED';
   g_test         := 'This will raise an error';
   g_after_error  := 'INITIALIZED';
END test_init;
/

What happens if the initialization block raises an error?

Now basically you get an error – as we would expect.

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So far so good – but now, a significant thing has changed between ORACLE 11 and ORACLE 12.


What happens if we call the show_values procedure again?

ORACLE 12 (same in 18)

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So in ORACLE 12 the initialization block is executed again. This indicates, that the package was not instantiated due to the error in the initialization block. 

ORACLE 11

BEGIN
   test_init.show_values();
END;
/

PL/SQL procedure successfully completed.

Before : INITIALIZED
After  : NOT INITIALIZED

In ORACLE 11 the packages has been instantiated even if the initialization run into an error. So a second call will not run the initialization again…

As you can see the second global variable (g_after_error) has not been changed by the initialization block whereas the first was.

 


What does this mean?

In my opinion the current (12c/18c) behaviour is much more consistent…but this change in behaviour can (and will) affect applications that did not care too much on whether the initialization run into an error in Oracle 11g – as a secondary call to a package item that does not depend on a correct initialization of the package worked..

 

 

 

ORACLE 18c – Qualified Expressions

21 Wednesday Feb 2018

Posted by troroblog in ORACLE, ORACLE 18c, PL/SQL

≈ Leave a comment

Tags

New Feature, ORACLE, PL/SQL

Qualified expressions improve readability and maintainability of our PL/SQL code. 

While initialising nested tables was quite easy in the past, initialising associative arrays was quite a pain, if it was not done using a query. 

The following examples show how qualified expressions simplify the coding using an associative array, where the key is the name of a city and the content is the population.

Before 18c

DECLARE
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
   city_population city_population_type;
BEGIN
   <<Init>>
   BEGIN
      city_population('Zürich')   := 400000;
      city_population('Genf')     := 195000;
      city_population('Basel')    := 175000;
      city_population('Bern')     := 140000;
      city_population('Lausanne') := 135000;
   END Init;
 
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

18c using qualified expression

DECLARE   
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);   
   city_population city_population_type;
BEGIN   
   <<Init>>   
   BEGIN       
      city_population := city_population_type('Zürich'   => 400000
                                             ,'Genf'     => 195000
                                             ,'Basel'    => 175000
                                             ,'Bern'     => 140000
                                             ,'Lausanne' => 135000);
   END Init;      
  
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

Qualified expression can also be used when working with record structures (and of course also when record structures are part of a associative array).


Record structures with qualified expressions

DECLARE
   TYPE emp_type IS RECORD(empno    emp.empno%type
                          ,ename    emp.ename%type
                          ,job      emp.job%type
                          ,mgr      emp.mgr%type 
                          ,hiredate emp.hiredate%type
                          ,sal      emp.sal%type
                          ,comm     emp.comm%type 
                          ,deptno   emp.deptno%type);

   TYPE emp_tab_type IS TABLE OF emp_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_type(ename => 'Smith'
                                          , Job => 'Clerk')
                          ,7782 => emp_type(ename => 'Clark'
                                          , Job => 'Manager'
                                          , Sal => 3000)
                          ,7902 => emp_type(ename => 'Ford'
                                          , Sal => 3000
                                          , Deptno => 20));
 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Now…normally we would make use of %ROWTYPE when we need a record structure that reflects a table row…

DECLARE
   TYPE emp_tab_type IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => ?(ename => 'Smith'
                                   , Job => 'Clerk')
                          ,7782 => ?(ename => 'Clark'
                                   , Job => 'Manager'
                                   , Sal => 3000)
                          ,7902 => ?(ename => 'Ford'
                                   , Sal => 3000
                                   , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Hmm….what “constructor” should we use if the associative array holds a rowtype? But…perhaps a subtype could help.

DECLARE
   SUBTYPE emp_row_type IS emp%ROWTYPE;
   TYPE emp_tab_type IS TABLE OF emp_row_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_row_type(ename => 'Smith'
                                              , Job => 'Clerk')
                          ,7782 => emp_row_type(ename => 'Clark'
                                              , Job => 'Manager'
                                              , Sal => 3000)
                          ,7902 => emp_row_type(ename => 'Ford'
                                              , Sal => 3000
                                              , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

ORA-06550: line 6, column 36: 
PLS-00222: no function with name 'EMP_ROW_TYPE' exists in this scope

Too bad…that would have been nice too.


Conclusion

Qualified expression will help us to write code that is more readable and easier. It’s a nice new feature that I will use for sure.

And perhaps, someday, the %ROWTYPE thing will be possible too.

 

 

 

ORACLE 18c – ALTER SEQUENCE RESTART

20 Tuesday Feb 2018

Posted by troroblog in ORACLE, ORACLE 18c, SQL

≈ 3 Comments

Tags

Sequence, SQL

How many times have we seen the request to “set back” or “set forward” a sequence to its original value. Reasons for doing this have been e.g. having a sequence, that start every time period (e.g. month) new or a heavy import has been done and the sequence should now be set to the max-value+1 of the rows inserted to the table.

There have been various ways to solve this kind of issues.

  • To set the sequence back normally we dropped and recreated it (and hopefully did not forget the grants).
  • To set the sequence to a new high value, we just built a loop or changed the stepwidth to get the sequence to the desired value.

All those workarounds are no longer needed with ORACLE 18c as there is a new option that we can use to handle both cases.


Test 1:

After having consumed 500 number from the sequence, we would like to set it back to it’s original value (1).

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
      1

So this works as expected.


Test 2:

After having consumed 500 number from the sequence, we would like to set it forward to 1500.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
   1500

Excellent….


Test 3:

I expect the ALTER SEQUENCE statement to commit open transactions…but just to be sure.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

CREATE TABLE t (x NUMBER);

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501

INSERT INTO t VALUES (1);
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

ROLLBACK;

SELECT *
 FROM t;

X
-
1

ok, would have been nice if not, but it’s what was expected.


Conclusion

Still this is a nice feature available in ORACLE 18c but not in the documentation (SQL 

There you will find:

To restart the sequence at a different number, you must drop and re-create it.

and

You will also miss the RESTART option in the syntax diagram of the ALTER SEQUENCE command…


Update (6.1.2020)

After having filed an SR with ORACLE pointing out that the RESTART option is missing in the documentation, I got the following answer:

It (ALTER SEQUENCE … RESTART) was created for internal operations –
Requested to use (ALTER SEQUENCE) as per documentation.

Which means for me, that the restart option is not supported but as oracle is using it internally …

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...