Page 1 of 1
dbms_sql.return_result
Posted: Tue 12 May 2020 17:16
by jdorlon
Hello,
I know that if a TSmartQuery contains sql like this
Code: Select all
DECLARE
dept_cur SYS_REFCURSOR;
BEGIN
OPEN dept_cur FOR SELECT * FROM Dept;
DBMS_SQL.RETURN_RESULT(dept_cur);
end;
Then I can use TSmartQuery.Open to see the data.
However, if the anonymous pl/sql block does not return data, such as this:
and I try to use TSmartQuery.Open with it, I will receive the error "Statement does not return rows". For these I must use .Execute or .ExecSQL.
My question is, does TSmartQuery have a property that I can look at before it is executed so that I can determine if I should use .Open or .ExecSQL? In my case. the SQL being executed is up to the end user.
Thanks
John
Re: dbms_sql.return_result
Posted: Tue 12 May 2020 20:15
by jdorlon
Another note on this. I can't even parse the sql looking for dbms_sql.return_result because it could be buried in a stored procedure that the user is calling, like this:
Code: Select all
begin
dbms_sql_return_result_emp;
end;
where dbms_sql_return_result_emp is really:
Code: Select all
CREATE OR REPLACE procedure dbms_sql_return_result_emp as
emp_cur SYS_REFCURSOR;
BEGIN
OPEN emp_cur FOR SELECT * FROM EMP;
DBMS_SQL.RETURN_RESULT(emp_cur);
end;
/
It would be very nice if there was just one command I could call that would work for either type of pl/sql block (with or without dbms_sql.return_result), as SQL*Plus does. Rows would be fetched if there are any, otherwise, just execute the pl/sql block as normal.
Re: dbms_sql.return_result
Posted: Wed 20 May 2020 19:14
by MaximG
Thank you for the information. We've fixed incorrect behavior of the Execute method for queries returning Implicit Result Sets. The fix will be included in the upcoming version of our components. As a temporary solution, we can send you the nightly build with the necessary changes -- you'll be able to run your queries using Execute.
Re: dbms_sql.return_result
Posted: Wed 20 May 2020 19:17
by jdorlon
That's excellent, thank you Maxim! How long do you approximate until the next version is officially released? If it's not too long I will just wait until then.
Re: dbms_sql.return_result
Posted: Fri 29 May 2020 14:31
by MaximG
We're planning to release all our DAC products for RAD Studio 10.4 Sydney next week
Re: dbms_sql.return_result
Posted: Fri 29 May 2020 15:20
by jdorlon
Excellent! Thank you.
Re: dbms_sql.return_result
Posted: Tue 02 Jun 2020 16:05
by MaximG
We are excited to let you know that we have released new versions of DAC products with support for RAD Studio 10.4.
They are available for download on our website.