Found this presentation from 2005 OOW: </code> http://download-east.oracle.com/oowsf2005/644.pdf <code>

It won't turn you into a JAVA programmer over night but it will answer some of those basic questions every PL/SQL developer ask when they start using JAVA.

Best Regards,
Andries Hanekom

As someone who has used PL/SQL for 12 years and who decided to learn Java a couple of years ago, it sounds like we have some similarities.

If it's JDBC you are interested in, then "Expert Oracle JDBC Programming" by RM Menon, is a good read. For pure Java and a good grasp of the language, then I would recommend "Learning Java" by Niemeyer and Knudsen.

As always the standard documentation on both the Oracle and Sun sites is immensely helpful, both for JDBC / SQLJ and for Java itself.

Hope this may have been of some help.

it gives you a good idea of how PL/SQL and Java work together. It is especially useful if you are planning to use Java inside the Oracle database. It was originally written for 9i but should apply to 10G in most cases.

</code> http://www.amazon.com/gp/product/1861006020/102-8508482-2167356?v=glance&n=283155 <code>

Kashif

If you want / need an introduction to OO theory (essential to properly understand Java), I would recommend the classic work "Object Oriented Analysis and Design with Applications", by Grady Booch.

This book is now a few years old and the examples are in C++. It remains however, a superb primer in understanding OO techniques.

Pleased be warned: it is pretty dry and stodgy reading.

The document http://download-east.oracle.com/oowsf2005/644.pdf referred above states that PL/SQL CANNOT "Cannot subclass a procedure or package"

Confused here, we can call a procedure from another procedure and the same with packages. Correct? or I'm I missing something here.

Except for interacting with the os file system, what is the use of using java inside Oracle database? Do we have the exampleof reading the os file system using a java stored procedure on this website( tried searching for it)?

Am an Oracle developer for over a decade, found the following book a good primer, not something that will make you an expert, but will introduce you to the key concepts and keep your interest:

http://books.google.co.uk/books?q=head+first+java

Tom

I hear Java developers repeatedly boasting that orjecting oriented programming especially inheritance is only possible in Java and not PL/SQL.

But hasnt PL/SQL been enhanced since 9i rel1 for object oriented programming.

Can you please demo an OOPS example using PL/SQL.

Please, please correct if I'm wrong but there is no encapsulation when you work with Oracle Objects. There is inheritance and polymorphism but no encapsulation. You can't define private members and private fields and you can't turn off the default constructor.

Packages provide encapsulation but Oracle object types...?

Objects views could have been a great tool but Oracle doesn't really promote it. All the developers who code the middle tier with Java or C# spent much of their time (50%?)doing object relational mapping. They could have used object views.

But if you use ODP.NET (the 'tool' that provides the connection between a .Net program and Oracle) you can't use object views, well you can but the ODP.NET release that supports Oracle objects is dated 27-dec-2007. So it is possible but only for less than a month.

It would be handy if Oracle objects would also provide encapsulation.

I think good ODP.NET support for object views could have made my job easier and support for mdsys.sdo_geometry in combination with ODP.NET is also a very important topic.

I like working with .NET objects and interfaces, properties, inheritance, polymorphism and especially generics.

Good support for Oracle Types makes it also possible to do more business logic with PL/SQL. In my middle tier I have a .NET object, the question is how to get that object in the database? And the next question is how to get my relational data in my middle tier? I think that object views can answer the second question.

For me OO is more than the cats meow, I have made an OO example with PL/SQL:


set echo on
set serveroutput on size 100000

drop table big;

create table big as select object_name from all_objects where 1=0;

drop type action1;

drop type action2;

drop type actionbase;


create or replace type actionbase as object
( action_description varchar2(200)
, action_type varchar2(1)
, not instantiable member procedure do_bulk
, member procedure exec
)
not final
not instantiable
/


create or replace type body actionbase as

member procedure exec
is
begin
dbms_output.put_line('**');
dbms_output.put_line(action_description||':');

do_bulk;

