Oracle Workflow Tables with links

---------Get Process details------------------

SELECT distinct wa_process.NAME, wat_process.display_name, wa_process.item_type,wa_process.TYPE,wa_process.FUNCTION
,trunc(wa_process.BEGIN_DATE),trunc(wa_process.END_DATE),wa_process.VERSION
  FROM wf_activities wa_process, wf_activities_tl wat_process
WHERE wa_process.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_process.TYPE = 'PROCESS'
--   AND wa_process.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type =:A AND TYPE = 'PROCESS')
   AND wat_process.item_type = wa_process.item_type
   AND wat_process.VERSION = wa_process.VERSION
   AND wat_process.LANGUAGE = 'US'
   AND wat_process.NAME = wa_process.NAME
   And to_char(trunc(wa_process.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_process.END_DATE) is null or trunc(wa_process.END_DATE) > sysdate)
UNION
---------Get Event details------------------
SELECT distinct wa_event.NAME, wat_event.display_name, wa_event.item_type,wa_event.TYPE,wa_event.FUNCTION
,trunc(wa_event.BEGIN_DATE),trunc(wa_event.END_DATE),wa_event.VERSION
  FROM wf_activities wa_event, wf_activities_tl wat_event
WHERE wa_event.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_event.TYPE = 'EVENT'
--   AND wa_event.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'EVENT')
   AND wat_event.item_type = wa_event.item_type
   AND wat_event.VERSION = wa_event.VERSION
   AND wat_event.LANGUAGE = 'US'
   AND wat_event.NAME = wa_event.NAME
   And to_char(trunc(wa_event.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_event.END_DATE) is null or trunc(wa_event.END_DATE) > sysdate)
UNION
---------Get Function details------------------
SELECT distinct wa_func.NAME, wat_func.display_name, wa_func.item_type,wa_func.TYPE,wa_func.FUNCTION
,trunc(wa_func.BEGIN_DATE),trunc(wa_func.END_DATE),wa_func.VERSION
  FROM wf_activities wa_func, wf_activities_tl wat_func
WHERE wa_func.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_func.TYPE = 'FUNCTION'
--   AND wa_func.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'FUNCTION')
   AND wat_func.item_type = wa_func.item_type
   AND wat_func.VERSION = wa_func.VERSION
   AND wat_func.LANGUAGE = 'US'
   AND wat_func.NAME = wa_func.NAME
   And to_char(trunc(wa_func.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_func.END_DATE) is null or trunc(wa_func.END_DATE) > sysdate)
UNION  
---------Get Notification details------------------
SELECT distinct wa_not.NAME, wat_not.display_name, wa_not.item_type,wa_not.TYPE,wa_not.FUNCTION
,trunc(wa_not.BEGIN_DATE),trunc(wa_not.END_DATE),wa_not.VERSION
  FROM wf_activities wa_not, wf_activities_tl wat_not
WHERE wa_not.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_not.TYPE = 'NOTICE'
--   AND wa_not.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'NOTICE')
   AND wat_not.item_type = wa_not.item_type
   AND wat_not.VERSION = wa_not.VERSION
   AND wat_not.LANGUAGE = 'US'
   AND wat_not.NAME = wa_not.NAME
   And to_char(trunc(wa_not.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_not.END_DATE) is null or trunc(wa_not.END_DATE) > sysdate)
UNION
---------Get Message  details------------------
SELECT ws.name,wst.display_name,ws.TYPE,'Message',null,null,null,null FROM wf_messages ws,WF_messages_TL wst
Where ws.TYPE IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And ws.type=wst.TYPE
And ws.name=wst.name
And wst.language='US'
UNION
---------Get Message Attribute details------------------
Select ws.NAME,wst.DISPLAY_NAME,ws.message_type,'Message Attribute',null,null,null,null
from  WF_MESSAGE_ATTRIBUTES ws,WF_MESSAGE_ATTRIBUTES_TL wst
Where ws.message_type  IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And ws.NAME=wst.NAME
And ws.message_TYPE=wst.MESSAGE_TYPE
And wst.language='US'
Union
---------Get Lookup  details------------------
Select  wl.LOOKUP_TYPE NAME,wlt.DISPLAY_NAME,wl.item_type,'Lookup Type',null,null,null,null
from wf_lookup_types wl,
      wf_lookup_types_TL wlt
  Where wl.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And wl.LOOKUP_TYPE=wlt.LOOKUP_TYPE
