Object-PL/SQL

Quality 0.50 · 0 views · Updated 2 months ago

title: "Object-PL/SQL" type: doc version: 1 created: 2026-02-28 author: "Wikipedia contributors" status: active scope: public tags: ["oracle-software", "sql", "object-oriented-programming"] topic_path: "technology/databases" source: "https://en.wikipedia.org/wiki/Object-PL/SQL" license: "CC BY-SA 4.0" wikipedia_page_id: 0 wikipedia_revision_id: 0

|concern = Probably fails WP:NOPAGE, definitely at least needs WP:TNT. The code examples are a copyvio to boot. The apparent subject of this article is object-oriented programming features in PL/SQL, and "Object-PL/SQL" is an invented name for this. In general we do not have articles about programming methodologies in specific languages; Wikipedia presents a language's features by summarizing them (e.g. Python syntax and semantics) and sometimes splitting out specific features with clear standalone notability into their own articles (e.g. C++ classes). Any information about OOP features can be summarized in PL/SQL; there is no useful information here that can be merged into the main article. The title "Object-PL/SQL" is nonsense and should not be a redirect. |timestamp = 20260128014604 |nom = Helpful Raccoon |help = off

Object-PL/SQL (Object-Procedural Language/Structured Query Language or simply O-PL/SQL) is a methodology of using the Oracle Corporation's procedural extension language for SQL and the Oracle relational database. The additional features from version 7 and other improvements, lead to one of the large-scale environment implementations of the object-oriented database paradigm.

Although PL/SQL's general syntax formerly used to resemble that of Ada or Pascal, there were many improvements that mainly include the Java embedding code and the object-oriented syntax inside the SQL.

The mixing and embedding of triggers and stored procedures was one of the breakthrough points up to support the use of PL/SQL in a OO paradigm. The inclusion in the SQL syntax of statements such as , and the implementation of the object type (like any OO language), completed the minimum requisites to a mapping approach in an extended SQL language without use of specific mapping software.

A confusing of ''objects''

There can be confusion of the notions of object of DBMS and of class object. This is very important as we live with both significances in one language. It's necessary to identify when the documentation refers to an object as one of the two definitions.

Database objects are concepts that refer to relational or sequential databases and persist being valid in new models. Tables, triggers, columns, indexes are examples of database objects, which are present in O-PL/SQL, but with the same meaning of the notion of Java objects, specifically an element of a set that has its existence beginning from an instantiation of a class.

The PL/SQL

Main article: PL/SQL

PL/SQL is the extended SQL language used by Oracle Database.

PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM Db2 (since version 9.7).

O-PL/SQL allows the definition of classes and instantiating these as objects, thus creating user-defined datatypes as writing constructors, beyond using Java in stored procedures and triggers.

Examples of uses of syntax of O-PL/SQL

Here is a small set of examples of O-PL/SQL syntax, extracted from the official documentation and other sources:

A simple example of object-oriented PL/SQL ::code[lang=plpgsql] create or replace type base_type as object ( a number, constructor function base_type return self as result, member function func return number, member procedure proc (n number) ) instantiable not final; / ::

Now, the type's implementation is created. The implementation defines how the type's functions, procedures and how explicit constructors behave: create or replace type body base_type as constructor function base_type return self as result is begin a:=0; return; end base_type;

member function func return number is begin return a; end func;

member procedure proc (n number) as begin a:=n; end proc; end; / We're ready to derive from base_type. The keyword for deriving is under. The derived type defines a new attribute (named: m) and overrides func. create or replace type deriv_type under base_type ( m number, overriding member function func return number ); / As is the case with base types, the overridden methods in the derived type must be implemented: ::code[lang=plpgsql] create or replace type body deriv_type as overriding member function func return number is begin return m*a; end; end; / ::

The created types can be instantiated and methods can be called: ::code[lang=plpgsql] declare b1 base_type :=base_type(); b2 base_type :=base_type(4); d1 deriv_type:=deriv_type(5,6); d2 deriv_type:=deriv_type(5,6); begin dbms_output.put_line(b1.func); dbms_output.put_line(b2.func);

d1.proc(4); dbms_output.put_line(d1.func); dbms_output.put_line(d2.func); end; / ::

Results 0 4 24 30 The created types have become real types and can be used in tables: ::code[lang=plpgsql] create table table_base ( b base_type ); declare base base_type := base_type(); deriv deriv_type:= deriv_type(8,9); begin insert into table_base values(base); insert into table_base values(deriv); end; / select t.b.func() from table_base t; ::

Results: 0 72 ::code[lang=plpgsql] select avg(t.b.func()) from table_base t; ::

Result: 36

Bibliography

References

External sources

  • Examples of O-Pl/SQL
  • Another example of stored procedure in Java embedded in Oracle Documentation: ORA2

This is not the place for links to each and every website that may be helpful to a PL/SQL programmer! Spam links WILL be removed. Thanks!

References

  1. Lassan, Alan R.. (13 June 2000). "Experiences with Object Oriented Development in PL/SQL". The danish National Center for IT Research.
  2. (2000). "Experiences with Object Oriented Development in PL/SQL".
  3. Cunningham, Lewis. "PL/SQL Features by Release". Burleson Consulting.
  4. "When Should you use Java Stored Procedures with an Oracle Database, what are the Drawbacks?". Stack Overflow.
  5. "Oracle's Object-Oriented Features". etutorial.org.
  6. Benett, 2002:144
  7. Bales, 2007:107-209
  8. Shubho, Al-Farooque. (8 November 2009). "Optimize Database Files and Apply Partitioning". The Code Project.
  9. "DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows". IBM.
  10. "Oracle Documentatio". Oracle.
  11. "Object Oriented Oracle, example 1". René Nyffenegger's collection of things on the web.

::callout[type=info title="Wikipedia Source"] This article was imported from Wikipedia and is available under the Creative Commons Attribution-ShareAlike 4.0 License. Content has been adapted to SurfDoc format. Original contributors can be found on the article history page. ::

oracle-softwaresqlobject-oriented-programming