We have the following statement executed in the database by the outside-of-the-database application:
"SELECT COUNT(*) INTO :num FROM TABLE1"
I'd like to be able to execute this statatement at will via Dynamic SQL to preserve the same sql_id.
We are deploying SQL baselines and ability to run any statement and check whether v$sql.sql_plan_baseline column is populated, is a plus. We don't have any issues using Dynamic SQL to execute any other "regular" statement, but is it even feasible to execute the above statement with the "INTO :num" clause in PL/SQL?
I'd like to be able to execute this statatement at will via Dynamic SQL to preserve the same sql_id. I don't understand. If you're using dynamic SQL, that implies the text of the query changes. And if the text changes, you'll have a different SQL ID!
If you're not changing the statement, why do you need dynamic SQL?
Anyhow, if you want to get the values from dynamic SQL in PL/SQL you can return the values from execute immediate with something like this:
var c number;
begin
execute immediate 'select count(*) from dual' into :c;
end;
/
print :c
C
----------
1