Home » SQL & PL/SQL » SQL & PL/SQL » Performance on Object type collection
Performance on Object type collection [message #188363] Fri, 18 August 2006 04:05 Go to next message
Aju
Messages: 94
Registered: October 2004
Member
We need to return a collection object to Java. Will there be any difference in performance on the folllowing scenario.

1--> if the object type is created in the database itself
2--> if the object type is created in the package

What is the mechanism of Oracle when accessing on both the scenario.

Thanks
Re: Performance on Object type collection [message #188369 is a reply to message #188363] Fri, 18 August 2006 04:20 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
Are there any problem with test? It will take a couple of hours (maximum).
I guess it will be a minor difference (if any).
Re: Performance on Object type collection [message #188384 is a reply to message #188363] Fri, 18 August 2006 05:27 Go to previous messageGo to next message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Hi Anju,
The thing is why you want to create the objects inside the package at all.Because these are one time job .And the chances are that ,suppose you run the package once so the objects get created and these objects are also used by some other packages,
now sometime later you again run the package ,at that time if somebody changes the specification of the objects then the other packages who will be using the objects will might have error when they are run.So that is a risk :
2--> if the object type is created in the package

But if
1--> if the object type is created in the database itself.
You can restrict the modifications of the types.In java you can accept the values passed in the objects in the XML format.
You can populate the objects by first taking the data in a cursor and then from the cursor you can populate the objects

Regards
Jaydeep Mitra
PH: 91-9440499806

Re: Performance on Object type collection [message #188389 is a reply to message #188384] Fri, 18 August 2006 06:00 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
jaydeep mitra wrote on Fri, 18 August 2006 14:27

Hi Anju,
The thing is why you want to create the objects inside the package at all.Because these are one time job .And the chances are that ,suppose you run the package once so the objects get created and these objects are also used by some other packages,
now sometime later you again run the package ,at that time if somebody changes the specification of the objects then the other packages who will be using the objects will might have error when they are run.So that is a risk :
2--> if the object type is created in the package

...

Regards
Jaydeep Mitra
PH: 91-9440499806



Nonsense.
Package recompilation in running production is not acceptable and must be strongly prohibited.
Re: Performance on Object type collection [message #188390 is a reply to message #188384] Fri, 18 August 2006 06:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
jaydeep mitra wrote on Fri, 18 August 2006 12:27

Hi Anju,
The thing is why you want to create the objects inside the package at all.Because these are one time job .



I think the OP meant created in the package, not BY the package. So either SQL-types or PL/SQL-types.
Re: Performance on Object type collection [message #188407 is a reply to message #188363] Fri, 18 August 2006 07:31 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
Oh my God...
I completely forgot that you have no choice: you must use the first option cause the second is not implemented by Oracle, i.e. you are not able to return PL/SQL collection into Java.
Re: Performance on Object type collection [message #188847 is a reply to message #188407] Tue, 22 August 2006 02:02 Go to previous message
Aju
Messages: 94
Registered: October 2004
Member
Sorry for the delay in response. Here is the detail for clarification.

Create TYPE t_ary AS VARRAY(4) OF VARCHAR2(255)

Now I initialized in the code as

v_op t_ary := t_ary ();
v_op.EXTEND (1);
v_op (v_op.COUNT) := 'XXXXX';
.................
.................

Then this v_op is returned to the Java. I guess this can be returned both in the package level as well as in the database level. Orafaquer cannot this v_op be returned to Java. Im not a Java guy, Cannot test on this.

In fact I am least bothered about the perfomance whether t_ary is decleread in the package itself or in the database level.

Just keen to know how does oracle process if the object is declared in database level and how if it is at package level.

Thanks all for you time.

Here is the sample code.

CREATE OR REPLACE PACKAGE test_pkg
AS
type t_ary is VARRAY(4) of VARCHAR2(255);
function f_ret_arr RETURN t_ary;

END test_pkg;

CREATE OR REPLACE PACKAGE BODY test_pkg AS
function f_ret_arr RETURN t_ary
AS
v_op t_ary := t_ary ();
BEGIN
v_op.EXTEND (1);
v_op (v_op.COUNT) := 'XXXX';

v_op.EXTEND (1);
v_op (v_op.COUNT) := 'YYYY';

RETURN v_op;
END f_ret_arr;

END test_pkg;


Previous Topic: ORA-32162: Read/Write SQL method not registered
Next Topic: How to update CLOB column having more than 4000 chars
Goto Forum:
  


Current Time: Fri Dec 02 12:24:11 CST 2016

Total time taken to generate the page: 0.10895 seconds