When I try to execute procedure on any stored procedure in the latest GA version I get an error, and it works in the previous version, and I will be using R1 until fixed.
When I click OK I get error in 2 below
Can you share a code sample that reproduces it? Specifically the issue has to do with Toad's understanding of your RESULT parameter so your parameter declarations are what I'm most interested in, but a complete sample I can run locally would be best. Toad is unable to resolve it and is generating incorrect PL/SQL to execute the procedure. We can see on line 5 in your screenshot that the variable declaration is incorrect. Feel free to share here or email to me at michael.staszewski@quest.com.
Thanks,
Michael
Hi,
Like I wrote it happens for everything, and now when detailing the process I can see that R2 adds code that does not exists in R1 version, please see end of this message for code.
Here is a simple example on how to repeat the error.
CREATE TABLE t_test_example_toad ( idno NUMBER PRIMARY KEY, firstname VARCHAR2(50) );
INSERT INTO t_test_example_toad (idno, firstname) VALUES (1, 'Alice');
INSERT INTO t_test_example_toad (idno, firstname) VALUES (2, 'Bob');
commit;
CREATE OR REPLACE PROCEDURE p_getdata(
RESULT IN OUT SYS_REFCURSOR)
AS
BEGIN
OPEN RESULT
FOR
SELECT *
FROM t_test_example_toad;
END p_getdata;
/
Output options for both cases
Toad 2025 R1
2025 R1 result
Toad 2025 R2
2025 R2 result
Toad 2025 R1 code
DECLARE
-- Variable declarations
l_RESULT SYS_REFCURSOR;
PROCEDURE OUTPUT_LINE(a VARCHAR2)
AS
BEGIN
IF LENGTHB(a) <= 32767
THEN
SYS.DBMS_OUTPUT.put_line(a);
ELSE
SYS.DBMS_OUTPUT.put_line(SUBSTRB(a, 1, 32767));
END IF;
END;
BEGIN
-- Variable initializations
l_RESULT := NULL; -- Modify the code to initialize this parameter
-- Call
P_GETDATA(RESULT => l_RESULT);
-- DBMS_OUTPUT statements
-- l_RESULT: Unable to resolve fields
OUTPUT_LINE('');
-- Transaction control
COMMIT;
-- Output bind variables, do not modify
:1 := l_RESULT;
END;
Toad 2025 R2 code
DECLARE
-- Variable declarations
l_RESULT SYS_REFCURSOR;
l_RESULT_rec PL / SQL RECORD;
l_rows_output NUMBER;
PROCEDURE OUTPUT_LINE(a VARCHAR2)
AS
BEGIN
IF LENGTHB(a) <= 32767
THEN
SYS.DBMS_OUTPUT.put_line(a);
ELSE
SYS.DBMS_OUTPUT.put_line(SUBSTRB(a, 1, 32767));
END IF;
END;
BEGIN
-- Variable initializations
l_RESULT := NULL; -- Modify the code to initialize this parameter
-- Call
P_GETDATA(RESULT => l_RESULT);
-- DBMS_OUTPUT statements
OUTPUT_LINE('RESULT =');
IF l_RESULT%ISOPEN
THEN
l_rows_output := 0;
OUTPUT_LINE();
LOOP
FETCH l_RESULT INTO l_RESULT_rec;
EXIT WHEN l_RESULT%NOTFOUND;
OUTPUT_LINE();
l_rows_output := l_rows_output + 1;
IF FALSE
THEN
EXIT WHEN l_rows_output = 0;
END IF;
END LOOP;
ELSE
OUTPUT_LINE('Cursor RESULT is not open');
END IF;
OUTPUT_LINE('');
-- Transaction control
COMMIT;
-- Output bind variables, do not modify
:1 := l_RESULT;
END;
I just tried your example and it works fine for me. I see Alice and Bob.
Can you try it with Spool SQL turned on, so we can see the output?
Hej,
I have now done that in both R1 and R2 and uploaded the result.
Toad2025R1_SpoolSql.sql (7.5 KB)
Toad2025R2_SpoolSql.sql (23.5 KB)
I was hoping to see the ORA-06550 in the spool SQL output, but it's not there. Did the error happen when you collected the output?
Oh! I see the problem. Did you paste that in?
No, that is added by Toad, I just click the execute procedure without changing anything, and there are differences in the SQL generated between R1 & R2 as I included in my detailed comment above.
What version is your database? Can you zip and email your user files folder? Its location is within this folder shown in your Toad Options. michael.staszewski@quest.com
Hej,
Mail sent
Oracle version:
Thanks for the files. I can reproduce this on a 19 db. I'll log it.
There are a couple of things going on here. First, the anonymous block that Toad generates to kick off the execution is incorrect. That's a new issue for 2025 R2. Also, the anonymous block that's generated is as if this option is selected, but your screenshot shows that you have the one above it selected. If you check this option shown and then return your selection to the Fetch into memory option the anonymous block will generate correctly for that option. That final option has a problem on newer dbs.
1 Like
Perfect,
I checked Send to DBMS Output and then checked Fetch into memory and now it works as it should.
Thanks a lot, now I can continue to to use 2025 R2, as this functionality is a must have for me.
1 Like