Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    6

    Question Unanswered: Stored proceedures.

    I have come across an interesting issue.

    In using a stored procedure, If the proceedure itself does not contain a "Commit" the procedure hold open cursors until they timeout.

    With this, there is no provision for rollbacks in a stored proceedure.

    Normally using ordinary connections, as soon as the connection is broken, the resource is released.

    Is there a way to get a stored proceedure to release resources without containing a Commit?

    I can't really allocate any more cursors than I have right now.

    I'm looking for a way around this.

    Any Ideas?

    Thanks
    -Will

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Stored proceedures.

    I don't quite follow. If you no longer need a cursor to be open, you should close it and the resources will be released. If you do need the cursor to be open, then you surely don't want the procedure to close it.

    What do you mean by "no provision for rollbacks in a stored procedure?"
    You certainly can issue a ROLLBACK statement if you need to. More usually, you just raise an exception and let the calling program do the rollback.

    Maybe your problem is that you are not explicitly closing package-level cursors when exceptions are raised, e.g.:

    PACKAGE p

    CURSOR c;

    PROCEDURE pr IS
    BEGIN
    OPEN c;
    ....
    CLOSE c;
    EXCEPTION
    WHEN ... THEN
    CLOSE c;
    RAISE;
    END pr;

    END p;

    Maybe I have missed the point totally. Perhaps you can give an example of a cursor and the conditions under which it is left open when it should not be.

  3. #3
    Join Date
    Oct 2002
    Posts
    6
    Should the proceedures specifically open and close the resources?

    here is an example of one of my proceedures:

    create or replace procedure spInsertEmail
    (
    addressid OUT NUMBER,
    ownerid IN NUMBER,
    ownertype IN VARCHAR2,
    isprimary IN NUMBER,
    address IN VARCHAR2,
    isconfirmed IN NUMBER,
    confcode IN VARCHAR,
    confattempts IN NUMBER
    )
    AS
    BEGIN
    SELECT SEQADDRESS.NEXTVAL INTO addressid FROM DUAL;

    INSERT INTO TBLEMAILADDRESS
    (NEMAILADDRESSID, NOWNERID, SOWNERTYPE, BISPRIMARY,
    SEMAILADDRESS, BISCONFIRMED, SCONFCODE, NCONFATTEMPTS, DDATEADDED)
    VALUES
    (addressid, ownerid, ownertype, isprimary, lower(address), isconfirmed, confcode, confattempts, SYSDATE);

    COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
    END spInsertEmail;

    From what I can see it doesn't specify the resource, and without the commit, the resource just hangs open.


    Thanks
    -Will

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •