+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: How can I run a committed sql-statement (as a parameter) in a procedure and get the e (/showthread.php?tid=87529)
How can I run a committed sql-statement (as a parameter) in a procedure and get the e - leusiam - 04-30-2023
I'd want to build a PL/SQL function using a SQL-statement as a parameter. The statement may be DDL, DML, or DCL.
In the console, I want to print the time from the executed statement.
I've got the following code:
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;I saw an article that says to modify systimestamp - l_start_time to trunc((systimestamp - l_start_time) * 24 * 60 * 60) in seconds. But I'm looking forward to a more straightforward method.
However, it does not work. What's the problem?
RE: How can I run a committed sql-statement (as a parameter) in a procedure and get the e - PomarTonkaa - 05-04-2023
The issue with your PL/SQL code seems to be that you have not declared the variable l_start_time in your code, which is being used in the expression systimestamp - l_start_time.
To fix this error, you can declare the variable l_start_time and set its value to systimestamp before executing the SQL statement. Then, you can calculate the elapsed time by subtracting l_start_time from systimestamp and convert it to seconds using the EXTRACT function.