dbms_output.put_line(to_char(sql%rowcount)||' rows '||case action_type
when 'D' then ' deleted '
when 'I' then ' inserted '
when 'U' then ' updated '
end);
dbms_output.put_line('**');
commit;

exception
when others then
--do some logging
raise;
end;


end;
/



create or replace type action1 under actionbase
(
constructor function action1 return self as result
, overriding member procedure do_bulk
)
/


create or replace type body action1
is

constructor function action1 return self as result
is
begin
action_description := 'Insert rows into big from table all_objects';
action_type := 'I';
return;
end;


overriding member procedure do_bulk
is
begin

insert into big
select object_name
from all_objects;

end;

end;
/



create or replace type action2 under actionbase
(
constructor function action2 return self as result
, overriding member procedure do_bulk
)
/


create or replace type body action2
is

constructor function action2 return self as result
is
begin
action_description := 'Delete rows that start with a C';
action_type := 'D';
return;
end;


overriding member procedure do_bulk
is
begin

delete big
where object_name like 'C%';

end;

end;
/

You can test this with:

declare
l_actionbase actionbase;
begin
l_actionbase := action1;
l_actionbase.exec;
l_actionbase := action2;
l_actionbase.exec;
end;
/

It outputs:

**
Insert rows into big from table all_objects:
41692 rows inserted
**
**
Delete rows that start with a C:
608 rows deleted
**

I like this because there is a separation between presentation (dbms_output), transaction and exception handling (member actionbase.exec) and the members action1.do_bulk and action2.do_bulk who do the actual work (the working horses).

The test block can be changed into:

declare
l_action1 action1 := action1();
l_action2 action2 := action2();
begin
l_action1.exec;
l_action2.exec;
end;
/

I think my approach is very modular and it is maintenable.

If you create an action3 and you forget to override member do_bulk the compiler will raise an error. If you create an instance of actionbase the compiler will raise an error too because actionbase is not instantiable, it won't compile.

If you want to change the commiting, the logging, the exception handling or the interaction with the user you only have to change member actionbase.exec.

procedure p ( l_obj in actionbase )
is
begin
  l_obj.exec;  <<<=== what the HECK did I just do, what code did i actually call
end

I understand that you should not commit between fire_employee and hire_employee, the client should commit.

My example is not for one or two record operations, but for bulk actions (maybe some kind of ETL operation?). Who should do the commit in a large nightly ETL operation?

But my example is not about the commit, it is about OO. I don't consider it a large obfuscated monolithic code base. There is actually a separation, one member for logging and exception handling, the others members do the actual work.

But I will contemplate your remarks.

This is the typical discussion between OO developers and procedural developers.

To a procedural developer, what is important in the comparison is that procedural code is straightforward and simple, and the learning curve is lower. To an OO developer, what is important in the comparison is that with OO code, even though there is a steeper learning curve (for both the language/technology and also the system being developed), once you get past that learning curve, modifications are easier, and you can add/change a lot of functionality with just one or two small changes in the code.

Both perspectives are legitimate. No matter what tool you're using, you can be very productive if you know it well. Tom obviously knows a procedural approach very well, and some developers know an OO approach well.

Another consideration: you have to consider the person who comes behind you to maintain your code 2 years from now. If you're using technology that very few people know (such as OO PL/SQL), then it may be hard for your team to find someone to replace you. However, if you're a Java developer, working with OO Java, then you're going to be much easier to find a replacement for.

It is true that 'I' have to consider the person who comes behind me to maintain 'my' code.

But this also creates a chicken egg situation. If no one knows OO PL/SQL no one can use OO PL/SQL, and if no one uses OO PL/SQL no one can know OO PL/SQL so no one will use OO PL/SQL.

I like both, I like PL/SQL and I like C# (very OO).

If the controller of the ETL is written in PL/SQL and you can't do a commit in PL/SQL, how can you commit?

But well provide your nice packaged solution.

I know that there is no real difference between functions, methods, subroutines, procedures and members. It is just a name. In Delphi they are called procedures and functions and Delphi is OO.