Home » SQL & PL/SQL » SQL & PL/SQL » Instead of using Cursor is there any other option.
Instead of using Cursor is there any other option. [message #288993] Wed, 19 December 2007 04:07 Go to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi All,

Good Day,
Instead of using CURSOR is there any other way.

Why because my requirment is we no need to use the CURSOR.


Can you please help me.


Thanx & Reds
Thangam.
Re: Instead of using Cursor is there any other option. [message #288994 is a reply to message #288993] Wed, 19 December 2007 04:12 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

Quote:

Why because my requirement is we no need to use the CURSOR


unless you tell us more about your requirement how can we know what you are trying to achieve.


regards,
Re: Instead of using Cursor is there any other option. [message #288995 is a reply to message #288993] Wed, 19 December 2007 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is always better to use a single SQL statement rather than a PL/SQL cursor loop.

Regards
Michel
Re: Instead of using Cursor is there any other option. [message #288999 is a reply to message #288994] Wed, 19 December 2007 04:35 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi,

My requirment is no need to use CURSOR instead of doing the cursor works i need to do all.

Sorry, Because my customer asking like that.

Let me know if you need any more details on this.

Thanx & Reds
Thangam.
Re: Instead of using Cursor is there any other option. [message #289000 is a reply to message #288999] Wed, 19 December 2007 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you want us the answer to this no-question?
So do it.

Regards
Michel
Re: Instead of using Cursor is there any other option. [message #289009 is a reply to message #288993] Wed, 19 December 2007 04:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
iamdurai wrote on Wed, 19 December 2007 11:07


Instead of using CURSOR is there any other way.

To do what?
Re: Instead of using Cursor is there any other option. [message #289014 is a reply to message #288993] Wed, 19 December 2007 05:00 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Row by row executions.

Like Bulk Collect and Varry i don't knowexectly this is the correct way.

Can you please help me.

[Updated on: Wed, 19 December 2007 05:03]

Report message to a moderator

Re: Instead of using Cursor is there any other option. [message #289024 is a reply to message #289014] Wed, 19 December 2007 05:15 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

still not able to get what exactly you want to know.why create a suspense?you as the db programmer can decide what to use?is it possible for you to give a test case.so that others might guide you.


regards,
Re: Instead of using Cursor is there any other option. [message #289039 is a reply to message #289014] Wed, 19 December 2007 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Can you please help me.

Yes, if you post a PRECISE problem.

Regards
Michel
Re: Instead of using Cursor is there any other option. [message #289040 is a reply to message #288999] Wed, 19 December 2007 05:59 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
iamdurai wrote on Wed, 19 December 2007 11:35

Because my customer asking like that.

You must be kidding! What does a customer know about Oracle programming?!? I'd say too much, but not enough at the same time.

When buying a chocolate, do you demand that producers should stir the mixture counterclockwise or you wouldn't buy it? I suppose not.

So, why don't you do your job, and (politely) ask the customer to stay out of it?

P.S. Unless a customer is, actually, your boss.
Re: Instead of using Cursor is there any other option. [message #289086 is a reply to message #288993] Wed, 19 December 2007 09:36 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
An overview of the business problem you are trying to solve may help us to point you in a direction.

Cursors are not necessarily a bad thing, but as Michel already stated, you should try to do as much as you can in SQL, realizing that there are times when PL/SQL is needed.

Re: Instead of using Cursor is there any other option. [message #289150 is a reply to message #289014] Wed, 19 December 2007 20:46 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
iamdurai wrote on Wed, 19 December 2007 22:00

Row by row executions.

Like Bulk Collect and Varry i don't knowexectly this is the correct way.

Can you please help me.


I think the OP is trying to avoid EXPLICIT cursors.

You can loop though a result set using an implicit cursor:
FOR empl IN (
    SELECT ename
    FROM   emp
) LOOP
    <... some statements ...>
END LOOP;


You can also use an Implicit Cursor with BULK COLLECT:
SELECT ename
BULK COLLECT INTO empl_tab
FROM   emp;

FOR i IN empl_tab.FIRST .. empl_tab.LAST LOOP
    <... some statements ...>
END LOOP;



But you must use an Explicit Cursor for BULK COLLECT with LIMIT:
DECLARE
    CURSOR empl_cur IS
    SELECT ename
    FROM   emp;

    TYPE empl_tab_typ IS TABLE OF EMPL_CUR%ROWTYPE;
    empl_tab EMPL_TAB_TYPE;
BEGIN
    OPEN empl_cur;
    LOOP
        FETCH empl_cur BULK COLLECT INTO empl_tab LIMIT 1000;
        EXIT WHEN empl_tab.COUNT = 0;

        FOR i IN empl_tab.FIRST .. empl_tab.LAST LOOP
            <... some statements ...>
        END LOOP;

        EXIT WHEN empl_cur%NOTFOUND;
    END LOOP;
    CLOSE empl_cur;
END;


Note that the 3rd way - using the LIMIT clause - is the most efficient and scalable. By avoiding explicit cursors you are throwing away one of the most powerful tools in your kit.

Ross Leishman
Re: Instead of using Cursor is there any other option. [message #289202 is a reply to message #288993] Thu, 20 December 2007 02:54 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi all,

Thanks for the time spend my query.

Actually i said customer means my Boss suggestions like that. sorry the Inconvenience.



I want to say special Thanks to Ross Leishman.

Thanx & Reds
Thangam
Re: Instead of using Cursor is there any other option. [message #289203 is a reply to message #288993] Thu, 20 December 2007 03:25 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Littlefoot wrote on Wed, 19 December 2007 17:29
Quote:

When buying a chocolate, do you demand that producers should stir the mixture counterclockwise or you wouldn't buy it? I suppose not.

P.S. Unless a customer is, actually, your boss.



Laughing
Re: Instead of using Cursor is there any other option. [message #289440 is a reply to message #288993] Fri, 21 December 2007 12:35 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Next time you ask a question, give more detail. You gave no information on what you wanted to query or insert or update. Your question would be like asking someone

"should I use blue or black"

Without telling us what the color was for, it is a worthless question.
Re: Instead of using Cursor is there any other option. [message #289450 is a reply to message #289440] Fri, 21 December 2007 14:37 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can't be wrong if you choose black.
Re: Instead of using Cursor is there any other option. [message #289456 is a reply to message #288993] Fri, 21 December 2007 16:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
making an assumption that you are looking at what might be thought of as: "performance ramifications of differing rowset management coding styles", I figure your need may be to understand the differences between the following PL/SQL and SQL coding strategies:

Explicit cursors and slow by slow processing
Bulk Binding
ForALL loops
Cursor For loops (especailly pre-10g vs. 10g and after) (plsql_optimize_level)
SQL ONLY Solutions


I would offer up the following as a place to start some research. This is an interesting place to start as it is a good teaser to get you interested in doing the research you need to do.

http://dba-oracle.com/oracle_news/2006_03_17_test_10g_logical_io_plsql_faster.htm

As you do your own research across the internet, pay attention to the following:

1) You will see many examples of each coding method. Note how the code created using each method is different when compared to the others. Some code is easier to read and maintain than other alternatives.

2) Note the different limitations of each mechanism. For example, FORALL allows only one statment and does not work across a dblink (at least this is what I remember).

3) note the performance characteristics of each programming technique. You will see a progression from bad to good with plain Jane Cursors and slow by slow at the bottom, and SQL ONLY solutions at the top.

Indeed, Tom Kyte of ASKTOMHOME fame has a post where in he actually shows several of these techniques one after the other. Most interesting reading and very to your point I think. I am sorry however that I cannot locate it.

Maybe someone else here can find it and post the link for us please?

Once you have learned more about these concepts, you will be in a good place to show your customer that you know what their concerns are and can accomodate their needs. Your goal should be to give them the confidence that you will create good code and that they thus do not need to micro-manage your development. Of course, effective communication between parties is the best way to work.

All this said, maybe I am off the mark, in which case, read this stuf anyway, it will be good for your future as an Oracle Developer.

Good luck, Kevin
Re: Instead of using Cursor is there any other option. [message #289496 is a reply to message #288993] Sat, 22 December 2007 09:36 Go to previous messageGo to next message
0551373
Messages: 6
Registered: December 2007
Junior Member
Did anyone Found out what the issue was? how can we make suggestions if we no not the issue? that has always been the problem with the DBA's and the Developers, jump to suggestions and never finding the real issue.
Re: Instead of using Cursor is there any other option. [message #289510 is a reply to message #289496] Sun, 23 December 2007 00:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Since the OP bothered to thank me, I think I stumbled on the solution; he did not want to use explicit cursors. It's reasonably intuitive, because most newbies will not be aware that a cursor gets created even when the CURSOR keyword is not used.

Most people don't log onto these forums to get good advice, they want their problem solved. That may not be the best approach, but that's the way it is.

Now this guy's problem is that he has been asked not to use (explicit) cursors. We can only guess as to the reason for this - I don't think the OP know why either. I doubt his boss does, really.

When the OP becomes the boss, he make his own arbitrary rules to torment his subordinates. Until then he has to tow the line. He's clearly a newbie, and the last thing he needs is to start an argument with his boss that he cannot argue on his own. I doubt he wants to martyr himself for our cause.

How can we help? Give him the answer (we think) he wants to hear, and add a footnote about how crazy it is so that others don't try it (at least not without being warned).

Ross Leishman
Re: Instead of using Cursor is there any other option. [message #291047 is a reply to message #288993] Wed, 02 January 2008 17:42 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
0551373, what is your objection. Do you think what we have told iamdurai something bad?

You might be wondering how Ross determined this was "reasonably intuitive". It is because he is has been there too (as have many of us). Certainly those posting here were guessing to some degree, but they were good guesses, borne from experience.

Also, let me ask you...

How does a Man better himself (or Woman for that matter)? The unknowing often seek the counsel of Sages. That is why they come here. To some degree we have a duty to read between the lines, and offer up knowledge and advice that goes beyond what the post is asking. After all, the OP is only one person of many who will eventually read these posts. It is this ability to reach many which among other facits, makes OraFaq so powerful and thus why anone posts an answer to a question in the first place.

Good luck, Kevin
Previous Topic: ONLINE REDEFINITION
Next Topic: Rollback a committed trans
Goto Forum:
  


Current Time: Sun Dec 04 02:37:24 CST 2016

Total time taken to generate the page: 0.12539 seconds