Case Study Oracle Pl/Sql Tutorial

3 Case Studies

The following case studies for Informix demonstrate some of the features of the Oracle Database Gateway. You can verify that the gateway is installed and operating correctly by using the demonstration files included on the distribution media.

The demonstration files are automatically copied to disk when the gateway is installed.

This chapter contains the following sections:

Case Descriptions

The cases illustrate:

  • A simple query (Case 1)

  • A more complex query (Case 2)

  • Joining Informix tables (Case 3)

  • Write capabilities (Case 4)

  • A data dictionary query (Case 5)

  • The pass-through feature (Case 6)

Distribution Media Contents

The distribution media contains the following:

  • Demonstration files

  • One SQL script file that creates the demonstration tables in the Informix database

  • One SQL script file that drops the demonstration tables from the Informix database

Demonstration Files

After a successful gateway installation, use the demonstration files stored in the directory where is the directory under which the gateway is installed. The directory contains the following demonstration files:

Demonstration FilesDemonstration Files

Demonstration Requirements

The case studies assume these requirements have been met:

  • The gateway demonstration tables are installed in the Informix database

  • The Oracle database has an account named with a password of

  • The Oracle database has a database link called (set up as public or private to the user ) which connects the gateway to a Informix database as with password

    For example, you can create the database link as follows:

    SQL> CREATE DATABASE LINK GTWLINK CONNECT TO SCOTT 2 IDENTIFIED BY TIGER2 USING 'GTWSID';
  • Oracle Net Services is configured correctly and running.

  • The Informix environment variable, , is set correctly.

Creating Demonstration Tables

The case studies are based on the , , and tables. If the demonstration tables have not been created in the Informix database, use the script to create them, as follows:

Set environment variable .

If you have the Bourne or Korn Shell, enter the following:

$ DELIMIDENT = y; export DELIMIDENT

If you have the C Shell, enter the following:

$ setenv DELIMIDENT y $ cd $ORACLE_HOME/dg4ifmx/demo$ dbaccess database_name bldifmx.sql

The script creates the demonstration tables in the Informix database accordingly:

CREATE TABLE GTW_EMP ( EMPNO SMALLINT NOT NULL ENAME VARCHAR(10), JOB VARCHAR(9), MGR SMALLINT, HIREDATE DATETIME, SAL NUMERIC(7,2), COMM NUMERIC(7,2), DEPTNO SMALLINT) CREATE TABLE GTW_DEPT ( DEPTNO SMALLINT NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13)) CREATE TABLE GTW_SALGRADE ( GRADE MONEY, LOSAL NUMERIC(9,4), HISAL NUMERIC(9,4))

Demonstration Table Definitions

The following table definitions use information retrieved by the SQL*PLUS command:

GTW_EMP

Name Null? Type ------------------------------- -------- ---- EMPNO NOT NULL NUMBER(5) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(5) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(5)

GTW_DEPT

Name Null? Type ------------------------------- -------- ---- DEPTNO NOT NULL NUMBER(5) DNAME VARCHAR2(14) LOC VARCHAR2(13)

GTW_SALGRADE

Name Null? Type ------------------------------- -------- ---- GRADE NUMBER(19,4) LOSAL NUMBER(9,4) HISAL NUMBER(9,4)

Demonstration Table Contents

The contents of the Informix tables are:

GTW_EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ----- --- --- -------- --- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

GTW_DEPT

DEPTNO DNAME LOC ----- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

GTW_SALGRADE

GRADE LOSAL HISAL ------ ------ ----- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999

Case 1: Simple Queries

Case 1 demonstrates the following:

  • A simple query.

  • A simple query retrieving full date information.

The first query retrieves all the data from and confirms that the gateway is working correctly. The second query retrieves all the data from including the time portion of the hire date because the default date format was set to for the session by an command.

Case 2: A More Complex Query

Case 2 demonstrates the following:

  • The functions and in the list.

  • The and clauses.

This query retrieves the departments from whose total monthly expenses are higher than .

Case 3: Joining Informix Tables

Case 3 demonstrates the following:

  • Joins between Informix tables.

  • Subselects.

The query retrieves information from three Informix tables and relates the employees to their department name and salary grade, but only for those employees earning more than the average salary.

Case 4: Write Capabilities

Case 4 is split into three cases and demonstrates the following:

DELETE Statement

