OTN Appreciation Day : Adding Invisible Column

Hi everyone,

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”.

Now, let’s see how an invisible column is created.

CREATE TABLE invColExm
(
colA NUMBER,
colB NUMBER INVISIBLE,
colC VARCHAR2 (100)
);

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.

1 – SELECT * FROM invColExm; — colB is invisible.

2 – DESC invColExm; — colB column is invisible in the list.

3– /in the table referenced with* ROWTYPE ,
the invisible column cannot be reached,
therefore it’s invisible in here too*/

I think this feature is so useful when you need to hide some important data. Also using and understanding of this feature is so easy.

If you want to learn some detail information about this feature, you can click this link.

Happy OTN Appreciation Day

Emrah METE

Advertisements
Posted in Oracle, Root, Uncategorized | Tagged , , , | Leave a comment

Announcement: New Oracle ACE

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 OrhanSteven 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 .

My Ace Profile is as follow:

https://apex.oracle.com/pls/apex/f?p=19297:4:::NO:4:P4_ID:15345

These were sent by Oracle Ace Program. Ace Plaque and Special Gifts.

 

Posted in Oracle, Root | Tagged , , , , | Leave a comment

Oracle SQL: LATERAL, CROSS APPLY, OUTER APPLY

Hi everyone,

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.

 

SELECT *
FROM hr.employees e,
  (SELECT * FROM hr.departments WHERE department_id = e.department_id) x;

ORA-00904: "E"."DEPARTMENT_ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 14 Column: 55

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.

SELECT *
FROM hr.employees e,
  LATERAL
  (SELECT * FROM hr.departments WHERE department_id = e.department_id) x
WHERE x.department_name ='Public Relations';

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?

lateral-join

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.

SELECT *
FROM hr.employees e
  CROSS APPLY
  (SELECT * FROM hr.departments WHERE department_id = e.department_id) x;

The query above integrated the two groups by using cross join.

SELECT *
FROM hr.employees e
  OUTER APPLY
  (SELECT * FROM hr.departments WHERE department_id = e.department_id) x;

outer-apply

The query above integrated the two groups by using outer join.

So, it seems that these commands which have newly arrived with ORACLE 12c SQL will facilitate our job a great deal in cases where we’ll need them.

Posted in Oracle, Root | Tagged , , , , , | Leave a comment

Oracle Temporal Validity

Hi everyone,

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.

 

CREATE TABLE hr.invoice
  (
    custid    NUMBER,
    inv_no    NUMBER,
    inv_total NUMBER,
    inv_date  DATE,
    t_date    TIMESTAMP
  ); 

INSERT INTO hr.invoice VALUES  (1,1,100,sysdate-30,sysdate);
INSERT INTO hr.invoice VALUES  (1,2,900,sysdate-60,sysdate);
INSERT INTO hr.invoice VALUES  (1,3,700,sysdate-90,sysdate);
INSERT INTO hr.invoice VALUES  (1,4,600,sysdate-120,sysdate);
INSERT INTO hr.invoice VALUES  (1,5,300,sysdate-150,sysdate);
INSERT INTO hr.invoice VALUES  (1,6,200,sysdate-180,sysdate);
COMMIT;

We’ve created our table and inserted data in it. Now, it’s time to run the feature that will grant our table a time dimension.

We provided a time dimension to our table by using the PERIOD FOR phrase. We could also do this while creating our table like below.

CREATE TABLE hr.invoice
  (
    custid    NUMBER,
    inv_no    NUMBER,
    inv_total NUMBER,
    inv_date  DATE,
    t_date    TIMESTAMP,
    PERIOD FOR valid_time
  ); 

