Re: Problem with open cursors.

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 31 Jul 2005 11:31:31 -0700
Message-ID: <1122834691.034907.25290@o13g2000cwo.googlegroups.com>

I don't think just closing it will be sufficient. Since 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_at_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_at_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_at_ORA92> SHOW ERRORS
No errors.

scott_at_ORA92> VARIABLE g_ref REFCURSOR
scott_at_ORA92> SET AUTOPRINT ON
scott_at_ORA92> EXECUTE FaxSettings (1, '1', '1', 1, '1', 1, 1, 1, '1', 1,

:g_ref)

PL/SQL procedure successfully completed.

no rows selected

scott_at_ORA92> EXECUTE FaxSettings (1, '2', '2', 2, '2', 2, 2, 1, '2', 2,
:g_ref)

PL/SQL procedure successfully completed.

no rows selected

scott_at_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_at_ORA92> EXECUTE FaxSettings (4, '4', '4', 4, '4', 4, 4, 2, '4', 4,
:g_ref)

PL/SQL procedure successfully completed.

no rows selected Received on Sun Jul 31 2005 - 13:31:31 CDT