Reputation: 1862
I want to write a function in PL/SQL, which has a sql-statement as parameter. The statement can be DDL,DML and DCL
I want to print the time from the executed statement in the console.
I have following code:
create or replace procedure measureTime (statement IN varchar2 )AS
--declarations
neededTime INTEGER;
BEGIN
dbms_output.enable;
EXECUTE IMMEDIATE statement
COMMIT;
SELECT ELAPSED_TIME AS neededTime FROM V_$SQL WHERE SQL_TEXT=statement
dbms_output.put_line('executeTime '|| neededTime);
END measureTime;
But it doesn't work. What's wrong?
Upvotes: 0
Views: 274
Reputation: 52923
There's a simpler way to do it; simply take the start time before you start and take that off the current time when you've finished.
create or replace procedure measureTime (statement IN varchar2 )AS
l_start_time timestamp default systimestamp;
BEGIN
dbms_output.enable;
EXECUTE IMMEDIATE statement;
COMMIT;
dbms_output.put_line('executeTime '|| systimestamp - l_start_time);
END measureTime;
Alternatively if you want it in seconds say change systimestamp - l_start_time
to trunc((systimestamp - l_start_time) * 24 * 60 * 60)
This does seem like an over-worked way to go about things. execute immediate
will be slightly slower as Oracle has to validate the query before it can run each time. Why are you not just executing the query?
The commit
will also commit everything that has happened in that session, which may not be what you want.
You should also ensure that you have escaped your statement
properly to avoid SQL injection.
Your procedure wouldn't have compiled as you needed a semi-colon after your execute immediate
. This should not be included in the parameter statement
.
Your syntax for your select is also incorrect. As this is PL/SQL and not SQL you need to use the into clause and you were missing a semi-colon at the end of this statement as well.
select elapsed_time
into neededTime
from v$sql
where sql_text = statement;
Even without syntax errors there are several reasons your statement might not work for instance, sql_text
in v$sql
only has the first 1,000 characters, so if statement
is more than 1k characters long there would be no match. If you have a group by
clause, according to the documentation, statistics are not stored.
Upvotes: 5