CODEWITHSUNDEEP
functionplsqloracle11gpackage
Lady in Green
Lady in Green

Reputation: 1

PL/SQL : How to run the sql statements stored within a column of the table to get the execution time of the sql statement?

I have some difficulty in writing my pl sql script. This is the scenario:

I have Table A which have 2 Columns namely Column 1(varchar), Column 2(varchar) as shown below.

Table A

       Column 1              Column 2
Row 1   a               select * from table_one
Row 2   b               select * from table_two 
Row 3   c               select * from table_three

I have created a package: pg_creating_package which contains a main function func_loop, which will eventually loop in each row of the table and execute each sql stored in Column 2, one by one and insert the time taken in the final table B for example:

Table B

              Start time               End time       Time Taken
Row 1   27/04/2014 12:33:44   27/04/2014 12:35:44       2mins  
Row 2   27/04/2014 12:33:44   27/04/2014 12:33:45       1sec

and so on...

My questions are :
1. How will i run the sql statements stored within that column?
2. How will i get the start time of the sql statement?
3. How will i get the ending time of the sql statement?

Upvotes: 0

Views: 149

Answers (1)

a1ex07
a1ex07

Reputation: 37382

You can do EXECUTE IMMEDIATE 'your select statement' or DBMS_SQL.EXECUTE . To record execution time, get time before and after EXECUTE IMMEDIATE. 

DECLARE   
start_time TIMESTAMP;  
end_time TIMESTAMP;  
BEGIN  
 ....
 start_time := SYSTIMESTAMP;  
 EXECUTE IMMEDIATE '....';  
 end_time := SYSTIMESTAMP;  
 .....  
END;

You may want to use DBMS_UTILITY.GET_TIME instead of datetime/timestamp.

Upvotes: 1

Related Questions