And wl.ITEM_TYPE=wlt.ITEM_TYPE
And wlt.language='US'
UNION
---------Get Item Attribute details------------------
Select WIA.NAME,wit.DISPLAY_NAME,WIA.item_type,'Item Attribute',null,null,null,null
FROM
WF_ITEM_ATTRIBUTES WIA,
WF_ITEM_ATTRIBUTES_TL WIT
Where WIA.item_type =WIT.item_type
And WIA.name=WIT.name
And language='US'
And WIA.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
order by 3,4;

How to tune JDeveloper memory settings to get better performance?

Thats natural that JDeveloper out of the box comes with minimal settings for resource usage. And obviously it is far away not enough for comfortable usage. So there are at least couple of settings that needs to be tuned right after installation:

• Java heap size

Open file %MW_HOME%/jdeveloper/ide/bin/ide.conf and add/change following two lines

1 2 3

AddVMOption -Xms2048M   AddVMOption -Xmx4096M

• Java MaxPermSize

Open file %MW_HOME%/jdeveloper/jdev/bin/jdev.conf and add/change following line

1	AddVMOption -XX:MaxPermSize=1024M

Provided settings should be optimal in most cases if you’re using at least 16GB of RAM.

Additionally one more nice to have feature dealing with memory tuning – its real time memory usage monitor, integrated into JDeveloper GUI. It is displayed in the lower right corner of JDeveloper window:

To make it visible, need to add another row into jdev.conf file:

1	AddVMOption -DMainWindow.MemoryMonitorOn=true

Tune your JDeveloper 12c (12.1.3)

Arda Eralp
PRINCIPAL CONSULTANT

Is your JDeveloper 12c too slow, follow these 3 basic steps and increase perfomance of JDeveloper 12c (12.1.3)

Step 1: Configure JVM settings in jdev.conf

 

Path: $MV_HOME$/jdeveloper/jdev/bin/jdev.conf

# optimize the JVM for strings / text editing

AddVMOption -XX:+UseStringCache

AddVMOption -XX:+OptimizeStringConcat

AddVMOption -XX:+UseCompressedStrings

# if on a 64-bit system, but using less than 32 GB RAM, this reduces object pointer memory size

AddVMOption -XX:+UseCompressedOops

# use an aggressive garbage collector (constant small collections)

AddVMOption -XX:+AggressiveOpts

# for multi-core machines, use multiple threads to create objects and reduce pause times

AddVMOption -XX:+UseConcMarkSweepGC

AddVMOption -DVFS_ENABLE=true

AddVMOption -Dsun.java2d.ddoffscreen=false

AddVMOption -XX:+UseParNewGC

AddVMOption -XX:+CMSIncrementalMode

AddVMOption -XX:+CMSIncrementalPacing

AddVMOption -XX:CMSIncrementalDutyCycleMin=0

AddVMOption -XX:CMSIncrementalDutyCycle=10

Step 2: Configure Jdeveloper memory settings in ide.conf

 

Path: $MV_HOME$/jdeveloper/ide/bin/ide.conf

# Set the default memory options for the Java VM which apply to both 32 and 64-bit VM's.

# These values can be overridden in the user .conf file, see the comment at the top of this file.

AddVMOption -Xms2048M

AddVMOption -Xmx4096M

Step 3: Disable "Build After Save"

Oracle Business Intelligence 11g installation guide (Linux)

GDAL/OGR with OCI support on Linux

---

Written by Andrew Fomin on February, 22 2016

This is a second post of the Maps series. It describes how to build a GDAL/OGR tool.

GDAL - Geospatial Data Abstraction Library

GDAL is a translator library for raster and vector geospatial data formats that is released under an X/MIT style Open Source licence by the Open Source Geospatial Foundation. As a library, it presents a single raster abstract data model and vector abstract data model to the calling application for all supported formats. It also comes with a variety of useful command line utilities for data translation and processing.

In a nutshell, GDAL/OGR is a library and a set of command line tools for manipulating geodata. It is used as a library in many products or can be used standalone for different purposes. I'm going to use ogr2ogr tool for importing Open Street Maps .pbf files into Oracle Spatial.

