Toad World® Forums

Double insert

Hi!

12.11.06/12.10.0.30

DROP TABLE tst#;

CREATE TABLE tst#
(
ID NUMBER
);

CREATE SEQUENCE tst#_seq;

CREATE OR REPLACE PROCEDURE proc_tst# (cur OUT SYS_REFCURSOR)
IS
BEGIN
INSERT INTO tst# (id)
VALUES (tst#_seq.NEXTVAL);

OPEN cur FOR SELECT tst#_seq.CURRVAL FROM DUAL;
END;

DELETE FROM tst# WHERE 1=1;

BEGIN
proc_tst#(:cur);
END;

SELECT * FROM tst#;

It’s all right with

DECLARE
cur sys_refcursor;
BEGIN
proc_tst#(cur);
END;

Do you have a question?

Calling with :cur inserts two rows, with cur - one. Why?

It’s a bug in Toad. We’re doing an OCI describe before running the query, to determine the ideal fetch size of the result set. This is causing the proc to be executed twice. It’s only going to happen on procs (or pl/sql blocks) with a cursor output param. As a workaround, go into Options -> Oracle -> General. On the right, where it says “OCI Array Buffer Size”, set it to Manual and 500.

I’ll post again when I have a solution.

This is fixed in the current 12.11 beta.