Developers Club geek daily blog

Oracle Database 12c: setup of performance

2 years, 10 months ago

Database Time

Before speaking about optimization of performance of databases, it is necessary to explain by what indicator this performance is measured — especially as in memory of many people connected with databases the indicator "response time" which many got used to consider as a universal criterion of performance of DBMS still is live.

Read more »

7 errors of the ETL developer

2 years, 10 months ago
Projects of data storages are part of IT infrastructure of the majority of large enterprises for a long time. Processes of ETL are part of these projects, however developers sometimes make the same mistakes at design and maintenance of these processes. Some of these errors are described in this post.

Read more »

Sublime text for the developer's PL/SQL

2 years, 10 months ago
There was a wish to tell about how many favourite Sublime Text can be used as quite good means for development on PL/SQL.

It would be desirable to begin with for what this bicycle was necessary, there are, it seems, many other IDE for work with SQL and in particular Oracle PL/SQL, such as Toad for Oracle, SQL Navigator, PL/SQL Developer and even free Oracle SQL Developer, however the majority of them has several shortcomings in comparison with text editors like Emacs, SciTe, Vim, Notepad ++, Sublime Text, etc.

I will list some of them, this list especially subjective:

  • "Ponderousness" of each IDE, it is expressed not only in the amount of a distribution kit, but also the general feelings, existence of a set not of the necessary functions, buttons, responsiveness, etc.
  • The majority of acceptable IDE are paid, or conditionally free with limited functionality.
  • It would be desirable a krossplatformennost, under this requirement from the given IDE only Oracle SQL Developer gets.
  • Functionality of text editing. In the majority of them there are only basic methods on work with the text: it is text typing, copy-paste, syntax highlighting. About any "CTRL+D" as speech Sublime Text also does not go.
  • Simplicity of expansion, practically all IDE are closed, any support of samopisny pagin, etc.

There is one more point, but it belongs to the organization of projects and tasks on a workplace more. It would be desirable to touch a little upon this subject as the organization on projects left the mark on the Sublime Text setup.

Read more »

Parallel processing of a big selekt in several sessions

2 years, 11 months ago
Provide: is selekt which returns records, each of which needs to be processed, and whether it is a lot of records, whether processing of each record takes a lot of time, and processing of one record does not depend on processes of other records.
Classical example to involve a multithreading or in case of databases to execute processing in several sessions. In Orakle hint / * + by parallel () */and pipelined functions is for this purpose used. It is healthy, but if you have Oracle standard edition (where parallel does not work) or you want to process not each record separately (for reasons that it is better to save up work, and then in bulk, with one blow, to execute), and to divide all output of a selekt to pieces and to process everyone separately?

Read more »

We configure a linking of Apache Zeppelin + Oracle

2 years, 11 months ago
Long ago I look for the convenient tool for execution of ad hoc SQL queries in Oracle DB, with a possibility of fast creation of different types of diagrams on data retrieveds. Everything that can facilitate operational creation of reports, as they say "on the fly". Quite recently came across here such tool as Apache Zeppelin. The short overview of opportunities on the website and a demo video showed in documentation that it is piece rather interesting and it makes sense to investigate more densely it and to configure access from Apache Zeppelin to Oracle DBMS.

Read more »

We accelerate OSB

2 years, 12 months ago
Article is prepared by Dmitry Ovcharenko, the architect of Department of applied financial systems of the company of "Infosistemy Jett"

Expecting a problem

Starting development of proxy service on Oracle Service Bus, it is necessary to take conditions of use of this service into account. For example, if the developed process participates in a set of popular operations or to be used by a large number of users. In case of, for example, information request for the first page after a user login on the website it is necessary to adhere to reasons of the optimum code from the point of view of performance at once.

In the course of writing of transformations and all appeals to ways in the message be not too lazy to specify [1] after each node in XPath


