Oracle TimeZone and daylight savings

Posted on March 19, 2018 by kuperusm

With the upcoming daylight savings weekend, it’s always nice to (re-)discuss datetime and timezone problems.

In our current project we receive EDI files with the customers estimated time of arrival and estimated time of departure. The EDI file contains strings (not dates). Note: they indicate the localtime for which the customer will arrive and depart.

In the database we store dates as timestamps in UTC format. Now, on our different test servers we get different results for some testcases for when they arrive on saturday and leave on monday. Note: on our client side application the localtime is displayed, so a conversion from UTC to the screen is also necessary.

Consinder the following (simplified) table and row indicating arrival and departure at locatime 8:00 AM

CREATE TABLE EDITABLE
( eta_str VARCHAR2(50)
, etd_str VARCHAR2(50)
);
INSERT INTO EDITABLE(eta_str, etd_str) VALUES ('20180324080000','20180326080000');

During the conversion from EDI strings to timestamp we use the following:

set serveroutput on size 100000
declare
  lc_FormatMask CONSTANT VARCHAR2(50) := 'YYYYMMDDHH24MISS';
  l_eta_ts      TIMESTAMP(6);
  l_etd_ts      TIMESTAMP(6);
  l_eta_gui     DATE;
  l_etd_gui     DATE;
