Home » SQL & PL/SQL » SQL & PL/SQL » Row prefetch attribute and Stored procedures refcursor
Row prefetch attribute and Stored procedures refcursor [message #309216] Wed, 26 March 2008 17:32 Go to next message
jayesh_nazre
Messages: 4
Registered: March 2008
Junior Member
When I set the prefetch value from the calling program like say (java or PHP, PL/SQL) etc and when I call a stored procedure which returns a refcursor output parameter can someone confirm for me whether this parameter setting takes into effect or not. I know prefetch setting takes into effect when we execute SELECT's but I was not sure or did not find the same been mentioned for stored procedures. Logically it should affect even ref cursors as the calling program (like java etc) will translate the same into application resultset's

Can some DBA gurus confirm the same that the row prefetch parameter when set via say a JDBC driver also takes effect when a ref cursor is returned by a stored procedure.
The default row pretech value is 10 but I need to set it to "20" as we are showing "20" rows per web page.
Re: Row prefetch attribute and Stored procedures refcursor [message #309310 is a reply to message #309216] Thu, 27 March 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prefetch is used when you FETCH from a cursor not when you execute/open it. So it does not matter where the cursor/ref cursor comes from.

Is there a question in your last sentence?

Regards
Michel
Re: Row prefetch attribute and Stored procedures refcursor [message #309365 is a reply to message #309310] Thu, 27 March 2008 04:02 Go to previous messageGo to next message
jayesh_nazre
Messages: 4
Registered: March 2008
Junior Member
Thanks Michel for responding
Yes the question is as follows

The stored procedure gets called by a java program which is using JDBC (thin) drivers to execute the procedure from the application server. Now by default when we execute a "SELECT" statement from a java code or for that matter any code written in any programming languauge (I assume that includes PL/SQL), oracle allows the calling program, java in this case to set whats called row prefetch attribute, this attribute by default is set to "10", so for each database server call requesting "one" row, the oracle driver actually returns 10 rows prefetched into the client buffer.

When you read the oracle documentation, off their web site or google for row prefetch you will get tones of articles that talk abour row prefetch and how it should be set with respect to "SELECT" always.

Now when the calling program (java in this case) calls a stored procedure that has refcursor as an output parameter, the oracle driver transaltes that call and returns a ResultSet object which points to the refcursor type in oracle.
In java when we say get me the first row by saying ResultSet.next(), the oracle thin driver inturn calls the refcursor I assume FETCH, when it does that, what I am asking is does the Oracle driver prefetch 10 rows from the database although the code is asking for one row, in other words does the prefetch attribute take effect when we execute stored procedures, the same way it does when we execute SELECT from the calling program.

To give a PL/SQL analogy say my PL/SQL code in in Oracle forms WHEN_BUTTON_PRESSED trigger, now oracle forms is running in the oracle application server and is talking to Oracle database server residing on another physical box via OCI drivers. Now when the PL/SQL code in Oracle forms calls a stored procedure, does the row prefetch attribute (which is a Oracle driver attribute not any application programming code attribute - so the driver here is OCI Oracle Driver), takes effect.


I hope I am clear, the reason I am trying to give a PL/SQL analogy is this is a SQL/ PLSQL forum and if I ask in Java context then I am pretty sure I will get remarks about ......"post it to the appropriate forum etc......


Re: Row prefetch attribute and Stored procedures refcursor [message #309367 is a reply to message #309365] Thu, 27 March 2008 04:19 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what I am asking is does the Oracle driver prefetch 10 rows from the database although the code is asking for one row, in other words does the prefetch attribute take effect when we execute stored procedures, the same way it does when we execute SELECT from the calling program.

If the fetch is done inside PL/SQL, PL/SQL default is in place (1 before 10g, 100 starting with 10g). In PL/SQL, the correct way is to use BULK FETCH.
If the fetch is done inside your program, then the prefetch you set (or the default value if not set) is applied.

Regards
Michel

Previous Topic: Finding middle Character
Next Topic: default tablespace of dba user
Goto Forum:
  


Current Time: Sun Dec 04 00:08:44 CST 2016

Total time taken to generate the page: 0.07180 seconds