This tool is distributed in source codes and you need to build it before use. While this procedure is quite simple, I haven't found a complete guide for building it with OCI and PBF support on Linux platform.

At the time of writing this post, the current version is 2.0.2. You can get actual sources here

Download the source code.

Unpack it.

Change directory to the unpacked sources.

Run ./configure command.

Depending on your machine and which packages are installed you can get another result. I use freshly installed Oracle Linux 6 VM for this gude. You need to check these positions:

• Expat support


• OCI support


• SQLite support

Expat and SQLite libraries can be easily installed with yum (or other package managers). We need to install both library and -devel packages.

Now ./configure should show that we have expat and SQLite libraries. But no OSI support yet.

For building GDAL/OGR with OCI support, we need to download and install Oracle Database Client. You need at least Runtime install type. Instant client is not enough.

Update: See info from John A Stevenson about using Oracle Instant Client below in the comments section.

Define ORACLE_HOME variable. If you don't do it, the compiler won't see your newly installed database client.

Run ./configure once again. You could see that all required options are "yes" for this time. If something of these three options is "no" you should resolve the issue before proceeding.

If everything is OK you may run make. It takes a few minutes to complete.

And the last step - make install (sudo make install).

Now your tools are located in the /usr/local/bin directory (you could change it with --prefix option of the configure commnad).

And configuration files are located in the /usr/local/share/gdal directory.

I'm going to use ogr2ogr program. A small test.

The file I'm going to use contains non-English characters, so I need to define NLS_LANG environment variable. It looks like the only important part is a character set (UTF8).

The basic syntax for ogr2org is the following:
org2ogr -f OCI OCI:username/password@tnsalias filename It looks like that only tnsalias is supported. I haven't managed to get work EZ-connect syntax.

This is the basic syntax, and it's likely that a big pbf file won't import with the default options

It's possible that we get some warnings. I see no impact of this warning on the result so simply ignore it.

Now we can open schema and see what was created. There are five tables for different types of data: LINES, ULTILINESTRINGS, MULTYPOLYGONS, OTHER_RELATIONS and POINTS.

We can preview data using Oracle Mapbuilder by now. It's OK that it look ugly. Later I'll show how to make it look good.

In the next post I'm going to show osmconf.ini file and some basic options for importing big data files.

maps OSM GDAL linux

---

Andrew Fomin

OBIEE specialist since 2007 and Oracle Discoverer before. DWH architect, BI enthusiast, blogger. Lazy cats owner. All opinions are my own and not the views of my employer.

Moscow

---

Additional Posts

• 
  Importing OSM Geofabrik data into Oracle database with GDAL/OGR

  This post is the third in line of posts about Maps generally and Open Street Maps in particular. The previous…
• 
  Maps starting post

  Preface This post starts a series of posts about using GIS data for visualizing an information in OBIEE. I don't…

Share This Post

   

Like

1

Comments (5)

Closed discussion

M

Andrew Fomin> John A Stevenson2018.04.13 17:25

John A Stevenson

It is possible to build the GDAL/OGR OCI client without installing the Oracle Runtime. Just the Instant Client and more...

Hi John.

Thanks for the info.

+0

-0

Родион Куликов2018.04.13 15:30

Ну что за люди пошли, толькo вчepа тyт былa ссылкa нa caйт на кoторoм мoжнo хoть нeмнoгo зарабoтaть, сeгодня eе yжe нет, удалили, зачeм так делaть?

+0

-0

Наиль Данилов2018.04.13 14:48

Message deleted

Этo пpоcтo капец какoй тo, ну пoлyчилоcь у Вac - отлично, зачем вeзде тpубить пpo это, дa eщe и ccылки на caйт публикoвать, yдалитe!

+0

-0

Видана Устинова2018.04.13 14:47

Как так может пoлучатьcя, чтo одни нормaльно дeнегe зapaбaтывaют а тут вoт перeбиваeшьcя нa гpани беднoсти! Ведь ecть же возможности, тoлько где, кто можeт подcкaзать?

+0

-0

John A Stevenson2018.04.13 14:39

