Re: Using Cursors

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: peterDavey (peter.davey_at_austin.org.au)
Date: 01/24/05 

Date: Tue, 25 Jan 2005 09:47:46 +1100

Thanks to everybody for all the replies so far. It looks like there's a lot
of interest in this topic.

There's no doubt that in Oracle cursors are invaluable. I could indeed do
what I want using set based SQL but it would require 3 - 4 passes through
the data with separate SQL statments to acheive what I want. Using a
cursor, I select the data once, then execute a number of operations for each
record selected - In my Oracle procedures I've found technique this to be
extremely efficient. There are also other stored procedures that I need to
convert that I'm sure I couldn't do without a cursor.

The crux of this question however was not so much about the usefulness or
otherwise of cursors but simply how do I stop the 'feedback' I'm getting for
each record fetched from the cursor. Based on your replies however, I will
compare the performance of the set based SQL solution with the cursor
solution. I'll let you know what I find.

Below is an example of the 'feedback' I'm talking about:

ep_key_e campus_e ur_e
--------------- -------- ---------------
IP01803871 2 743600

(1 row(s) affected)

ep_key_e campus_e ur_e
--------------- -------- ---------------
IP01805187 2 KM3527

(1 row(s) affected)

I can get rid of the "(1 row(s) affected)" by using SET NOCOUNT ON (thanks
GreyAlien007) but the other stuff remains.

cheers
peterDavey

"peterDavey" <peter.davey@austin.org.au> wrote in message
news:eTNJlNgAFHA.3376@TK2MSFTNGP12.phx.gbl...
> G'day,
> I'm moving from Oracle top SQL Server and starting recode my Oracle stored
> procedures into T-SQL. I have a procedure in Oracle that opens a cursor,
> loops through each record and does an update or insert where appropriate.
>
> I've just created my first cursor in T-SQL. It runs OK in Query Analyser
> but the problem is that it in the output pane it displays a separate query
> output for each record returned by the cursor. I don't want to it to
output
> anything other than some summary stats afte it's completed. The code is
> below: I've excluded the SELECT statement because it's BIG.
>
> DECLARE curEpisode SCROLL CURSOR FOR
> SELECT
> some stuff ...
>
> OPEN curEpisode
>
> -- Perform the first fetch.
> FETCH NEXT FROM curEpisode
>
> -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
> WHILE (@@FETCH_STATUS <> -1)
> BEGIN
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM curEpisode
> --... do stuff ....
> SET @counter = @counter + 1
> END
>
> CLOSE curEpisode
> DEALLOCATE curEpisode
> SELECT @counter
> GO
>
> Thanks in advance for any help.
>
> cheers
> peterDavey
> Austin Health
> Melbourne
>
>

Relevant Pages

  • Re: Help: Is there any way I can catch the sql that calls the function from inside the function?
    ... > (l_data_input in varchar2) ... make your helpers' life easier by making sure the SQL is correct ... and telling us the Oracle version. ... Oracle doesn't have a concept of cursor parent and child in the sense ...
    (comp.databases.oracle.server)
  • Re: Performance problems with StoredProcedure in Web application
    ... you could probably just ignore my comments about stored procedure performance as compared to textual queries because they ... As for your question about how to avoid using a cursor (as I suggested was possible in Oracle) read the following paragraph for more ... The .NET Framework Data Provider for Oracle does not support batched SQL statements. ... CURSOR output parameters to fill a DataSet, ...
    (microsoft.public.dotnet.languages.csharp)
  • Using Cursors
    ... I'm moving from Oracle top SQL Server and starting recode my Oracle stored ... I've just created my first cursor in T-SQL. ... DECLARE curEpisode SCROLL CURSOR FOR ... -- Perform the first fetch. ...
    (microsoft.public.sqlserver.programming)
  • Re: Oracle CASE statement precompile error in Micro Focus
    ... where to put it (which doesn't make sense to me on the cursor declaration ... the level of the precompiler that you have) though I _don't_ know this. ... follow code logic versus sql logic. ... > We are converting our COBOL from DB2 to Oracle (also IBM z/OS to Micro ...
    (comp.lang.cobol)
  • Re: ORATCL help needed!
    ... set cursor ... Wow, An Oratcl 3.3. ... It is coded with the the OCI layer released with Oracle ... Oracle instant client is available for many many platforms, ...
    (comp.lang.tcl)