0

I had the following code. When I compile my function, SQL Developer said all is OK, but when I execute my DECLARE, BEGIN, END block, SQL Developer said the cursor is already open, but I close it into my function? Anyone can help me please?

CREATE OR REPLACE TYPE courriel AS TABLE OF VARCHAR2 (100);
/
CREATE OR REPLACE FUNCTION listCourriel
  RETURN courriel
AS
  listeCourriel   courriel := courriel();
    vSuffixeCourriel varchar2(10);
    vPrenom varchar2(255);
    vNom varchar2(255);
    CURSOR c_emp IS SELECT first_name, last_name FROM s_emp;
BEGIN
        vSuffixeCourriel := '@summit.ca';
        OPEN c_emp;

    FOR v_emp IN c_emp LOOP
        vPrenom := v_emp.first_name;
        vNom := lower(v_emp.last_name);
        listeCourriel.EXTEND;
        listeCourriel(listeCourriel.LAST) := vPrenom || vNom || vSuffixeCourriel;
    END LOOP;
    CLOSE c_emp;
  RETURN listeCourriel;
END;
/

DECLARE
    listeCourriel courriel := courriel();
    unNom VARCHAR2(100);
BEGIN
    listeCourriel := listCourriel();
    FOR i IN 1..listeCourriel.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(listeCourriel(i));
    END LOOP;
END;

Tancks in advance! :)

SurveyVisual
  • 27
  • 1
  • 8
  • 2
    This is the problem `FOR v_emp IN c_emp LOOP`. You open the cursor manually with `open c_emp` and then cursor `FOR` loop is trying to open it, again. If you decide to use cursor `FOR` loop, then there is no need to open the cursor manually, cursor `FOR` loop will do it for you. I'll also close it automatically, so there is no need for manual cursor closing either in this case. – Nick Krasnov Oct 11 '17 at 14:20
  • Tancks Nick Krasnov for this explanations!! I remove my OPEN and CLOSE cursor, and all work fine now! Tancks for your help! :) – SurveyVisual Oct 11 '17 at 14:25

0 Answers0