PL/SQL Declaring cursors with Parameters

Posted on 2003-03-18
Medium Priority
Last Modified: 2012-06-27
Informational question

I have a contractor coding a parameterized cursor like I've never seen before. Their claim is this how they teach it in class.  That's their blanket response, but they can't explain why this is done or the performance implications.  I would like to expand my knowledge why it works and the performance implications.

Example of their coding:
     v_value NUMBER;

     CURSOR curs1 IS
     SELECT col1, col2
       FROM tab1
      WHERE col1 = v_value;

     FOR v_cnt IN 1..100 LOOP
         v_value := v_cnt;
         OPEN curs1;
         …../* some fetching, some processing, closing cursor */

     END LOOP;

The way I have seen it coded and was taught.


    CURSOR curs1(p1 number)IS
    SELECT col1, col2
      FROM tab1
     WHERE col1 = p1

    LOOP v_cnt IN 1..100 LOOP
        OPEN curs1(v_cnt);
         …../* some fetching, some processing, closing cursor */


My gut says the contractor's method will be slower, since I believe the statement would need to be resolved every time it is called. Whereas, with a
parameterize cursor Oracle would create a substition variable and the statement will be in the buffer.

Actually, I was a bit surprised Oracle allowed it. Why?
Question by:hedjj01
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

by:Daniel Stanley
ID: 8162918
both examples are comparible in nature. it is questionable which would run faster; i doubt it would even be measurable since both are parameterized and just using different methods to do the binding.

good luck,

Accepted Solution

saxena_mohit earned 60 total points
ID: 8162944
Hi there,
  After looking at you example I would say both statement are same as far as Oracle parsing is concerns . there only statement in the buffer would be
SELECT col1, col2
     FROM tab1
    WHERE col1 = :a;

so it doesn't matter how you pass the variables.But your method will reduce the extra step of assigning the values everytime. but performancewise both are same but your's statement is good practice to have if you have long code.

Expert Comment

ID: 8165763

I too am a contractor and I have spent 3+ years contracting to Oracle. I agree with Mohit and my preference would be to go with your method as it's the cleanest and neatest solution.


Expert Comment

ID: 8167856
This method

  CURSOR curs1(p1 number)IS
   SELECT col1, col2
     FROM tab1
    WHERE col1 = p1;

promotes cursor reuse. For eg, If this cursor is going to be used in several procedures, you can declare it in one package and use it everywhere.

With the other method

    CURSOR curs1 IS
    SELECT col1, col2
      FROM tab1
     WHERE col1 = v_value;

It becomes very difficult to reuse because the cursor is tied down to the v_value in the procedure/package/function where it is declared.

Performance wise, both will be the same.



Author Comment

ID: 8168907

I ran a series of tests with the different methods.  A loop was constructed to iterate for 1,000,000 times the opening and closing of the cursor.  The time difference  between the 2 methods was micro seconds.  However, the method that did not pass a parameter was however slightly faster, but not appreciably.  

My conclusion is that there is no performance benefit to the cursor being declared without the parameter.  Further, based on the feedback received and my own experience; coding a cursor with a parameter improves readability and reuse.


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question