By Denio Flavio Garcia da Silva
After more than 15 years working with PL/SQL, I still managed to find a feature that genuinely surprised me.
I ran into it while preparing for the PL/SQL certification exam (1z0-149), which was easily the hardest Oracle certification I have taken so far. I passed with 66.2%, just above the 66% passing score, so yes, it was very close. But in the end, the preparation was absolutely worth it.
What made that journey valuable was not just the exam. It was the fact that it pushed me back into the official Oracle documentation. I did not take an Oracle course. I did not use third-party training. I relied on the experience I already had and spent a lot of time reading and rereading the Oracle Database PL/SQL Language Reference.
That is where I stopped on something I had never really explored: object types with methods.
For a long time, I saw TYPE mostly as structure. Something useful to define shape, group attributes, and model data. But the more carefully I read the documentation, the more I realized Oracle treats object types as more than passive containers. They can hold data, but they can also define behavior.
That was the part that made me stop and think.
Because if you have spent years building PL/SQL solutions the way many of us do, most logic naturally ends up in packages. That is still true for me. I am not writing this post to argue that object types should replace packages.
What changed for me was simpler than that.
I started to see object types as a legitimate way to keep certain kinds of logic close to the data they belong to.
A small example that changed the way I looked at TYPE
Let’s start with something simple.
Imagine an order item. In a lot of PL/SQL codebases, we would just store product name, quantity, and unit price, then calculate the total somewhere else.
With an object type, we can define both the attributes and the behavior.
create or replace type order_item_t as object (
product_name varchar2(100),
quantity number,
unit_price number(10,2),
member function total return number
);
/Now the implementation:
create or replace type body order_item_t as
member function total return number is
begin
return nvl(self.quantity, 0) * nvl(self.unit_price, 0);
end total;
end;
/Let´s execute on VSCode:
And a simple block to use it:
declare
l_item order_item_t;
begin
l_item := order_item_t('Keyboard', 2, 150);
dbms_output.put_line('Product: ' || l_item.product_name);
dbms_output.put_line('Total: ' || l_item.total());
end;
/The output is:
What I like about this example is that it is very easy to read.
The object stores data, but it also knows how to calculate something about itself. The logic is right next to the attributes it depends on. That is what made the feature click for me.
Until then, I had mostly seen TYPE as structure. Here, it started to feel more like a small, self-contained model.
Member methods feel more natural than they sound
When I first saw methods inside a type definition, it felt a little strange.
Not because the syntax was hard, but because it was not something I had really used in everyday enterprise PL/SQL work. In real systems, packages are still where most orchestration, validations, and business rules tend to live.
But member methods started to make sense as soon as I looked at them through a practical lens.
An order item calculating its own total is not some academic object-oriented example. It is just a case where the behavior clearly belongs to the data structure itself.
That is the kind of scenario where this feature begins to feel natural.
Then I found the second interesting part: static methods
Once I understood the basic idea of a member method, the next thing that caught my attention was that Oracle also supports STATIC methods.
That opens another possibility.
A member method is tied to an instance. A static method is tied to the type itself. In practical terms, that means a static method can be useful when you want the type to define how certain objects should be created.
For example:
create or replace type order_item_t as object (
product_name varchar2(100),
quantity number,
unit_price number(10,2),
member function total return number,
static function service_item (
p_name varchar2,
p_unit_price number
) return order_item_t
);
/And the body:
create or replace type body order_item_t as
member function total return number is
begin
return nvl(self.quantity, 0) * nvl(self.unit_price, 0);
end total;
static function service_item (
p_name varchar2,
p_unit_price number
) return order_item_t is
begin
return order_item_t(p_name, 1, p_unit_price);
end service_item;
end;
/Usage:
declare
l_item order_item_t;
begin
l_item := order_item_t.service_item('Installation', 300);
dbms_output.put_line('Product: ' || l_item.product_name);
dbms_output.put_line('Total: ' || l_item.total());
end;
/The output is quite simple:
This was another moment where I had to pause and rethink what I assumed TYPE was meant for.
Because now it was not just a structure with a calculation attached to it. It could also expose controlled creation logic.
Again, not something I would force everywhere. But definitely more powerful than I used to think.
And then came the part I found even more interesting
At that point, the topic was already interesting enough on its own.
But then I remembered something else from the documentation: these object types are not limited to anonymous blocks or in-memory examples. They can also be used in database structures.
That is where the subject really started to stand out to me.
For example:
create table sales_demo (
id number,
item order_item_t
);
/You can insert an instance of the type:
insert into sales_demo
values (
1,
order_item_t('Keyboard', 2, 150)
);
/
And then call the method from the stored object:
select t.item.total()
from sales_demo t;
/And the magic is done:
That does not mean the table itself has methods.
The type has methods.
The table stores instances of that type, and those instances carry both structure and behavior. That distinction is important, and honestly, it is one of the reasons I find the feature so interesting. It is not magic. It is just a richer model than many of us are used to associating with PL/SQL object types.
Does this replace packages? Not at all
I think it is important to say this clearly.
I do not see object type methods as a replacement for packages. In the kind of systems I work on, packages are still the natural home for most business logic, integration flows, validation chains, and reusable APIs.
But I also do not see object types the same way anymore.
Now I see them as another tool in the language. A more specific one, yes, but still useful.
When the logic is tightly connected to the structure itself, keeping that behavior inside the type can make the code easier to read and easier to reason about.
That was the real takeaway for me.
Not that I should rewrite everything using object types.
Just that I had underestimated what Oracle made possible with them.
Final thoughts
One of the best things about studying seriously for a certification is that it forces you to revisit parts of the language you may have ignored for years.
That is exactly what happened to me here.
After more than 15 years working with PL/SQL, I found a feature that I had technically seen before, but had never really stopped to understand. And it was the process of reading the documentation carefully, under the pressure of a difficult exam, that finally made me look at it properly.
The exam was hard. The pass was narrow. But the preparation paid off.
For a long time, I saw object types mostly as structure.
Now I see that Oracle designed them for more than that.
They can carry attributes, define behavior, and even participate directly in persisted database models.
I discovered that late.
But definitely not too late to appreciate it.
Deixe um comentário