Case 4a demonstrates bind values and subselect. All employees in department 20 and one employee, , in department 30 are deleted.

UPDATE Statement

Case 4b provides an example of a simple statement. In this example, employees are given a a month salary increase.

INSERT Statement

Case 4c is an example of a simple insert statement that does not provide information for all columns.

Case 5: Data Dictionary Query

Case 5 demonstrates data dictionary mapping. It retrieves all the tables and views that exist in the Informix database that begin with "".

Case 6: The Pass-Through Feature

Case 6 demonstrates the gateway pass-through feature which allows an application to send commands or statements to Informix.

This case demonstrates:

  • A pass-through statement using bind variables

  • A pass-through statement

UPDATE Statement

Case 6a provides an example of a pass-through statement with bind variables. In this example, the salary for is set to .

SELECT Statement

Case 6b provides an example of a pass-through statement. The data that is returned from the statement is inserted into a local table at the Oracle database.

Oracle PL/SQL Tutorial

This is a free Oracle PL/SQL tutorial that is an introduction to learning the basics of the PL/SQL language. It includes PL/SQL use cases along with practical applications. You will learn about the components of this language like data type, structured query, exception handling, loop statement, procedural language, function procedures creating use cases and more, SQL, as we know, is a powerful structured query language for querying, updating and retrieving data in relational databases like Oracle database. Looking at the intensity of data growing each data and the complexity of managing that data, Oracle created an extension to this query language in the early 90’s as Procedural  Language to SQL.

This enhanced the capabilities of SQL as PL/SQL allows mixing of SQL statements with procedural constructs. Unlike SQL, PL/SQL is a programming language extending SQL, which includes procedural elements like loops, data types, control structures and others. You can create program blocks, functions, methods, triggers and packages with this extension. In simple terms, while SQL tells the relational database what to do, PL/SQL tells them how to do.

Some important features of Oracle Pl/SQL are:

  • The syntax is based on ADA and Pascal programming language
  • sending the block statements at once reduce network traffic and increase performance
  • Features like exception handling, encapsulation, data hiding, etc., save time in debugging and designing.

In order to become a successful DBA, one should clearly know the difference between SQL and PL/SQL:

SQLPL/SQL
Structured Query LanguageProgramming language
Used to write single queryUsed to write complete programs
Source of data for preparing reportsLanguage for writing applications
Data-orientedProcedural oriented
Can be embedded within Pl/SQL codesCannot be embedded within SQL codes

Our Oracle PL/SQL tutorial teaches various PL/SQL programming fundamentals and makes you familiar with data types, control structures, collections and records. A comprehensive study of procedural language basics- this learning package is a realistic implementation of Pl/SQL operations, subprograms, and packages. You can learn to query data using implicit and explicit cursor for loops. The tutorial also covers Exception handling in relational databases to handle PL/SQL errors.

Further, deeper insights into PL/SQL object types and language elements are also provided with suitable examples and real-world implementations. Overall, the tutorial is designed to satiate the learning needs of all types of audiences, whether beginners, intermediate or advanced-level professionals. For advanced programmers and database administrators, our tutelage will serve as a conceptual revival and can be referred time and again to revise and polish up their PL/SQL concepts.

If you like this tutorial, you can opt for our comprehensive training course on Oracle PL/SQL, which offers more than 70% learning through hands-on exercises, assignments and project work, along with a course completion certificate from Intellipaat and preparation for related Professional Certifications. Besides, you will have a 24*7 dedicated support to assist you with any queries regarding training courses, tutorials and videos.

Recommended Audience

  • Software Professionals willing to learn PL/SQL programming
  • SQL Developers eager to enhance their database knowledge and reach the next career level in databases
  • Data Analysts and Data Scientists, working with large databases every day for analyzing, handling, storing and updating data and converting into meaningful information
  • Database Administrators and Architects
  • Entry-level and advanced programmers in Java, C++, Python

Wish to Learn Oracle PL SQL? Click Here

Prerequisites

  • Prior experience of using SQL language (whether from SQL Server, Oracle, Sybase, and MySQL) would be very useful.

Oracle PL/SQL Tutorial Video

Get Oracle PL SQL Certification in just 14 Hours

CLICK HERE

Free Ebook: Step by Step Guide to master PL/SQL

CLICK HERE

0 Thoughts to “Case Study Oracle Pl/Sql Tutorial

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *