Proc to run a long select statement

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

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply