-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|