OraFAQ Forum: Server Administration » Problem with open cursors.

Home » RDBMS Server » Server Administration » Problem with open cursors.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with open cursors. [message #130483]

Sun, 31 July 2005 05:59

Achchan
Messages: 86
Registered: June 2005

Member

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.

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.

Report message to a moderator

Re: Problem with open cursors. [message #130499 is a reply to message #130483]

Sun, 31 July 2005 14:54

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

I don't think just closing it will be sufficient. Snce it is expecting a ref cursor as an out parameter, I believe you will need to return an empty result set, as demonstrated below.

scott@ORA92> CREATE TABLE UserAProFaxSettings
  2    (Code	  NUMBER,
  3  	Sender	  VARCHAR2(10),
  4  	Title	  VARCHAR2(10),
  5  	DialTone  NUMBER,
  6  	Preffix   VARCHAR2(10),
  7  	DialWait  NUMBER,
  8  	DialRetry NUMBER,
  9  	Modem	  VARCHAR2(10),
 10  	FaxServer NUMBER)
 11  /

Table created.

scott@ORA92> CREATE OR REPLACE PROCEDURE FaxSettings
  2    (p_USERCODE	 number,
  3  	p_HSENDER	 VARCHAR2,
  4  	p_HTITLE	 VARCHAR2,
  5  	p_DIALTONE	 number,
  6  	p_PREFFIX	 VARCHAR2,
  7  	p_WAIT		 number,
  8  	p_DIALRETRY	 number,
  9  	p_READWRITE	 number,
 10  	p_MODEM 	 VARCHAR2,
 11  	p_FAXSERVER	 NUMBER,
 12  	p_result_cur OUT sys_refcursor)
 13  AS
 14    v_counter	 NUMBER := 0;
 15  BEGIN
 16    IF p_READWRITE = 1 then
 17  	 SELECT COUNT(*)
 18  	 INTO	v_counter
 19  	 FROM	UserAProFaxSettings
 20  	 WHERE	Code = p_USERCODE;
 21  
 22  	 IF v_counter = 0 then
 23  	   INSERT INTO UserAProFaxSettings
 24  	     (Code,Sender,Title,DialTone,Preffix,DialWait,DialRetry,Modem,FaxServer)
 25  	   VALUES
 26  	     (p_USERCODE,
 27  	      p_HSENDER,
 28  	      p_HTITLE,
 29  	      p_DIALTONE,
 30  	      p_PREFFIX,
 31  	      p_WAIT,
 32  	      p_DIALRETRY,
 33  	      p_MODEM,
 34  	      p_FAXSERVER);
 35  	 ELSE
 36  	   UPDATE UserAProFaxSettings
 37  	   SET	  Sender    = p_HSENDER,
 38  		  Title     = p_HTITLE,
 39  		  DialTone  = p_DIALTONE,
 40  		  Preffix   = p_PREFFIX,
 41  		  DialWait  = p_WAIT,
 42  		  DialRetry = p_DIALRETRY,
 43  		  Modem     = p_MODEM,
 44  		  FaxServer = p_FAXSERVER
 45  	   WHERE  Code	    = p_USERCODE;
 46  	 END IF;
 47  	 OPEN p_result_cur FOR SELECT * FROM DUAL WHERE 1 = 2;
 48    ELSIF p_READWRITE = 2 THEN
 49  	 OPEN	p_result_cur for
 50  	 SELECT *
 51  	 FROM	UserAProFaxSettings
 52  	 WHERE	Code = p_USERCODE;
 53    END IF;
 54  END FaxSettings;
 55  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> EXECUTE FaxSettings (1, '1', '1', 1, '1', 1, 1, 1, '1', 1, :g_ref)

PL/SQL procedure successfully completed.


no rows selected

scott@ORA92> EXECUTE FaxSettings (1, '2', '2', 2, '2', 2, 2, 1, '2', 2, :g_ref)

PL/SQL procedure successfully completed.


no rows selected

scott@ORA92> EXECUTE FaxSettings (1, '3', '3', 3, '3', 3, 3, 2, '3', 3, :g_ref)

PL/SQL procedure successfully completed.


      CODE SENDER     TITLE        DIALTONE PREFFIX      DIALWAIT  DIALRETRY MODEM       FAXSERVER
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 2          2                   2 2                   2          2 2                   2

scott@ORA92> EXECUTE FaxSettings (4, '4', '4', 4, '4', 4, 4, 2, '4', 4, :g_ref)

PL/SQL procedure successfully completed.


no rows selected

Report message to a moderator

Previous Topic:	Table created under which datafile
Next Topic:	Dropped Table is not available in Recyclebin

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 23:27:54 CST 2025