Close cursor in stored procedure in oracle implicitly or explicitly in Oracle 10?

Question

Iam use procedure stored in Oracle 10.2, i have one question, in this version, the Cursor close implicitly or emplicitly? Or close sinse Java, for example:

sql = "{ call procedureName (?,?)}";
    PreparedStatement st = getCon().prepareCall(sql);
    .
    .
    .
    st.execute();
    .
    .
    .
    st.close();

And where to close the cursor in the stored procedure?

Thanks!

Add stored procedure.

CREATE OR REPLACE PROCEDURE OWNER.STOREDPROCEDURE(
    V_OPTION IN NUMBER,
    V_STA IN OWNER.TABLENAME.ATTRIBUTENAME%TYPE,
    V_DESC IN OWNER.TABLENAME.ATTRIBUTENAME%TYPE,
    V_CURSOR OUT SYS_REFCURSOR,
    Error_Code OUT NUMBER,
    Error_Msg OUT VARCHAR2)

    IS
    V_ST_LN NUMBER:= LENGTH(V_STA);
    V_DSC_LN NUMBER:= LENGTH(V_DESC);
    BEGIN
        IF V_ST_LN >0 AND V_DSC_LN >0 THEN
            IF V_STA IS NOT NULL  AND V_OPTION IS NOT NULL THEN
                CASE V_OPTION
                    WHEN 1 THEN
                        OPEN V_CURSOR FOR
                            SELECT *FROM OWNER.TABLENAME SB WHERE SB.STA = V_STA;
                            Error_Code:=0;
                            Error_Msg:='DONE';
                    WHEN 2 THEN
                        IF  V_DESC IS NOT NULL THEN
                            OPEN V_CURSOR FOR
                                SELECT *FROM OWNER.TABLENAME SB WHERE SB.STA = V_STA AND SB.DESC != V_DESC;
                                Error_Code:=0;
                                Error_Msg:='DONE';
                        ELSE
                                Error_Code:=1;
                                Error_Msg:='NO CONDITIONS TO BE EXECUTED STOREDPROCEDURENAME';
                        END IF;
                END CASE;
                ELSE
                    Error_Code:=1;
                    Error_Msg:='NO CONDITIONS TO BE EXECUTED STOREDPROCEDURENAME';
            END IF;
        ELSE
             Error_Code:=1;
              Error_Msg:='NO CONDITIONS TO BE EXECUTED STOREDPROCEDURENAME';
          END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            Error_Code:=1;
            Error_Msg:='DONE, NO DATA FOUND';
            RAISE_APPLICATION_ERROR (-20100, 'NO DATA FOUND  STOREDPROCEDURENAME(' || SQLCODE || ' ' || SQLERRM || ')');
         WHEN OTHERS THEN
            Error_Code:=4;
            Error_Msg:=SQLCODE || ' ' || SQLERRM;
            RAISE_APPLICATION_ERROR ( 20100, 'DON'T EXECUTE STOREDPROCEDURENAME (' || SQLCODE || ' ' || SQLERRM || ')');
END STOREDPROCEDURENAME;
/
GRANT EXECUTE ON OWNER.STOREDPROCEDURE TO USER;

Where to close my CURSOR here?

Do you mean how to close the CURSOR you have inside the Oracle stored procedure? You need to do it within the procedure. If you mean the JDBC Statement, you should use try-with-resources for closing that. — Mick Mnemonic

QuickSort QuickSort · Accepted Answer · 2017-02-10T20:59:28

just to make the cursor-concept clear:

Implicit Cursor:

for SQL-statements, which only have one result. eg:

Update ... Set .. WHERE id=47

DB-Server handles that.

Excplicit Cursor:

set-oriented queries, which result can consists of more than 1 row.
declared explicitly in procedure.

The opening and closing of Cursors happens normally in the procedure.

When using the Cursor-For-Loop, the opening and closing of the Cursor will be done automatically.

Close cursor in stored procedure in oracle implicitly or explicitly in Oracle 10?

1 Answers