Oracle Procedures by examples

A procedure is a module that performs one or more actions; it does not need to return any values.

The syntax for creating a procedure is as follows:

CREATE OR REPLACE PROCEDURE name
[(parameter[, parameter, ...])]
AS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];

A procedure may have zero to many parameters.

Parameters are the means to pass values to and from the calling environment to the server. There are three types of modes for procedures:-

IN: data passed to the procedure.
OUT: data returned by the procedure.
INOUT: data passed to the procedure that is, during procedure execution, replaced by data to be returned from the procedure.

IN Mode Example

I simply created a procedure using IN mode that insert data into the table by using TOAD for Oracle.


Use F5 key to compile procedure. Now passing data from calling envirnment to the procedure.


Data insert successfully into the table by using procedure.

OUT Mode Example

This procedure is simply selecting data from table and pass the selected data to the calling environment.


Use F5 key to compile procedure. Now passing data from the procedure to the calling envirnment.


INOUT Mode Example




Calling procedure by using Oracle 10g Form Developer

I create a simple form for data insertion and i am calling my stored procedure from the form. For this purpose i create a Save Button on the form and put the procedure calling code on button's WHEN_BUTTON_PRESSED trigger.


Comments

Post a Comment

Popular posts from this blog

What is Oracle Integration Cloud Service - ICS?

Image
Oracle Integration Cloud Service (ICS) is a solution that enables you to simplify integrations between cloud applications, and between cloud and on premises applications. It helps you create connections to well-known and less-known SaaS and PaaS applications, using the available cloud adapters, publish or subscribe to the Messaging Cloud Service, or use industry standards such as SOAP, REST, FTP, File, and JMS. For more information watch this Oracle video.
Read more

How to Create Packages in Oracle Database using TOAD for Oracle

Image
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.
Read more

How to create a Simple Scheduler Job in Oracle Database using Toad

Image
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;
Read more