(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.

SELECT owner,
  table_name,
  column_name,
  data_type
FROM all_tab_columns
WHERE owner    = 'HR'
AND table_name = 'INVOICE';

tmv1

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.

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '20' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE inv_no         =1;


UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '20' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE inv_no         =2;

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '15' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '5'  MINUTE
WHERE inv_no         =3;

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '19' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE inv_no         =4;

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP - INTERVAL '14' MINUTE,
    valid_time_end   = SYSTIMESTAMP - INTERVAL '3'  MINUTE
WHERE inv_no         =5;

UPDATE hr.invoice
SET valid_time_start = SYSTIMESTAMP + INTERVAL '2' MINUTE,
    valid_time_end   = SYSTIMESTAMP + INTERVAL '5'  MINUTE
WHERE inv_no         =6;

COMMIT;

With the updates we have made, we made every one of the entries valid in a particular time. Now, what kind of a result appears when I query my table without touching my session’s flashback setting?

tmv2

I was able to view all the data in my table when I queried without touching the flashback setting. Good, now how can I query the table for a particular period or a point of date?

This question has many answers; now let’s look at the methods in order.

QUERYING THE DATA ACCORDING TO THE TIME

1- DBMS_FLASHBACK_ ARCHIVE : You can do the query using DBMS_FLASHBACK_ARCHIVE package. However, in order to use it, the user we connect with should have an authorization on that package.

GRANT ALL ON SYS.dbms_flashback_archive TO HR;

After authorizing the user, now we can adjust the time as session based and re-run our query.

EXECUTE SYS.DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ASOF', SYSTIMESTAMP - INTERVAL '7' MINUTE);

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; 

tmv3

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.

EXECUTE SYS.DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ASOF', SYSTIMESTAMP + INTERVAL '3' MINUTE);

We have enabled the “3 minutes after”. Now let’s query again.

SELECT * from hr.invoice; 

tmv4

As can be seen, the entry that would be valid after 3 minutes has appeared.

2- AS OF PERIOD FOR and VERSIONS PERIOD FOR phrases: Like the previous example, let us query the 7 minutes ago with this method.

SELECT * from hr.invoice AS OF PERIOD FOR valid_time SYSTIMESTAMP - INTERVAL '7' MINUTE;

tmv5

And now, let’s query the time period between 10 minutes and 18 minutes ago.

SELECT * from hr.invoice VERSIONS PERIOD FOR valid_time between  SYSTIMESTAMP - INTERVAL '18' MINUTE and  SYSTIMESTAMP - INTERVAL '10' MINUTE;

tmv6

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.

REFERENCES

Posted in Oracle, Root | Tagged , , , | Leave a comment

TRANSACTIONAL DDL BASIS WITH CREATE SCHEMA PHRASE

Hi everyone,

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-1 Transactions-2  Transactions-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.

CREATE SCHEMA AUTHORIZATION hr
CREATE TABLE authdetail
(
   id        NUMBER PRIMARY KEY,
   depname   VARCHAR2 (10)
)
GRANT ALL ON authdetail TO SCOTT
CREATE TABLE authmaster
(
   id             NUMBER PRIMARY KEY,
   name           VARCHAR2 (10),
   surname        VARCHAR2 (10),
   departmentid   NUMBER,
   CONSTRAINT fk_x FOREIGN KEY (departmentid) REFERENCES authdetail (XYXXXX)
);

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.

SELECT *
  FROM user_tables
 WHERE table_name = 'AUTHDETAIL';

-- no rows selected.

SELECT *
  FROM table_privileges
 WHERE owner = 'HR' AND TABLE_NAME = 'AUTHDETAIL';
-- no rows selected.

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.

REFRENCES

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm

https://blogs.oracle.com/sql/entry/creating_multiple_tables_in_a

Posted in Oracle, Root | Tagged , , , , , , | Leave a comment

Oracle Cloud Day 2015 Istanbul Reflections

Hi everyone,

20151119_125627

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.

z1 z2 z3

 

 

 

 

 

 

 

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.

em2 em3 em1

 

 

 

 

 

 

 

 

Third session was held by Erkan Ülgey and  Özgür Umut Vurgun who are managing members of TROUG. Presentation name was . “Oracle Database Appliance ile Cloud Entegrasyonu”. This presentation was higly technical and so detailed. And also we had a point of view about  Oracle cloud investment thanks to this session.

eu eu2 eu3

 

 

 

 

 

 

 

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.

ba1 ba2 ba

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

oe1 oe2 oe3

 

 

 

 

 

 

 

 

 

 

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.

Posted in Oracle, Root | Tagged , , , , , , | Leave a comment

LiveSQL!!! Platform

Hi Everyone,

livesqlstart

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.
create schema object

– You can create schema objects using well designed wizards.

createdbob

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

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

Oracle_Account

Access Link for LiveSQL Platform.
LiveSQL!!!

Enjoy it.

Posted in Oracle, Root | Tagged , , , , | Leave a comment