Oracle Plsql Database Administration 11g12c Dba Developer

What Are The Language Features Of Pl/SQL Preview 20:48

The previous lesson has given us great idea on what the PL/SQL is and what makes it stand out. In this chapter we will be talking extensively about on the aspects of the language. Identical to the diverse programming languages that we have, the PL/SQL has reserved words, punctuation, data types, character set and fixed syntax rules. 

Structure Of A Program Block Preview 02:08

When it comes to programming, a block, which is also referred to as code block, is a lexical structure of source code that are group together. The block is made up of one or more than one statements and declaration. The program block allows collections of statements to be carried out as if they were one. As it constricts the lexical scope of variables, functions and procedures that are declared in a block to prevent mix-up with variables with similar name even if they are in a different program and even used for dissimilar purposes.

Using The Nested Program Blocks Preview 06:03

When we talk about nested blocks, we are talking about PL/SQL blocks that are embedded in one or more PL/SQL block and such nested block gives you a better command of the exception handling and program execution while also restrict variable scope or visibility. In nested blocks, all the inner blocks are will also be PL/SQL block, having the same features and characteristics with the outer block. 

Software In General Preview 03:45

The concept of software cannot be discussed without first having a clear understanding of what a computer program signifies. A computer program can be described as a set of electrical impulses that move within the computer and tells the computer what tasks to carry out and how well to carry out such tasks. These programs executes what might be related to magic as they interact with the mouse, the memory and also the display screen which in turn gives the humans the permission to perform useful tasks, solve high-level problems and play a lot of interesting games. It is good to know that there are several types of computer programs. These programs are also function specific. 

Tools For Web Development Preview 08:42

In the last lesson we took our time to intimate ourselves on the essence and usefulness of computer software. We saw the relationship between computer software and the general term people refer to as computer programs. By now we know that a software is an aspect of the computer that is intangible but necessary for execution of some works on the computer. We also know that a program is a sort of software. Software however could be grouped into several aspects. 

Introduction To Functions Preview 05:32

       In the last chapter of the course we discussed extensively on the meaning of software and the types of soft wares that we have. We went ahead to intimate ourselves on the tools needed for the creation of a software. Haven understood the fact that among the tools needed for creating a software is the programming language, we discussed python programming in general as a relation to the programming language needed to create a software. In this chapter of the lesson we are going to be looking in the concept of functions in the python programming language concept.

Standard Functions Preview 05:27

               The standard functions are the general terms used in mathematics to declare certain functions and tasks. The standard math module provides much of the functionality of a scientific calculator.

What Are Triggers? Preview 01:03

A database trigger is to extraordinarily put away strategy that is run when particular activities happen inside a database. Most triggers are characterized to run when changes are made to a table's information. Triggers can be characterized to pursue rather than or DML (Data Manipulation Language) activities, for example, INSERT, UPDATE, and DELETE. Triggers enable the database creator to guarantee certain activities, for example, keeping up a review record are finished paying little mind to which program or client rolls out improvements to the information. 

Creating The Different Types Of Trigger Preview 06:55

There are diverse sorts of triggers in sql server, these are:

•            Data Manipulation Language (DML) Triggers

DML triggers are executed when a DML operation like INSERT, UPDATE OR DELETE is let go on a Table or View. There are two kinds of DML, they are (I) BEFORE and AFTER Triggers (ii) rather than triggers. 

Applying Triggers Within An Application Preview 03:39

