dbms_sql.return_result

[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:

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]

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]

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]

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.

[MaximG]

We're planning to release all our DAC products for RAD Studio 10.4 Sydney next week

[jdorlon]

Excellent! Thank you.

[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.