First of all, I would like to express my graditude for OTN and Tim Hall who is the father of idea. This is amazing. OTN is a very powerfull community and I am very proud to be part of this community. Thanks for all.

In this article, I’ll give short information about Invisible Columns that has come with the Oracle 12c. I hope this will be a beneficial article in terms of awareness.

With the Oracle 12c, there has been a feature added to tables about the visibility of columns. Now we can easily make the desired columns in the table visible or invisible. In my opinion, this feature was necessary because it seems practical to allow everyone in the tables, which are generically reached, to see every column without creating “view”.

As can be seen from the example above, after determining the data type the column can be made invisible by inserting the phrase “invisible” to associated column. Now, let’s see in which actions this column is invisible.

I’m very honored to announce that I was accepted to Oracle ACE Program as an ACE. This is a very important step in my carreer. Being amongest the valuable experts is awesome for me. I would like to express my gratitude to Gurcan Orhan, Steven Feuerstein and Laura Ramsey for being reference for my ACE nomination.

Previous week I recieved one more good news from Gokhan Atil who is Board of Directors at TROUG. His ACED nomination has accepted from Oracle ACE Program and now He is ACED. This is a very good thing for Turkish Oracle User Community. Congrats Gokhan Atil .

In this article I’ll speak about improvements that were made by Oracle 12c and SQL, such as the phrases LATERAL, CROSS APPLY and OUTER APPLY. I hope this will be a helpful article in terms of awareness.

When writing queries in database versions before Oracle 12c, we weren’t able to use correlated subquery in the FROM section of our query. With Oracle 12c, the basis for our uses and needs of this sort has been accessed.

Now, let’s figure out how this structure works through some examples.

First of all, let’s look at the result that we get when we run the query below in 11g database.

So, subselect that we wrote in from section of our query is dependent on employees table that we used externally.
Due to this dependence, Oracle could not recognize the external-dependent parameter in subquerry, and received an error. And this is the very point on which Oracle made the necessary improvements, thus paving the way for the user for these kinds of uses.
Now, let’s try to substantiate this need on 12c database.

We can see that the result returns successfully when we run our query. With this command, now we could use correlated subquerry on FROM side. What about execution plan of our query, though?

Now, let’s examine CROSS APPLY and OUTER APPLY. CROSS APPLY and OUTER APPLY also run in a similar way to LATERAL basis, and serves a similar need. The differences between are: if wer run CROSS APPLY, it automatically applies CROSS JOIN on tables/inline views that we wrote in FROM. If we use OUTER APPLY, it applies LEFT OUTER JOIN on tables/inline views that we wrote in FROM.
Now, let’s see how to use both these two commands.

In this article I will mention the temporal validity subject which is a new feature that has come with Oracle 12c R1. I hope that will be a beneficial article in terms of awareness.

Temporal validity offers us an infrastructure where we can render the data we have, valid for a particular time range and query our table depended on the time. It’s actually a mechanism that allows us to examine the status of the data in the table or to use it, in any time interval.

Now let’s get to the examples quickly and try to understand the subject.

Firstly let’s talk about the scenario. Let there be an invoice table and a validity interval assigned by us for the every entry in this table. Then let’s change our session parameters according to the validity dates of the inserted entries and query our table in a time period we want.

(You can see the article which includes different uses of syntax of the PERIOD FOR phrase from the links at the end of this article)
We have created the infrastructure where we can query our table according to time with the previous process. Actually Oracle has already created 2 invisible columns in the background using the valid_time name that we had given with the PERIOD FOR phrase. The names of these are;
valid_time_start
valid_time_end
If we query them in the dictionary we will not be able to see their existence as they are invisible.

Now we have provided them with time dimension with the PERIOD FOR phrase, let us update valid_time_start and valid_time_end columns which are created as invisible and whose validity according to time we can adjust, for every entry.

With the line above, we enabled “the 7 minutes ago” in our session. Now let us look at the valid data in that time interval.

SELECT * from hr.invoice;

We listed the entries that were valid 7 minutes ago. From this example, do not think that it’s only possible to go back in time. We can go to a time period where the validity is forward in time and examine the case in that time.

With the methods above, we can query our data however we want. We can even pull the data with the 2nd method and join it with other tables. In addition to that, in Temporal Validity infrastructure, in columns that include unique key constraint in our table, it’s given permission to violation of this constraint while it’s guaranteed that the entries will never overlap in means of time. It means that entries containing the same unique key can be present in the table more than one on condition of all being in different validity periods.

In this article I’ll be talking about TRANSACTIONAL DDL in Oracle. Hopefully, this will be helpful for your awareness.

