Home » SQL & PL/SQL » SQL & PL/SQL » few clarifications (oracle 10g,windows)
few clarifications [message #312205] Tue, 08 April 2008 03:42 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi All,

I have few doubts

1) which is better(by considering performance and other things) to find the no.of rows effected/returned by a cursor.


For e.g., I am using a procedure P1 in that cursor c1 is used .

witout using %rowcount
MEthod1:
Producer P1 is
cursor c1 is select * from test_tbl;
REC_COUNT number default 0;
Begin 
       FOR REC IN C1 Loop
       some statements;
       REC_COUNT := REC_COUNT + 1;
      end loop;
if REC_COUNT=0 then
exception
statements.....
END;

MEthod2:
Producer P1 is
cursor c1 is select * from test_tbl;
REC_COUNT number default 0;
Begin 
       FOR REC IN C1 Loop
       some statements;
       REC_COUNT := REC_COUNT%rowcount;
      end loop;
if REC_COUNT=0 then
exception
statements.....
END;

which is the Best Method 1 or 2 ?

2)I have a Procedure P2 which returns 3 ref cursors.

Method-3:
Current,logic for Procedure P2 out parameters is 
P2 calls P3 procedure which returns 1 Out parametr
P2 calls P4 procedure which returns 2 Out parametr
P2 calls P5 procedure which returns 3 Out parametr

Now,consider
Method 4:
i am able to send 3 out parameters  through only one procedure call P3 by opening 3 different cursor.

So,which is the best method 3 or 4 and why?

Thanks in Advance..

[Updated on: Tue, 08 April 2008 03:44]

Report message to a moderator

Re: few clarifications [message #312212 is a reply to message #312205] Tue, 08 April 2008 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question 1: dumping the files and counting the rows
Question 2: Calling 10 to 20 procedures that some returns a ref cursor and others no (if possible randomly)?

Regards
Michel
Re: few clarifications [message #312214 is a reply to message #312205] Tue, 08 April 2008 03:54 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Did not get any one of these answers.
Please ellaborate.

Thanks,
Re: few clarifications [message #312216 is a reply to message #312214] Tue, 08 April 2008 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did I forget the tongue in cheek smiley?

Regards
Michel
Re: few clarifications [message #312219 is a reply to message #312205] Tue, 08 April 2008 04:19 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Got it Michel,
Your saying in a derogatery way that means
MEthod2,Method 4 are the best ways.

Even I, thought the same but would like to check/verify with my answers.

Reasons: Method1 : every time it performs some arthmetic operations.so,for millions of records this can be be a performance degrador.

Method3: Every time P2 calls the P3,P4,P5.
so,A procedure call is required every time .This may also degrade the performance.

Whether My understanding is correct?


Re: few clarifications [message #312234 is a reply to message #312219] Tue, 08 April 2008 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Neither Method 1 or 2 is good, you have to use BULK COLLECT and then increment your count when you want.

Second question can't have a general answer. Procedure defines a logical unit of work. You have one or 3 depends on the features you have to code.

Regards
Michel
Re: few clarifications [message #312250 is a reply to message #312205] Tue, 08 April 2008 05:11 Go to previous message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks Michel,

For Question1:

But,we can find the no.of rows fetched/returned by using sql%rocount.
so,in case of for few records(say,less than 100).which is the best way use the explit counter to find the no.of rows effectd or by using the %ROWCOUNT?




Previous Topic: SOS PL/SQL missing
Next Topic: Converting long procedure with repeated code into package
Goto Forum:
  


Current Time: Sat Dec 03 20:08:06 CST 2016

Total time taken to generate the page: 0.09097 seconds