1.Learn PL/SQL

********************************************************
Summary of How this page can help you

The below article will give you a brief overview of what Oracle PL/SQL means and a little bit of
PL/SQL concepts.
At the end of the article we have put some links and also recommended a PL/SQL book which you
can prepare. Most importantly , if you are not confident in PL/SQL we strongly
urge you to take up a Instructor led course in PL/SQL.

To go back to the dba career shift home page click dbacareershift  

********************************************************

Why is PL/SQL necessary to become a DBA?

It is true that lot of DBA tasks can be done through the ORACLE Enterprise manager . Oracle
Enterprise manager is a tool provided by oracle to do majority of DBA tasks just
by clicking buttons so you dont have to know either sql or pl/sql.
Dont be happy to read the above statement.The reason is ,even though it looks simple enough to
click all buttons,what would you do if there are issues and if you get errors while
clicking on buttons? In that case you should be able to read and understand the errors and if needed
you should be able to understand the underlying SQL code with which the oracle objects are
created. If necessary you might have to look at the underlying stored procedures or packages to
find out the issue.Hence please learn PL/SQL and your life will be much easier as a DBA.

PL/SQL

The first important thing to know is that PL/SQL and SQL are two different languages. PL/SQL is a
combination of SQL statements that can be executed in one go. This whole block can be called a
PL/SQL Block. The advantage is that you dont have to manually execute 1 SQL and then execute
2nd SQL etc. Instead of that you group all related SQL
statements together and save it. This saved code can be called a procedure or function or trigger or
package or just an anonymous block depending on how you want to save it
and depending on which rules you followed. Having given a general idea of what PL/SQL is , below
is brief overview of PL/SQL taken from Oracle website. At the end of the article
is the link that directly takes you to the PL/SQL book on the oracle website.

Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the
following advantages:
• Tight Integration with SQL
• Better Performance
• Higher Productivity
• Full Portability
• Tight Security
• Access to Pre-defined Packages
• Support for Object-Oriented Programming
• Support for Developing Web Applications and Pages

Tight Integration with SQL

SQL has become the standard database language because it is flexible, powerful, and easy to learn.
A few English-like commands such as SELECT, INSERT, UPDATE, and
DELETE make it easy to manipulate the data stored in a relational database.

PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control
commands, as well as all the SQL functions, operators, and pseudocolumns. This
extensive SQL support lets you manipulate Oracle data flexibly and safely.

Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between
your applications and the database.

The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL
and PL/SQL datatypes; a NUMBER or VARCHAR2 column in the database is stored in a
NUMBER or VARCHAR2 variable in PL/SQL. This integration saves you both learning time and
processing time.

Special PL/SQL language features let you work with table columns and rows without specifying the
datatypes, saving on maintenance work when the table definitions change.
Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and
processing each line in popular scripting languages.

Using PL/SQL to access metadata about database objects and handle database error conditions,
you can write utility programs for database administration that are reliable and
produce readable output about the success of each operation.

Many database features, such as triggers and object types, make use of PL/SQL. You can write the
bodies of triggers and methods for object types in PL/SQL.
PL/SQL supports both static and dynamic SQL.

The syntax of static SQL statements is known at precompile time and the preparation of the static
SQL occurs before runtime, where as the syntax of dynamic SQL statements is
not known until runtime. Dynamic SQL is a programming technique that makes your applications
more flexible and versatile.

Your programs can build and process SQL data definition, data control, and session control
statements at run time, without knowing details such as table names and WHERE
clauses in advance. For information on the use of static SQL with PL/SQL, see Chapter 6,
"Performing SQL Operations from PL/SQL".

For information on the use of dynamic SQL, see Chapter 7, "Performing SQL Operations with
Native Dynamic SQL". For additional information about dynamic SQL, see Oracle
Database Application Developer's Guide - Fundamentals.

Better Performance

Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many
SQL statements require multiple calls to the database, resulting in significant
network and performance overhead.

With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically
reduce network traffic between the database and an application.

As Figure 1-1 shows, you can use PL/SQL blocks and subprograms to group SQL statements
before sending them to the database for execution. PL/SQL also has language
features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls
are efficient. Because stored procedures execute in the database server, a single
call over the network can start a large job. This division of work reduces network traffic and
improves response times. Stored procedures are cached and shared among users,
which lowers memory requirements and invocation overhead.

To proceed to the next page please click
PL/SQL continued