Results 1 to 4 of 4

Thread: Problem with open cursors.

  1. #1
    Join Date
    Jul 2005
    Posts
    35

    Unhappy Problem with open cursors.

    Hi all,I have translated this procedure from T-SQL to PL/SQL.As you see it is a mixed operation

    procedure,I mean if some condition is true it does a DML and if other condition is true it does a Query.
    Code:
    CREATE OR REPLACE PROCEDURE FaxSettings(
                    p_USERCODE  number,
                    p_HSENDER   VARCHAR2,
                    p_HTITLE    VARCHAR2,
                    p_DIALTONE  number,
                    p_PREFFIX   VARCHAR2,
                    p_WAIT      number,
                    p_DIALRETRY number,
                    p_READWRITE number,
                    p_MODEM     VARCHAR2,
                    p_FAXSERVER NUMBER,
                    p_result_cur OUT sys_refcursor)
    
    AS
    
      v_counter NUMBER := 0;
     
      BEGIN
    
      IF p_READWRITE = 1 then
        
          SELECT COUNT(*)
          INTO v_counter
          FROM   UserAProFaxSettings
          WHERE  Code = p_USERCODE;
    
          IF v_counter = 0 then
        
              INSERT INTO UserAProFaxSettings (Code,Sender,Title,DialTone
                                              ,Preffix,DialWait,DialRetry,Modem,FaxServer)
              VALUES      (p_USERCODE,
                          p_HSENDER,
                          p_HTITLE,
                          p_DIALTONE,
                          p_PREFFIX,
                          p_WAIT,
                          p_DIALRETRY,
                          p_MODEM,
                          p_FAXSERVER);
                 
           ELSE 
           
              UPDATE UserAProFaxSettings
              SET    Sender = p_HSENDER,
                     Title = p_HTITLE,
                     DialTone = p_DIALTONE,
                     Preffix = p_PREFFIX,
                     DialWait = p_WAIT,
                     DialRetry = p_DIALRETRY,
                     Modem = p_MODEM,
                     FaxServer = p_FAXSERVER
              WHERE  Code = p_USERCODE;
              
            END IF;
          
       END IF;
    
      IF p_READWRITE = 2 THEN
    
      OPEN p_result_cur for
       
          SELECT *
          FROM   UserAProFaxSettings
          WHERE  Code = p_USERCODE;
          
      END IF;
    
     END FaxSettings;
    /
    The problem is: I don't know it will be a DML without cursor or a Query with a returned cursor to

    application.So I do not know if I should close that cursor in application or no?
    Any help will be so appreciated.

  2. #2
    Join Date
    Jul 2005
    Posts
    13
    checking your code, i think you are not utilizing PL/SQL correctly, i am refering to the Insert - Update

    SQL%NOTFOUND construct is very handy here

    ex

    UPDATE UserAProFaxSettings
    SET Sender = p_HSENDER,
    Title = p_HTITLE,
    DialTone = p_DIALTONE,
    Preffix = p_PREFFIX,
    DialWait = p_WAIT,
    DialRetry = p_DIALRETRY,
    Modem = p_MODEM,
    FaxServer = p_FAXSERVER
    WHERE Code = p_USERCODE;
    IF SQL%NOTFOUND Then -- this means that no recrods updated
    INSERT INTO UserAProFaxSettings (Code,Sender,Title,DialTone
    ,Preffix,DialWait,DialRetry,Modem,FaxServer)
    VALUES (p_USERCODE,
    p_HSENDER,
    p_HTITLE,
    p_DIALTONE,
    p_PREFFIX,
    p_WAIT,
    p_DIALRETRY,
    p_MODEM,
    p_FAXSERVER);
    End if;

    I did not clearly understand your Cursor worries. Leaving the cursor open does not mean it will stay open indefinetly, it will remain in the Shared Pool Area until it is aged out. Closing the cursor indicates that you no longer need it and therefore,, will be flushed immediately. normally i keep my cursor open unless i am certain i no longer need them, I think this way i can improve shared pool hit ratio ,,, wallaho a3lam

    Ammar Sajdi

    ammar.sajdi@realsoft-me.com

    I have a simple OLD PL/SQL training material in my web site

    www.e-ammar.com in the download page, it could be helpful

    Ammar Sajdi

  3. #3
    Join Date
    Jul 2005
    Posts
    35

    Question

    Great point,But I have translated this code exactly from its
    T-SQL format and for some reasons we have not changed it at all.
    About cursors,IMHO your practice is not useful for high concurrent user environments.What if you reach open_cursors limit? It is true that oracle will close them but as you know this is an undocumented feature so we can not rely on.
    BTW,my problem is:In some situation That procedure will do insert or update that I have to return a dummy cursor for these situations,e.g. SELECT * FROM DUAL WHERE 1=2
    But the Delphi application that should close cursors does not know about this dummy returned cursor(It expects an insert or update without any returned thing) so such cursors will be open.
    I ask:Is it possible to close these cursors from within my pl/sql procedure?
    -Thanks

  4. #4
    Join Date
    Jul 2005
    Posts
    13
    to the best of my humble knowledge, you can explicitly close "explicitly declared" cusors, other than that, you can only only contol simultaneously opened cursors thorogh parameters

    ammar sajdi

Posting Permissions

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