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](http://stackoverflow.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc) for closing that. — Mick Mnemonic, Feb 10 '17 at 19:50
Possible duplicate of [How to close refcursor from java](http://stackoverflow.com/questions/5511278/how-to-close-refcursor-from-java) — Mick Mnemonic, Feb 10 '17 at 23:24

score 0 · Answer 1 · answered Feb 10 '17 at 20:59

0

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.

answered Feb 10 '17 at 20:59

QuickSort

494
5
14

https://community.oracle.com/thread/888365 pls read the first chapter. it'll take only a few minutes. Thank me later ;) – QuickSort Feb 10 '17 at 23:23

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

1 Answers1