For those who are interested in Oracle usually have certain ideas as to how transaction management is performed in Oracle. (For those who are unaware of this can take a look at the following links so as to have a rough idea. Transactions-1Transactions-2Transactions-3). Based on this, it’s obvious that transaction logic of DDL and DML operations is essentially different. In this writing, we’ll browse how DDL operations, which are autocommit, can be processed in a transactional way.

We possess the information that an implicit commit is run before and after DDL operations. It can be said that with this performance, a transaction is launched and ended for each DDL. However, Oracle can render this performance transactional for certain DDL operations, though not all of them. In other words, for successive DDL operations, a new transaction isn’t launched and ended. A transaction is launched for the whole of the related DDL, or all of them work successfully, or all DDL operations are undone when one receives an error.

So, which DDL operations can I run in a single transaction?

CREATE TABLE

CREATE VIEW

GRANT

After giving the basics, I’ll exemplify this operation.

We use the phrase CREATE SCHEMA AUTHORIZATION for this operation. However, to use this phrase, first we need to follow some rules. These rules are:

After the phrase CREATE SCHEMA AUTHORIZATION, we need to enter a schema user. This user, though, must be the same schema user that we’ll use to connect to database, run the related DLLs, which will consequently create objects.

For example, if I want to create a table under HR user, I need to connect to DB with HR user, write CREATE SCHEMA AUTHORIZATION HR and start this operation. Here, we need to remember this detail: We don’t create a new schema with CREATE SCHEMA command. With this phrase, we tell the system that we won’t use more than one transaction for DDLs.
Now let’s exemplify the topic at hand.

So, I intentionally made a mistake in the constraint that we form while creating authmaster table, and caused the whole ddl statement to receive an error. Due to this error, while authdetail was successfully created, the table was not. We can observe that GRANT command, which also ran successfully, was undone.

As can be understood from the example, we can make all DDL statements transactional. With this feature, we can avoid the separation of transaction in code blocks, in which we use DDL and DML statements jointly.

Oracle Cloud Day 2015 Istanbul was held in Lutfu Kirdar Congress Center on 19’th November. Events connected between Oracle products and Oracle users and then it provided to be sharing environment among attendees as usual. Thanks to Oracle and all event sponsors for the organizing this valuable event.

TROUG as we, made plenty of techinal presentation in the hall that allocated for us.

First presentatiton was held by Zekeriya Beşiroğlu who is chairman of the TROUG. His presentation name was “Big Data and Big Picture. This presentation showed that Where big data trends go to and What is the Big data technologies evolution. All audience listened carefully and they had point of view this topic after this presentation.

I spoke to second place in the hall. My presentation name was “Oracle 12c SQL New Features”. I showed that the new features of Oracle SQL 12c using presentation. The hall was almost completly full during my session. This detail made me very happy. I hope this has been a helpful presentation for their awareness.

Next session held by Başar Gülcü from Turkcell. This presentation name was “Telco Big Data & Data Monetization”. He talked to one of the big data use case in the Telco industry. This session showed that big data studies in Turkey are increasing day by day and great number of large scale companies invest this area agressively.

At the end of the last session in the hall held by Mehmet Eser and Orhan Eripek who are managing members of TROUG. This presentation name was “Oracle Enterprise Manager Cloud Control 12c”. This presentation targeted to especially DBA’s. Presentation inclueded plenty of new features about Oracle Enterprise manager. Especially new mobile application support for the remote control was so interesting.

This year we have left behind very successful and beautiful event. I hope It has been very useful event for all participants. Many thanks to everyone who contributed to this organization. I wish to be together again in another event.

In this context, I will talk about new online platform that belongs to Oracle. I hope, it would be helpful documents for the Oracle Professionals.

Last Month, Oracle annouced the new online platform that name is LiveSQL. LiveSQL is a platform where we can run SQL and PL/SQL. No longer, we will not need to
any virtual machine or local Oracle installments to try something on Oracle Database thanks to LiveSQL platform.

I think LiveSQL is a very beneficial platform(which was developed using Oracle APEX) for Oracle Professionals . It has a lot features to writing SQL and PL/SQL codes.

For Example
– You can write SQL or PL/SQL code on every computer thanks to browser based infrastructure.
– It can log your session history.
– You can manage your schema objects using Schema pages. Schema pages are interactive and user friendly pages. It is very easy to use.

– You can create schema objects using well designed wizards.

– You can access the community codes. This section is so beneficial and I think, it will be able to grows rapidly day by day.

You need to Oracle Account to using LiveSQL platform. If you have not any Oracle Account, you will take it using this link.