begin
  /* get the EDI strings as TimeStamps */
  select Sys_Extract_UTC(To_Timestamp(to_char(to_date(eta_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
       , Sys_Extract_UTC(To_Timestamp(to_char(to_date(etd_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
  into   l_eta_ts
       , l_etd_ts
  from   EDITABLE;

  /* show what is stored */
  dbms_output.put_line('Eta in: ' || to_char(l_eta_ts, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd in: ' || to_char(l_etd_ts, 'YYYYMMDDHH24MISS'));

  /* conversion used by GUI */
  l_eta_gui := CAST(from_tz(l_eta_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);
  l_etd_gui := CAST(from_tz(l_etd_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);

  /* show what is displayed */
  dbms_output.put_line('Eta gui: ' || to_char(l_eta_gui, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd gui: ' || to_char(l_etd_gui, 'YYYYMMDDHH24MISS'));
end;
/

Now, on one server there was some interesting output shown:
Eta in: 20180324070000 Etd in: 20180326070000 Eta gui: 20180324080000 Etd gui: 20180326090000
The input datetime is not converted to the proper UTC datetime, while the output converts it correctly to locatime. Resulting in the wrong time.

After looking too long to the output of SELECT * FROM SYS.NLS_SESSION_PARAMETERS; noticing nothing strange, the following statement was issued:

SELECT SESSIONTIMEZONE FROM DUAL;

Resulting in ‘+01:00’ for the particular server. On all other servers the result was ‘Europe/Amsterdam’

After adding

EXECUTE IMMEDIATE 'ALTER SESSION SET TIME_ZONE = '''||l_time_zone||'''';

in the code:

set serveroutput on size 100000
declare
  lc_FormatMask CONSTANT VARCHAR2(50) := 'YYYYMMDDHH24MISS';
  l_eta_ts      TIMESTAMP(6);
  l_etd_ts      TIMESTAMP(6);
  l_eta_gui     DATE;
  l_etd_gui     DATE;
  l_time_zone   CONSTANT VARCHAR2(30) := 'Europe/Amsterdam';
begin
  EXECUTE IMMEDIATE  'ALTER SESSION SET TIME_ZONE = '''||l_time_zone||'''';

  /* get the EDI strings as TimeStamps */
  select Sys_Extract_UTC(To_Timestamp(to_char(to_date(eta_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
       , Sys_Extract_UTC(To_Timestamp(to_char(to_date(etd_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
  into   l_eta_ts
       , l_etd_ts
  from   EDITABLE;

  /* show what is stored */
  dbms_output.put_line('Eta in: ' || to_char(l_eta_ts, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd in: ' || to_char(l_etd_ts, 'YYYYMMDDHH24MISS'));

  /* conversion used by GUI */
  l_eta_gui := CAST(from_tz(l_eta_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);
  l_etd_gui := CAST(from_tz(l_etd_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);

  /* show what is displayed */
  dbms_output.put_line('Eta gui: ' || to_char(l_eta_gui, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd gui: ' || to_char(l_etd_gui, 'YYYYMMDDHH24MISS'));
end;
/

The proper result was shown:
Eta in: 20180324070000 (UTC-1) Etd in: 20180326060000 (UTC-2) Eta gui: 20180324080000 Etd gui: 20180326080000

So, 2 lessons learned :)
-Read the Manual (Chapter 11 page 54 of Oracle® Database SQL Language Reference 11g Release 2):

Note: Time zone region names are needed by the daylight saving feature. These names are stored in two types of time zone files: one large and one small. One of these files is the default file, depending on your environment and the release of Oracle Database you are using. For more information regarding time zone files and names, see Oracle Database Globalization Support Guide.

-Make sure all your environments are created and configured the same

datapump expdp without access to expdp tool nor filesystem

Posted on April 24, 2016 by kuperusm

This article is about getting a database export dump without having access to the expdp tool nor the filesystem. In some cases you just don’t have access to the databaseserver and no one is available (like a dba), still you need sufficient accessrights. Note: this is merely an excersize, if you’re planning on doing this notify your dba anyway, this article is not about secretly getting a database dump :-)

For this to work, you don’t need access to the expdp tool or the filesystem but you need some rights on a directory object (preferable read/write) and have execute rights on the DBMS_DATAPUMP package.

Every step is mentioned here, maybe in your situation you can skip some steps.

1. Create a test user

CREATE USER TSTUSER
 IDENTIFIED BY "testing"
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 ACCOUNT UNLOCK;

GRANT UNLIMITED TABLESPACE TO TSTUSER;
GRANT CREATE SESSION TO TSTUSER;
GRANT CREATE TABLE TO TSTUSER;
GRANT EXECUTE ON SYS.DBMS_DATAPUMP TO TSTUSER;
GRANT EXP_FULL_DATABASE TO TSTUSER;

2. Create a directory object where the output will get written to

CREATE OR REPLACE DIRECTORY DATADIR_FLATF AS '[D:\directorypath]';
GRANT READ, WRITE ON DIRECTORY DATADIR_FLATF TO TSTUSER;

3. Create a controltextfile. In this file the filenames should be present which represent your dump files. Save your controlfile ‘lob_data.txt’ to the DATADIR_FLATF location.

DECLARE
  out UTL_FILE.FILE_TYPE;
BEGIN
  out := UTL_FILE.FOPEN('DATADIR_FLATF', 'lob_data.txt' , 'W');
  UTL_FILE.PUT_LINE(out , '1,TWO_SCHEMAS.DMP,');
  UTL_FILE.PUT_LINE(out , '2,TWO_SCHEMAS.LOG,');
  UTL_FILE.FCLOSE(out);
END;
/

content of file is now (extra , at the end is not a typo):
1,TWO_SCHEMAS.DMP,
2,TWO_SCHEMAS.LOG,

4. Create a datapump export using DBMS_DATAPUMP

DECLARE
  handle NUMBER;
  status VARCHAR2(20);
BEGIN
  handle := DBMS_DATAPUMP.OPEN ('EXPORT', 'SCHEMA');
  DBMS_DATAPUMP.ADD_FILE (handle, 'TWO_SCHEMAS.DMP', 'DATADIR_FLATF');
  DBMS_DATAPUMP.ADD_FILE (handle, 'TWO_SCHEMAS.LOG', 'DATADIR_FLATF', null, DBMS_DATAPUMP.ku$_file_type_log_file);
  DBMS_DATAPUMP.METADATA_FILTER (handle, 'SCHEMA_EXPR', 'IN (''SCHEMA1'',''SCHEMA2'')');
  DBMS_DATAPUMP.START_JOB (handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB (handle, status);
  DBMS_OUTPUT.PUT_LINE('Status : ' || status);
END;
/

5. Create an external table to gain access

CREATE TABLE lob_tab (
  number_content    NUMBER(10),
  blob_content      BLOB
)
ORGANIZATION EXTERNAL
(
  TYPE    ORACLE_LOADER
  DEFAULT DIRECTORY DATADIR_FLATF
  ACCESS  PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    BADFILE DATADIR_FLATF:'lob_tab_%a_%p.bad'
    LOGFILE DATADIR_FLATF:'lob_tab_%a_%p.log'
    FIELDS  TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      number_content    CHAR(10),
      blob_filename     CHAR(100)
    )
    COLUMN TRANSFORMS (blob_content FROM LOBFILE (blob_filename) FROM (DATADIR_FLATF) BLOB)
  )
  LOCATION ('lob_data.txt')
)
PARALLEL 2
REJECT LIMIT UNLIMITED
/

6. Test query

SELECT number_content, DBMS_LOB.getlength(blob_content) AS blob_length
FROM   lob_tab;

7. If you encounter the following

ORA-12801: error signaled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
error opening file D:\directorypath\TWO_SCHEMAS.DMP

place extra , at the end of each line maybe?

8. Download the files to your local OS using your favorite tool

And there you have it. A complete dump of your schema choice locally available without having the expdp tool installed on your system.

9. Note, if you do have access to the filesystem, it looks similar to this

10. Drop tstuser and objects

DROP USER TSTUSER CASCADE;

SP2-0027: Input is too long (> 2499 characters) add newline after comma fix

Posted on March 14, 2016 by kuperusm

Having a big file exported using SQL Developer I got the “SP2-0027: Input is too long (> 2499 characters)” error recently.
Since the file was too big to edit by hand, I used the following to add newlines after every comma in the file:

user@server:~/work$ cat file.sql | perl -pe 's/,/,\n/g' > new_file.sql

Generate XSD from base table with foreign key to primary key references

Posted on October 22, 2015 by kuperusm

For our Oracle BizTalk interface we use an XSD file to describe the master detail table sets. Surely there are enough tools to generate the XSD file, so here is another one which I created to fit our needs. It’s easily adjustable.

It starts from the base table, searches for foreign key references which map to the primary key using the following query:

    select dco1.owner
         , dco1.table_name
         , dcc.column_name
    from   dba_constraints  dco1
         , dba_cons_columns dcc
    where  dco1.constraint_type = 'R'
    and    dco1.r_constraint_name in (select dco2.constraint_name
                                      from   dba_constraints dco2
                                      where  dco2.owner      = '[OWNER]'
                                      and    dco2.table_name = '[TABLE]'
                                      and    dco2.constraint_type = 'P')
    and    dcc.owner = dco1.owner
    and    dcc.table_name = dco1.table_name
    and    dcc.constraint_name = dco1.constraint_name;

This way, other foreign keys to unique attributes are not included in the XSD file generation, just the one’s who refer to the primary key are included.

When references are found it calls itself and so on (a maximum depth can be supplied).

For the BizTalk implementation only NUMBER or STRING element types are relevant, but it is easy to adjust the attribute generation using the CASE statement:

    select column_name as element_name
         , case when dtc.data_type = 'NUMBER' then 'type="xs:int"/>' else 'type="xs:string"/>' end as element_type
    from   dba_tab_cols dtc
    where  dtc.owner      = '[OWNER]'
    and    dtc.table_name = '[TABLE]'
    order  by dtc.column_id;

The package generates a CLOB which you can insert into a table or write to a file. In the example the CLOB is inserted into a table:

CREATE TABLE TMP_CLOB (message CLOB, datetime_col DATE);

The package:
Package PCK_GenXsd.pks specification.
Package PCK_GenXsd.pkb body.

Call it:

begin
  PCK_GenXsd.PR_GenXsd('NICENAME','OWNER','STARTINGTABLE');
end;
/

Tables used by materialized views in a schema

Posted on September 24, 2015 by kuperusm

Query to show all tables used (and how many times) by your materialized views in a schema
SELECT ad.detailobj_owner , ad.detailobj_name , count(1) as occurrence FROM all_mview_detail_relations ad WHERE ad.owner = 'YOURSCHEMA' GROUP BY ad.detailobj_owner, ad.detailobj_name ORDER BY ad.detailobj_owner, ad.detailobj_name