Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    1

    Unanswered: Oraacle Cursors.

    My experiance with cursors is mainly with MS SQL Server and Sybase Sql Server. This goes back to Versions 4.x . Back then we as developers where always steered away from utilizing cursors. The strategy was always, if you have process some rows that process should alwyas occur on the client end.

    I seem to recall the reason was perfomance and bad things happen to the server if the process goes bad on the DBA would probably have to bounce the server.

    Do these reason still hold true with Oracle? Is it always best to process cursors on the Client side?

    Thanks!

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    those reasons NEVER held true for oracle. oracle has always promoted using database code (explicit or implicit cursors, and just straight sql) on the database. having the client call a stored procedure will result in less network traffic than having the client loop over a cursor while performing client work.

    and since all connections to oracle are two-task mode, the cursor on the client still results in a server process doing the work.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Jan 2004
    Posts
    492
    If you are going to use PL/SQL to incorporate programming logic, you will most likely have to use a cursor.

    In PL/SQL, if you are going to fetch more than one row back from a query, you must use a cursor.

    EX: (1 row)
    Code:
    select name
    into v_name
    from emp
    where empno = '8334'
    You expect to pull back one row, so you must select the result into a variable.

    EX2 (Multiple rows in cursor)

    DECLARE

    cursor c_test is
    select ename, empno
    from emp
    where deptno = 10;

    BEGIN
    for r_test in c_test loop
    dbms_output.put_line(r_test.ename || ' has empno '||r_test.empno);
    end loop;

    end;

    A for loop implicitly opens and closes the cursor, so you dont need to do yourself. Hope that helps - read up on the PL/SQL reference guide for more info
    Oracle OCPI (Certified Practicing Idiot)

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    shoblock, I wonder if the same helds true now that we have BULKS operation within either, the client (being Developer suite) and the engine. Where all my ETL can be on both. I would like to know your oppinion about it.

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    A lot depends on the client.

    SqlForms has it's own pl/sql engine, so when you run an anonymous pl/sql block on the client (declare...begin...end), the block is run locally, and only the individual sql statements are passed to the server (an "if" statement runs on the client, the "update" runs on the server). So, the client is doing a lot of context switches between pl/sql and sql, and performing a lot of network traffic (an update within a loop will send/receive across the network for each update).

    A java app will pass the entire pl/sql block from above to the server, and then receive one packet back for the answer. But you don't typically write java (or asp, .net...) that way. You use client code for the loops, if statements, and then call sql statements as needed. Doing it this way, it works similar to sqlforms - the java "if" runs on the client, the "update" is passed to the server.

    Any client calling stored pl/sql will make one network send/receive. It's the same as issuing an update statment - it must be run on the server.

    bulk processing has many benefits (regardless of client or server side). First, when you issue a select, the results are returned to memory (pga) immediately. Every fetch sends the address of the return variables, and the pointer of the next row in mapped. When you use bulk processing (works the same in pl/sql as it has always worked in pro*c), the address of the array is passed, and the pointer to the next row (first row on first fetch) is mapped. However, this mapping includes the "N" rows after it, so less work is done to pass addresses, move pointers in the pga and return results. Also, less context switches are made.

    A good programming guideline is keep as much client-related processing as possible on the client, and put the server processing on the server (in stored pl/sql). Then try to structure your client code to call the server code as little as possible - rather than calling the server within a loop, call it once using arrays (whether passing an array to a stored procedure, or using a bulk fetch, it's the same concept). If you have a large pl/sql block on the client, which is making client calls (e.g., next_record in Forms) and making server calls (e.g., updates), the entire block cannot be passed to the server (the server doesn't know about your screen), so you generate more individual server calls, each with network overhead (as a programmer, the network overhead is something you'll likely have zero controll over). But if you write the client code to loop over your screen records, putting the needed info into an array, and then make one server call, passing the array, you've now made less server calls, and less context switches, and less network traffic.

    For as long as array processing has been available (pro*c in the old days, now pl/sql also), performing one array insert of 500 rows is at least 4 times faster than performing 500 inserts of one row each. So, use arrays and put large, complex database operations (which don't require client processing) into stored pl/sql.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Great explanation. Appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •