dbms_sql.return_result

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jdorlon
Posts: 135
Joined: Fri 05 Jan 2007 22:07

dbms_sql.return_result

Post by jdorlon » Tue 12 May 2020 17:16

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:

Code: Select all

begin
  null;
end;
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

jdorlon
Posts: 135
Joined: Fri 05 Jan 2007 22:07

Re: dbms_sql.return_result

Post by jdorlon » Tue 12 May 2020 20:15

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.

MaximG
Devart Team
Posts: 1409
Joined: Mon 06 Jul 2015 11:34

Re: dbms_sql.return_result

Post by MaximG » Wed 20 May 2020 19:14

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.

jdorlon
Posts: 135
Joined: Fri 05 Jan 2007 22:07

Re: dbms_sql.return_result

Post by jdorlon » Wed 20 May 2020 19:17

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.

Post Reply