CODEWITHSUNDEEP
oracle-databaseplsql
FrankSharp
FrankSharp

Reputation: 2632

Replace SELECT INTO statement with a cursor ORACLE

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;

Upvotes: 0

Views: 10848

Answers (2)

winkbrace
winkbrace

Reputation: 2711

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'

Upvotes: 4

Tebbe
Tebbe

Reputation: 1372

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.

Upvotes: 3

Related Questions