Learn Oracle Database with Free Lessons & Tips

I have 7+ years of experience in PHP/MySQL development. I want to change my job profile in database. Could anybody provide me some material on PL SQL Developer and performance tuning?

read less

Sir can you please tell me about exame pattern of DBA (OCA, OCP, OCM). If I want to write OCM exam then what is the cause duration for entire course? read less

What is Data:-

The data is stored representation of objects and events which has the meaning .It can be structured or unstructured.

What is Information:-It is the data in the processed form which will increase the knowledge of end user.

What is Metadata:-It is the data which describes the properties or characteristics of the user data and its context.

What is database:-Database is the collection of data in one or more data file.

DBMS:-Database management system is the software which is used to create or manage the database and provides the controlled access to the database.

Database from Oracle point of View:-Database is the collection of data in one or more data file.

It is the collection of Logical structure and Physical Structure which are configured to maintain the integrity of the system.

Logical structured:-In the database State, The Logical structured are the representation of the actual metadata which are in the form of Table space, data dictionary.

Table space:- It is invisible in nature and bridge between the oracle database and Data File structure.

Data dictionary: - It is also a logical structure which basically captures the metadata of the Objects.

Physical Structure: - In the database State, The Physical structure are in the form of Table,synonym,view etc.

read less

Im a DBA with just the basic skills hence I got no confidence to face an interview. As a result Im stuck in the same company for 4.5 yrs with no progress Financially or Technology skills. Please help me in deciding what to do next etc. read less

VIRTUAL OR DERIVED COLUMN

A nice feature introduced in Oracle 11gR1. Welcome to the practical analysis of various scenarios with virtual columns - introduced in 11gR1 are like normal table columns whose values are derived from other columns. 

• Creating table with virtual column

DROP TABLE t_virtual_test1 PURGE;
CREATE TABLE t_virtual_test1
(
salary NUMBER,
commission NUMBER,
total NUMBER GENERATED ALWAYS AS (salary + commission) VIRTUAL
);

• Inserting values into virtual columns

INSERT INTO t_virtual_test1 (salary, commission) VALUES (10000,999);
COMMIT;

The third column (which is virtual/derived) is auto derived (not auto populated).

SELECT * FROM t_virtual_test1;

SALARY	COMMISSION	TOTAL
10000	999	10999

We will see difference between auto derivation and auto population in later sections. Actually, the above logic is same as deriving values in SELECT like

SELECT salary, commission, salary + commission total FROM t_virtual_test1;

We will try out different scenarios starting with the below INSERT. Any guess about the value in the virtual column TOTAL?

INSERT INTO t_virtual_test1 (salary, commission) VALUES (20000,NULL);
COMMIT;

SELECT * FROM t_virtual_test1;

SALARY	COMMISSION	TOTAL
Rs. 10000	null	null

• Data dictionary view

As a developer/DBA, it is important to get to know the data dictionary details corresponding to each new concept your are learning. For virtual columns, we can check the column DATA_DEFAULT in data dictionary USER_TAB_COLUMNS

TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_DEFAULT
T_VIRTUAL_TEST1	SALARY	NUMBER	null
T_VIRTUAL_TEST1	COMMISSION	NUMBER	null
T_VIRTUAL_TEST1	TOTAL	NUMBER	"SALARY"+"COMMISSION"

• Other scenarios

* Try updating the actual columns, and when you SELECT, the virtual column value will be derived based on the new/updated/latest values in the actual columns.

UPDATE t_virtual_test1
SET commission = 111;
COMMIT;

SELECT * FROM t_virtual_test1;

* Try giving your own value for derived column. You cannot insert value for derived columns because - it is always derived.

INSERT INTO t_virtual_test1 (salary, commission, total) VALUES (10000,999,10999);

Read the error report carefully - you get so much info/clue from this report
always note the error code and error message
--ORA-54013:Insert operation disallowed on virtual columns

Same for UPDATE.

UPDATE t_virtual_test1 SET total = 4444 WHERE salary = 10000;

error - Update operation disallowed on virtual columns

* Is there any way to include derived column name in INSERT?

yes - we can use the DEFAULT keyword (DEFAULT - is a basic concept which we normally use in our DMLs to instruct Oracle to go by default settings given during table creation. Please search and learn more about default).

INSERT INTO t_virtual_test1 (salary, commission, total) VALUES (11,22,DEFAULT);
COMMIT;

Note: Using DEFAULT in UPDATE will not work for virtual columns

• Variations in virtual column creation Syntax

Above points discussed gives an overview of Virtual column. Now we will look into the optional syntax keywords and what will happen if we ignore those keywords while virtual column creation

* We can omit the datatype of virtual column - datatype is auto set based on the expression given,       like (salary + commission) gives a NUMBER in below example

DROP TABLE t_virtual_test2 PURGE;
CREATE TABLE t_virtual_test2
(
salary NUMBER,
commission NUMBER,
total GENERATED ALWAYS AS (salary + commission) VIRTUAL
);

DESC t_virtual_test2

SALARY          NUMBER
COMMISSION NUMBER
TOTAL            NUMBER

 * GENERATED ALWAYS and VIRTUAL keywords are optional

DROP TABLE t_virtual_test3 PURGE;
CREATE TABLE t_virtual_test3
(
salary NUMBER,
commission NUMBER,
total AS (salary + commission)
);

INSERT INTO t_virtual_Test3 VALUES (10000,300,default);
COMMIT;

Keep you updated in next post.

I keep my posts designed in such a way that the contents are more program oriented. The program speaks volumes. I welcome your suggestions and reviews to update my style.

read less

I am looking to learn oracle forms and reports not in terms of theoretical training, As I want to work in a project base training. Those who have completed a Oracle project on your own can please guide me how to work in a real time project? read less

Yes, Oracle Forms is front-end tool for rapid development and it uses PL/SQL code in forms . You should know pl/sql before jumping to Forms and even it supports Java beans, jars etc.. It's tight integration with Oracle Database. Now Forms is part of Oracle Fusion Middle, latest version is 12c. By default for basic DML operations without any code also it will take care by itself to insert, update, delete etc,,, it can be called as intelligent Tool. read less

Hello, I have completed my Oracle DBA certification on 9I 5 years back but somehow I stuck working with support industry with ITIL related stuff. Now I want to switch my profile. Is it possible if I brush up my skills and try for a technical profile? I want to get into Oracle related profile. read less

Let me list few standard questions asked in the interviews on plsql package.

1. Why packages are used? What are it's advantages?

2. Name few bulin-in Oracle packages.

3. Can we have a function or procedure declared in Package specification and not defined in package body and why?

4. Can we have a function or procedure not declared in Package Specs and defined in Package body alone?

5. What are private components in Plsql packages?

6. Can we have only a Package specification and no body at all? If yes, under what circumstances? Such a Package specification would be compiled without errors?

7. Can we have Package body alone and no Specs? Will it be compiled without errors?

8. What is overloading and how is it accomplished in Oracle?

9. What are restrictions on overloading?

10. What are differences between and advantages/disadvantages of stand alone and packaged procedures/Functions?

11. What is forward declaration?

12. What are the disadvantages of Pacakges?

13. What is session varibale and how is it defined in package?

14. How Packages improve or degrade performance?

15. What is pragma serially_reusable and why is it used?

These questions actually cover the entire lesson on Plsql Packages.

 

 

 

read less