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've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply