Replace SELECT INTO statement with a cursor ORACLE

Question

This is the query. How to replace SELECT INTO statement with a cursor?

I'm newbie in Oracle

Thanks for your help

SELECT CEQ_LISTE_TYPE_QUESTIONS.ID_LISTE_TYPE_QUESTION
        INTO vintIdListeTypeQuestion
        FROM CEQ_FORMULAIRES
        inner join CEQ_LISTE_TYPE_QUESTIONS
          on CEQ_LISTE_TYPE_QUESTIONS.ID_LISTE_TYPE_FORMULAIRE=CEQ_FORMULAIRES.ID_TYPE_FORMULAIRE 
             AND CEQ_LISTE_TYPE_QUESTIONS.WEBCODE='ITEM_BETA_LACTAMASE'
WHERE CEQ_FORMULAIRES.ID_FORMULAIRE=to_number(out_rec.ID_FORMULAIRE) 
and ceq_formulaires.date_inactive is null;

Usually people are looking to do the opposite. What is your requirement? — D'Arcy Rittich
– D'Arcy Rittich, Commented Jul 25, 2012 at 13:11
Because an error techonthenet.com/oracle/errors/ora01422.php — FrankSharp
– FrankSharp, Commented Jul 25, 2012 at 13:15
That means you should write your query to return 1 row. AND DON'T USE where rownum <= 1, because that will return a random row! — winkbrace
– winkbrace, Commented Jul 25, 2012 at 13:18
Error: ORA-01422: exact fetch returns more than requested number of rows Cause: You tried to execute a SELECT INTO statement and more than one row was returned. Action: The options to resolve this Oracle error are: Rewrite your SELECT INTO statement so that only one row is returned. Replace your SELECT INTO statement with a cursor. — FrankSharp
– FrankSharp, Commented Jul 25, 2012 at 13:18

winkbrace · Accepted Answer · 2012-07-26 08:38:47Z

4

The error tells you that the query returns more than 1 row, so you should determine which row you need. Here an example how to fetch the most recent row based on a date field I thought up in ceq_list_type_questions "some_date".

select max(q.id_liste_type_question) keep (dense_rank last order by q.some_date) into vintidlistetypequestion
from   ceq_formulaires f
join   ceq_liste_type_questions q on q.id_liste_type_formulaire = f.id_type_formulaire
where  f.id_formulaire = to_number(out_rec.id_formulaire) 
and    f.date_inactive is null
and    q.webcode = 'ITEM_BETA_LACTAMASE'

edited Jul 26, 2012 at 8:38

answered Jul 25, 2012 at 13:24

winkbrace

2,70126 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vincent Malgrat Over a year ago

You're mixing a windowing function with the FIRST modifier of an aggregate, this won't work. The windowing function (with OVER) will not aggregate and therefore would return the same number of rows as the original. The correct synthax of FIRST is MAX(...) KEEP (DENSE_RANK FIRST ORDER BY yyy)

winkbrace Over a year ago

Thanks, Vincent. I edited my answer. I was too lazy to first create the tables in order to test my query. whoops.

Tebbe · Accepted Answer · 2012-07-25 13:20:42Z

Well, if you want to process your multiple rows in a loop, it's as simple as

BEGIN
    FOR curs IN (SELECT     ceq_liste_type_questions.id_liste_type_question
                 FROM       ceq_formulaires
                 INNER JOIN ceq_liste_type_questions ON ceq_liste_type_questions.id_liste_type_formulaire=ceq_formulaires.id_type_formulaire 
                                                    AND ceq_liste_type_questions.webcode = 'ITEM_BETA_LACTAMASE'
                 WHERE      ceq_formulaires.id_formulaire = TO_NUMBER(out_rec.id_formulaire) 
                 AND        ceq_formulaires.date_inactive IS NULL)
    LOOP
        DBMS_OUTPUT.PUT_LINE(curs.id_liste_type_question);  -- do what you need to do
    END LOOP;
END;
/

But, as BazzPsychoNut mentions, if it's a requirement that your SQL return/operate on a single row, you'll need to modify your query to meet that requirement.

Collectives™ on Stack Overflow

Replace SELECT INTO statement with a cursor ORACLE

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related