There are three principle components to be considered in including triggers, they are: (I) Actions (ii) The slide or layer in which the move makes put (where) and (iii) When {the occasion that occurred when it happened. 

System Event Triggers Preview 03:38

Framework occasions like LOGON and SHUTDOWN, give a component to following framework changes. Oracles helps you in consolidating this following with database occasion notice, which gives a straightforward and rich strategy for conveying non-concurrent informing to an application. Client occasions that can fire triggers are identified with client logon and logoff, DDL explanations, and DML proclamations. LOGON and LOGOFF triggers can be related with the database or with a diagram. 

Maintaining The Database Triggers Preview 02:36

Much the same as systems, triggers rely upon referenced items. Oracle consequently deals with the conditions of a trigger on the mapping objects referenced in its trigger activity. The reliance issues for triggers are the same as those for put away systems. They are embedded into the information word reference. 

What Are Packages? Preview 05:45

In our last class, we learnt about triggers how it could be created and used. In this section of our tutorial we will be talking yet about another must-know in the PL/SQL which is the packages.

A package is said to be schema object that groups locally related PL/SQL types, subprograms and variables. Packages usually have two obligatory parts, which are:

• Package specification
• Package body

Creating Packages Preview 06:54

As we have duly learnt what packages are about and the two major types. It is time for us to go further in our study of packages. For you to create a package specification, you will make use of the SQL statement CREATE PACKAGE. An example of package specification with a single procedure can be seen in the snippet below but do bear t in mind that you can have multiple procedures or functions and lots of universal variables defined within a package. The execution of the code above will bring about package creation.

Using System-Supplied Packages Preview 13:52

The UTL_FILE is a package supplied to enable the PL/SQL to read and write operating system text files. It also supply a restricted version of standard Operating system file input or output. The UTL_FILE can only read and write clear text files. Which means it is not capable to write or read binary file and the characters in the binary data will make the UTL_FILE pop up with issues.

Maintaining Packages Preview 00:29

Packages maintenance is as essential as using the packages the right way. When you are still using any type of the package, make sure that you follow the appropriate procedures so that you can get you work done in the most effective way. And do not forget that you will still have to use the packages some other time so you have to store and close it properly to avoid starting all over again.

About Cursor Preview 04:22

Moving on in our study of the PL/SQL program unit. We are going to be talking about cursors, what they mean, why we use them and how to use them. But first, what do we understand by cursors?

Explicit Cursor Preview 01:39

The explicit cursors is mandated to be created when a SELECT statement that results to more than a row is being executed. Though the cursor is meant to save more than one records in it, we have learnt that it can only process the records one at a time and the processed row is called current row. When there is need to control accurately the query processing, it is then that you can explicitly declare a cursor in the declarative part of any PL/SQL subprograms, package or block.

Opening And Declaring The Cursor Preview 04:35

When working with the cursors, you will have to declare the cursor before you move on to referencing it in some other statements. When you are in the declaring stage, you will have to name the cursor so as to relate it to a particular query. Although, if you like, you can declare a return type for the cursor, for instance the table_name%ROWTYPE. And also as a substitute to referring to local variables, you can stipulate the parameters that you want to use in the WHERE clause. 

Advanced Cursor Techniques Preview 11:41

In this last subsection of the cursor, we will be elucidating on the more technical usage of the cursor. And we will be talking about two major advanced techniques and these are the usage of the variables of cursor and also the expression of the cursor.

Creating Stored Procedures & Functions Preview 15:57

7.1 Creating Stored Procedures and Functions

In order to create a stored procedures and functions, the following are to be considered:

1.           In Server Explorer, right-tap the Functions organizer or any capacity in that envelope.

2.           Point to Add New and afterward pick Inline Function, Table-esteemed Function, or Scalar-esteemed Function on the alternate way menu. 

Implementing Stored Procedures And Functions Preview 01:23

The stored procedure functions is main used in selecting query and it ca also be called in the stored procedure. For the sake of better understanding, we will create “hi there” using the stored procedures and functions.

Maintaining Stored Procedures And Functions Preview 07:39

In a database administration framework DBMS, a put away system is an arrangement of Structured Query Language SQL explanations with a doled out name that is put away in the database in gathered shape so it can be shared by various projects. The utilization of put away techniques can be useful in controlling access to information end-clients may enter or change information yet don't compose methods, protecting information uprightness and enhancing efficiency proclamations in a put away system just should be composed one time. 

Quiz Section 1