It is possible to build the GDAL/OGR OCI client without installing the Oracle Runtime. Just the Instant Client and Instant Client SDK are required. The Dockerfile below shows how. It is based on https://github.com/GeographicaGS/Docker-GDAL2/tree/master/2.2.4, and includes a tip from GDAL-dev mailing list (https://lists.osgeo.org/pipermail/gdal-dev/2013-April/036097.html).

The tricky steps are to create symlinks for the libocci.so and libclntsh.so files and to register them with ldconfig.


```
##
# geographica/gdal2
#
# This creates an Ubuntu derived base image that installs GDAL 2.
#
# Ubuntu 16.04 Xenial Xerus
FROM ubuntu:xenial

MAINTAINER Cayetano Benavent < cayetano.benavent@geographica.gs >

ENV ROOTDIR /usr/local/
ENV GDAL_VERSION 2.2.4
ENV OPENJPEG_VERSION 2.2.0

# Load assets
WORKDIR $ROOTDIR/

# Install basic dependencies
RUN apt-get update -y && apt-get install -y \
software-properties-common \
python-software-properties \
python3-software-properties \
build-essential \
python-dev \
python3-dev \
python-numpy \
python3-numpy \
libspatialite-dev \
sqlite3 \
libaio1 \
libpq-dev \
libcurl4-gnutls-dev \
libproj-dev \
libxml2-dev \
libgeos-dev \
libnetcdf-dev \
libpoppler-dev \
libspatialite-dev \
libhdf4-alt-dev \
libhdf5-serial-dev \
wget \
bash-completion \
cmake \
unzip


# Download source code
#ADD http://download.osgeo.org/gdal/${GDAL_VERSION}/gdal-${GDAL_VERSION}.tar.gz $ROOTDIR/src/
#ADD https://github.com/uclouvain/openjpeg/archive/v${OPENJPEG_VERSION}.tar.gz $ROOTDIR/src/openjpeg-${OPENJPEG_VERSION}.tar.gz
COPY gdal-2.2.4.tar.gz $ROOTDIR/src/
COPY openjpeg-2.2.0.tar.gz $ROOTDIR/src/

# Compile and install OpenJPEG
RUN cd src && tar -xvf openjpeg-${OPENJPEG_VERSION}.tar.gz && cd openjpeg-${OPENJPEG_VERSION}/ \
&& mkdir build && cd build \
&& cmake .. -DCMAKE_BUILD_TYPE=Release -DCMAKE_INSTALL_PREFIX=$ROOTDIR \
&& make && make install && make clean \
&& cd $ROOTDIR && rm -Rf src/openjpeg*

# Install the proprietary Oracle client libraries (see README.md for details)
ENV INSTANTCLIENT=instantclient-basic-linux.x64-12.2.0.1.0.zip
COPY $INSTANTCLIENT $ROOTDIR/src/
ENV INSTANTCLIENTSDK=instantclient-sdk-linux.x64-12.2.0.1.0.zip
COPY $INSTANTCLIENTSDK $ROOTDIR/src/

RUN cd src && unzip $INSTANTCLIENT && unzip $INSTANTCLIENTSDK \
&& cd instantclient_12_2 \
&& ln -s libocci.so.12.1 libocci.so \
&& ln -s libclntsh.so.12.1 libclntsh.so \
&& echo ${ROOTDIR}src/instantclient_12_2 > /etc/ld.so.conf.d/oracle_oci.conf \
&& ldconfig && cd $ROOTDIR

# Compile and install GDAL
RUN cd src && tar -xvf gdal-${GDAL_VERSION}.tar.gz && cd gdal-${GDAL_VERSION} \
&& ./configure --with-python --with-spatialite --with-pg --with-curl \
--with-openjpeg=$ROOTDIR \
--with-oci-include=/usr/local/src/instantclient_12_2/sdk/include \
--with-oci-lib=/usr/local/src/instantclient_12_2 \
&& make && make install && ldconfig \
&& apt-get update -y \
&& apt-get remove -y --purge build-essential wget \
&& cd $ROOTDIR && cd src/gdal-${GDAL_VERSION}/swig/python \
&& python3 setup.py build \
&& python3 setup.py install \
&& cd $ROOTDIR && rm -Rf src/gdal*

# Output version and capabilities by default.
CMD gdalinfo --version && gdalinfo --formats && ogrinfo --formats
```

+1

+1

-0