×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Cursor Intro.

Cursor Intro.

Cursor Intro.

(OP)
I'm using Oracle 9i.
I'm creating a Db procedure that will retrieve data and I'm planning on using a cursor to loop through the returned data to insert into a table.

The only problem is that I'm a newbie to Cursors...my Db expertise is low.

So what I am asking is how to go about defining a cursor and so on...a better bet is to find a good FAQ on this topic.

Any help on cursors would be grateful...

RE: Cursor Intro.

The Oracle 9i forum forum759: Oracle: Oracle release - 9i will probably get you more assistance.  This forum is more generic than that.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: Cursor Intro.

Franky

There's no need to use a cursor to retrieve the data. Its a while since I touched Oracle stored procs and have long forgotten the exact syntax, but try something like:


CREATE PROCEDURE procname
  AS

INSERT INTO table2 (fielda, fieldb)
SELECT field1, field2
FROM sometable
WHERE field1 = 'X'

Be sure to change the query to your exact needs.
This stops you needing to use a cursor to insert each row individually.

John

RE: Cursor Intro.

(OP)
Thanks for all the info.
I think I do need a cursor as I need to process the data depending on the returned data.

Thanks to all.

RE: Cursor Intro.

I don;t know Oracle but in SQL server I would never use a cursor for that. I would use the CASe statement. But truly your best bet for help that will specifically address Oracle performance issues is inthe Oracle forums. I know a cursor is horrible for performance and should be avoided in SQL Server but don't know if Oracle handles them differnently.

Questions about posting. See FAQ183-874: Suggestions for Getting Quick and Appropriate Answers

RE: Cursor Intro.

Oracle does handle them differently.  Oracle is optomized for cursor based operations and processes them nicly.

Infact do to Oracle syntax differences cursors are ofter required where in SQL we would do a set based operation.

With Oracle if you do a SET based UPDATE in the back end the engine actualy does row by row updates as that's how they optomized the database engine.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close