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.

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

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

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