It will allow to process expression as the appeal to unit elements of structure, but not to multiple as it is meant generally.

Read more »

Regular expressions of Oracle. Dangerous range

3 years ago

The Oracle developer who is often using regular expressions in code especially on bases with orthodox settings, can face sooner or later the phenomenon which, except as mysticism, you will not call in any way. Long searches of origins of problem can lead to loss of weight, appetite and to provoke different psychosomatic frustration — all this I now and I will try to prevent. And I will be helped with it by the regexp_replace function. It can have to 6 arguments:

  1. iskhodnaya_stroka,
  2. template,
  3. zamenyayushchaya_stroka,
  4. position of the beginning of search of coincidence to template (by default 1),
  5. number of occurrence of template in initial line (by default 0 – all occurrences),
  6. modifier (meanwhile dark horse)
Returns the changed iskhodnuyu_stroka in which all occurrences of template are replaced with the value transferred in the parameter of zamenyayushchaya_strok. Often use the short version of function where 3 first arguments are set that happens enough for solution of many tasks. I too so will make. Let's say we need in line 'MASK: lower case' to disguise all lower case characters asterisks. The template of '[a-z]' has to be suitable for task of range of lower case characters. We check

select regexp_replace('MASK: lower case', '[a-z]', '*') as result from dual

| RESULT           |
| MASK: ***** **** |

| RESULT           |
| *A**: ***** **** |

If on your base this phenomenon was not reproduced, means to you it was still lucky. But more often diggings in codings begin, convertings of lines from one symbol set in another and over time there comes approximately such status

Read more »

Oracle, typical problems of SQL. The guaranteed choice

3 years ago

On nose elections, and it means that today we will select the governor. To be exact to assign barrier of passing of candidates for the following round. But at first it is necessary to decide on terms. What is the guaranteed choice in SQL? Let's say in request condition to the table comparison of any field with any variable is executed. Depending on value of this variable the request can return lines from the table, and can not return them at all. If such variable value drops out that lines from the table do not return, for this case it is necessary to generate specially predeterminated left result. That is anyway the general request has to with guarantee yes return something. The term is taken from here. However the task becomes complicated that (and can and vice versa, becomes simpler) that instead of one simple cell with value, is necessary for us is guaranteed to return full-fledged line.

I provide data of the Central Electoral Commission. The first round of vote has ended with such results
ID Name of the candidate Profession Number of votes
1 Incorruptible Amoral Chistorukovich prosecutor 9867
2 Effective Budget Osvoilovich businessman 8650
3 Truthful Chtodadut Napisaylovich editor of the newspaper 745
4 Comely Lucifer Feoktistovn  prior 234
5 Hrensgora Ktotakoy Niktoneznayevich teacher of rural school 3

Read more »

Know the consumer, or Industry solutions of Oracle

3 years ago
In addition to your goods or service the consumer buys three things — conversation, community and the smart contents. Thus it is not important that you sell — pills for cough or the career excavator. To sell or service goods or service, systems which manage consumer experience are necessary and constantly give the client the necessary feelings and the necessary information.

Read more »

Oracle, typical problems of SQL. Transformation of the values listed in column in lines of the table

3 years ago
Welcome to Hollywood. I represent you today's heroines

Angelina, Charlize and Penelope. Wake up, girls.

Today we are waited by society columns. At first we learn these three beauties had who in husbands. And then, by means of plain special effects, I will show you, in what order they brakosochetatsya with each other. So this article will be especially interesting to young maidens.

Let's create and will fill base table
create table hollywood 
with t (id, actress, husbands) as (
  select 1, 'Анджелина Джоли', 'Джонни Ли Миллер, Билли Боб Торнтон, Брэд Питт' from dual union all
  select 2, 'Шарлиз Терон', null from dual union all
  select 3, 'Пенелопа Крус', 'Хавьер Бардем' from dual
select * from t;

alter table hollywood add primary key (id);

Read more »