8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Misc » Here

Comments for Implicit vs. Explicit Cursors in Oracle PL/SQL


Lalit Kumar B said...

Hi Tim,

In the cursor for loop demo, as per the code:

" DBMS_OUTPUT.put_line('Implicit Loop 2: ' ||
(DBMS_UTILITY.get_time - l_start) || ' hsecs');

should be something like:

Explicit Loop: 311 hsecs
Implicit Loop 2: 75 hsecs

I guess it's a typo in the Implicit loop output. It says "Implicit Loop: 94 hsecs". Please verify and correct it.

Regards,
Lalit

Lalit Kumar B said...

Also, the performance of the Implicit loop varies significantly depending whether it was executed through SQL*Plus or through any GUI based tool like SQL Developer.

For example,
SQL*Plus:

Explicit Loop: 150 hsecs
Implicit Loop: 75 hsecs

SQL Developer:

anonymous block completed
Explicit Loop: 182 hsecs
Implicit Loop: 178 hsecs

Tim... said...

Hi.

The " 2" should not have been these in the output text of the example code. I've corrected that, so the output is consistent with the code now.

Thanks for the heads-up.

Cheers

Tim...

Lalit Kumar B said...

Looks consistent now.

Anyway, do you think it would be better if you mention that these results are from SQL*Plus and not on any other tool. The difference would not be relatively same in that case.

May be a one liner, the test case are executed in SQL*Plus. I heavily use your demos as excerpts on various forums while answering. So don't mind me finding some typos or issues now and then.

Tim... said...

Hi.

All this code is running on the server, with a single result coming back to the client, so it should not be affected greatly by the tool being used. It is affected by the performance of the server.

I think you are confusing this with a select statement returning lots of rows to a client, where array size affects things.

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!