I’m trying to test some select statements that have some lines longer than 4000 characters and I couldn’t get them to run in sqlplus so I built this proc to run a select statement that is stored in a CLOB and return the number of rows fetched and elapsed time in seconds.
CREATE OR REPLACE procedure runclob( sqlclob in clob, total_rows_fetched out NUMBER, elapsed_time_seconds out NUMBER) is clob_cursor INTEGER; rows_fetched INTEGER; before_date date; after_date date; BEGIN select sysdate into before_date from dual; clob_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (clob_cursor,sqlclob,DBMS_SQL.NATIVE); rows_fetched := DBMS_SQL.EXECUTE_AND_FETCH (clob_cursor); total_rows_fetched := rows_fetched; LOOP EXIT WHEN rows_fetched < 1; rows_fetched := DBMS_SQL.FETCH_ROWS (clob_cursor); total_rows_fetched := total_rows_fetched + rows_fetched; END LOOP; DBMS_SQL.CLOSE_CURSOR (clob_cursor); select sysdate into after_date from dual; elapsed_time_seconds := (after_date-before_date)*24*3600; END; /
The queries I’m working on are generated by OBIEE and I’m testing running them with two different optimizer statistics choices and I want to see which causes a group of queries to run faster. I will have a block of code that loops through a collection of select statements that I extracted from production and runs each one in my test database with the proc listed above.
– Bobby
