CODEWITHSUNDEEP
oracle-databaseplsql
RoyNasr
RoyNasr

Reputation: 359

Declaring a cursor after begin in oracle forms 10g

I am writing the following code block :

select companyid into compid from person where signedin = 1;
cursor getpending is select serviceid from servnego where companyid = compid and confirmed = 0;
open getpending;
loop
    exit when getpending%notfound;
    fetch getpending into servid;
    select servname into s from service where id = servid;
    add_list_element('homeadmin1.listpendingservice', counter2,s,s);
    counter2 := counter2 + 1;
end loop;

This is a part of the code that is inside the begin/end. The cursor getpending depends on the value of the variable compid from the select statement right before it. When compiling I get an error on the cursor line : "Encountered the symbol getpending when expecting one of the following : ...", so there is an error on the declaration of the cursor in the begin part. How can I solve the issue? Any help will be appreciated.

Upvotes: 1

Views: 1426

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Whenever possible, I recommend using a cursor for-loop. Oracle automatically optimizes cursor for-loops by bulk-binding the fetches, which reduces the round-trips to the database and thus improves performance. Also, try to do as much work in a single SQL statement as you can:

FOR aRow IN (SELECT c.SERVNAME
               FROM SERVNEGO s
               INNER JOIN PERSON p
                 ON s.COMPANYID = p.COMPID
               INNER JOIN SERVICE c
                 ON c.ID = s.SERVICE_ID
               WHERE s.CONFIRMED = 0 AND
                     p.SIGNEDIN = 1)
LOOP
  add_list_element('homeadmin1.listpendingservice',
                   counter2, aRow.SERVNAME, aRow.SERVNAME);
  counter2 := counter2 + 1;
END LOOP;

This also helps get rid of unneeded temporary variables which contribute to problems related to values being stored in multiple places, which is never a good thing.

Best of luck.

Upvotes: 1

APC
APC

Reputation: 146239

A cursor is like any other variable, it needs to be declared in the declaration section. However, you can declare it with a parameter...

declare
    ....
    cursor getpending (p_compid number) is 
        select serviceid from servnego 
        where companyid = p_compid 
        and confirmed = 0;
declare
    select companyid into compid 
    from person 
    where signedin = 1;
    open getpending (compid );
    loop
        fetch getpending into servid;
        exit when getpending%notfound;
        select servname into s 
        from service 
        where id = servid;
        add_list_element('homeadmin1.listpendingservice', counter2,s,s);
        counter2 := counter2 + 1;
    end loop;
    close getpending;

Note that you need to test for cursor %notfound after you execute the fetch. Otherwise you will get an error when the cursor is exhausted. Also, remember to close the cursor after you exit the loop.

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You could combine the first query within the CURSOR. Also note that the exit when statement should come after the FETCH

 DECLARE
    CURSOR getpending IS
      SELECT serviceid
      FROM   servnego
      WHERE  companyid IN (SELECT companyid 
                            FROM   person
                           WHERE  signedin = 1)
             AND confirmed = 0;
--declare  s and counter2 here appropriately.
BEGIN
    OPEN getpending;

    LOOP
        FETCH getpending INTO servid;

        exit WHEN getpending%NOTFOUND;

        SELECT servname
        INTO   s
        FROM   service
        WHERE  id = servid;

        Add_list_element('homeadmin1.listpendingservice', counter2, s, s);

        counter2 := counter2 + 1;
    END LOOP;
END;

/

Upvotes: 1

Related Questions