CREATE Oracle Procedure

Question

I am trying to create a procedure and created without errors. However, when I try to run it, I get the following error. Please inform

SQL> begin
  2   Update_STUD_Fin ( '1011');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "ORAIN.UPDATE_STUD_FIN", line 3
ORA-06512: at "ORAIN.UPDATE_STUD_FIN", line 8
ORA-06512: at line 2

Procedure

SQL> CREATE OR REPLACE PROCEDURE Update_STUD_Fin ( AIDY_CODE IN VARCHAR2 ) IS
  2    CURSOR PublicationC IS
  3      SELECT SGidm from SGB
  4       WHERE SGCODE_EFF ='201030';
  5  BEGIN
  6    OPEN PublicationC;
  7  
  8    FOR PublicationR IN PublicationC
  9    LOOP
 10      DBMS_OUTPUT.PUT_LINE( PublicationR.SGidm );
 11    END LOOP;
 12  
 13    close PublicationC;
 14   
 15  END;
 16  /

Procedure created.

Answer 1

You cannot explicit the OPEN cursor, nor can you use it in an implicit FOR loop. You select either implicit (FOR loop) or explicitly (OPEN / FETCH / CLOSE).

Answer 2

If you use a cursor with FOR / IN / LOOP, you do not need to open it explicitly. Just write:

SQL> CREATE OR REPLACE PROCEDURE Update_STUD_Fin ( AIDY_CODE IN VARCHAR2 ) IS
  2    CURSOR PublicationC IS
  3      SELECT SGidm from SGB
  4       WHERE SGCODE_EFF ='201030';
  5  BEGIN
  8    FOR PublicationR IN PublicationC
  9    LOOP
 10      DBMS_OUTPUT.PUT_LINE( PublicationR.SGidm );
 11    END LOOP;
 12  
 15  END;
 16  /

Answer 3

6512 PL-SQL, . , ?

Answer 4

, ORA-06512 - . , ?

ORA-06512 , PL/SQL- . :

ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "USER.SOME_PROCEDURE", line 5
ORA-06512: at line 1

- ORA-01001. ORA-06512s , : 5 USER.SOME_PROCEDURE, 1 PL/SQL.

, :

• FOR ... IN some_cursor LOOP ... . FOR.

• You should also not try to close a cursor that has not been opened. If you try to do this, you will receive an “invalid cursor” error ORA-01001. I assume you turned this on to make sure the cursor was closed before you opened it, but unfortunately you cannot do this.

In short, you must remove all of the instructions OPENand CLOSEof their procedures, and then try again.