Learning Oracle Integration Cloud Services SOA | BPM

Scheduler is a process which runs programmed jobs at scheduled times. This process wakes up at the specified time and runs automatically. The basic elements of the scheduler are: Jobs Schedules Programs A job specifies what needs to be executed and when. A program is a collection of metadata that will be executed by the scheduler. It contains the name of the program (a procedure or an executable, the type of the program (pl/sql block, shell script etc). A schedule specifies when and how many times a job is executed. 1. Login to the database using Toad, and create a Scheduler Job as follows;

What are Packages in Oracle Database A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification and Package Body. Package Specification Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code. Package Body Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification.

In a database management system, a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data, preserving data integrity and improving productivity. The Oracle PL/SQL language allows you to write procedure to centralize the business logic and store the code in the database. I have created the following Stored Procedure on Oracle using TOAD. This particular Stored Procedure will Insert a Customer to the Customer Table. For the sake of simplicity, Customer Table contains only 4 columns (Customer ID, Customer Name, City and Contact No). In this post I will show you how to create Oracle stored Procedure using TOAD.  Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser.

The Oracle PL/SQL language allows you to write functions and procedure to centralize the business logic and store the code in the database.  A stored function is similar to a stored procedure with one key difference, a stored function returns a single value. This makes a stored function available in your SQL SELECT statements, unlike stored procedures that you must call within an anonymous PL/SQL block or another stored procedure. In this post I will show you how to create Oracle stored Functions using TOAD.  Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser.

A synonym is a database object that provides a better name to refer to objects. You can use synonyms to  shorten lengthy names or to ease the reference to an object owned by another user. You can create  synonyms for a table, view, sequence, procedure and other objects. In this post I will show you how to create Synonyms in Oracle Database using TOAD for Oracle. Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser

We cannot characterize a many-to-many association directly in a relation scheme, because two tables cannot be children of each other. When you design your ERD, you will be challenged by lots of possibilities for many to many relationships. You would have learned that we do not want to draw many-to-many relationships in our ERD. We want to break them up by putting a junction-entity in between. Instead of a many-to-many relationship, we then get two one-to-many relationships with the junction entity in the middle. Examples: 1. Student-professor. A student will have one or more professors. The same professor will have lots of students. 2. At a hospital a patient will be assigned to a coupld of nurses. A specific nurse will be assigned to 1 or many patients. 3. A student will have lots of subjects and the same subject can be taken by lots of students. Lets look at the Student - Professor Now, because many-to-many are not allowed, we will change this to add a junction-entity (StudentProfess...

One to Many relationship is the most common relationship and your database entity relationship diagram (ERD) will be full of this kind of relationship. Examples 1. Master-detail. You have a master record with many detail records. For example an order, there will be a master record with the order date, person placing the order, etc. And then detail records of everything ordered. The master record will have many details, and the detail will have only 1 master. 2. Supervisor-subordinates. A supervisor will have one or many subordinates. A subordinate will have only 1 manager. 3. Division- department. A division will have one or many departments. A department will belong to only 1 division. Let’s look at the Master - Detail The two entities could translate into the following two tables: In the Master table, the OrderNumber is the primary key. In the detail table, OrderNumber is the foreign key. The primary key in the Detail table will be a combination of OderNumber and LineNumber. The inf...

Restriction for the use of LONG columns in Oracle Databsae 1) Only one column of type LONG is allowed.

The Oracle BFile type, is used to store unstructured binary data outside of the database in the operating system. BFiles are read-only and only support random-reads. CLOB type is stored in the database using two byte code character set. Unlike BFile, CLOB support sequential access and can be modified.

Whenever we issue an INSERT, DELETE, UPDATE or single row SELECT INTO statement, Oracle creates a implicit cursor to deal with this operation. An explicit cursor, is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR … IS  statement.

The DUAL table is a table that Oracle creates with it is data dictionary consisting in exactly one row. DUAL belongs to the SYS user but all users can query it , and use it’s unique property of returning only one row, to select pseudo-columns .

1) As a competent DBA, before creating a new user to check the following items: * Determine the object must be stored in the user table space; * Determine the quota for each table space; * Give the user the default table space and temporary table space; * Create a user; * The user system privileges and roles required to give users; 2) The following example creates a user:     create user infotech - -to create a user name for the infotech;     identified by infotech -- the user's password is infotech;     default tablespace APEX_SPACE -- the user's default table space is APEX_SPACE;     temporary tablespace temp --the user temporary table space is pioneer_temp;     quota 30m on APEX_SPACE --the user to use up to 30M in APEX_SPACE space;     quota 30m on users --the user in the users table space to use up to 30M of space;     password expire --the first time the user l...

A view logically represents subsets of data from one or more tables. You can restrict a user’s access to data in a table by using views. A view does not have a structure of its own and is stored as a SELECT statement in the database. Views are of two types, simple and complex: • Simple view: Retrieves data from only one table and contains no functions or group data. You can  perform DML operations through a simple view. • Complex view: Retrieves data from multiple tables or contains functions or group data. A complex  view does not always allow you to perform DML operations. Creating Simple View You create a simple view based on only one table. In other words, the SELECT statement of the view  retrieves data from only one table. For example:- CREATE OR REPLACE VIEW EMP_VU (EMPNO, EMP_NAME, ANNUAL_SALARY, DEPTNO) AS SELECT e.EMPNO, e.EMP_NAME, e.SALARY*12, e.DEPTNO FROM employee e; Write the SQL script in SQL tab and press F5 to execute. ...

In this tutorial I am sharing how to create a table in Oracle Database  using TOAD for Oracle Software. A table is a database object created by users to store data in the form of rows and columns. A table is the basic unit of storage. Table can be created at any time, even while users are accessing the database. A database table consists of column that needs to be defined when creating the table. Table can have up to 1,000 columns. A table must conform to some standard database object-naming conventions. Follow the given below steps to create table in Oracle Database using TOAD software.