2

Given a declared PLSQL Procedure

PROCEDURE GET_DISTINCT_TIMES(dtypes IN VARCHAR2, start IN NUMBER, end IN NUMBER
   distinct_times_cursor OUT SYS_REFCURSOR)

Where:

  • 'Dtypes' is a comma delimited string, with each value corresponding to a general type of data I want to retrieve. One data type may correspond to one or many different tables where distinct times need to be retrieved
  • 'Start' & 'End' are the two times I am querying between

I have written implementations that dynamically create the SQL using Unions to pull out only unique time values from each table. However, due to the dynamic nature of the 'Dtypes' argument, I am forced to generate this SQL in a loop control, and have not been able to figure a way to implement bind variables.

I believe that losing the benefit of bind variables may have caused the queries to take longer than necessary

So I am wondering, is there a way to either:

  • Associate bind variables with a dynamically created SQL string when the number of bind variables is completely variable with the arguments passed in to the procedure.
  • Take a different approach, where for each dtype - execute another stored procedure, or SQL Statement, store the recordset for each query, then merge the unique results and assign a cursor to the new results

Admittedly, I am quite new with PLSQL programming and haven't had the opportunity yet to grab a good reference/book on the language. Any comments, insights, or suggested readings would be appreciated too.

John
  • 1,037
  • 11
  • 19
  • why not pass in a table of varchar2 instead of comma separated list? Also, more of the procedure body would be helpful – tbone May 18 '11 at 17:02

2 Answers2

1

See this SO question. You can use the DBMS_SQL package to bind a varying number of bind variables.

One other possibility is to use a pipelined table function. For each dtype, you would execute a stored procedure or SQL statement and use PIPE ROW to output the results. If you still need the results in a ref cursor, you can do SELECT * FROM TABLE(pipelined_function()).

Community
  • 1
  • 1
Cheran Shunmugavel
  • 8,319
  • 1
  • 33
  • 40
  • Thank you for those links, I started yesterday attempting to implement the pipelined table method, but came across a solution that worked better for me from a friend. These links were informative though, I appreciate your input! – John May 18 '11 at 20:47
1

I asked around and one of my friends came up with a solution that I successfully implemented today.

Basically, I dynamically create the entire PLSQL block as a VARCHAR2 then assign the bind variables using EXECUTE IMMEDIATE. The benefit of this method provides is that I can now assign bind variables by name, instead of by position.

Here is an example of what I mean:

var rs_cursor refcursor

DECLARE
  start_tm CONSTANT PLS_INTEGER := 989452800;
  end_tm CONSTANT PLS_INTEGER := 989452820;
  plsql_block VARCHAR(1024);
BEGIN
  plsql_block := 'BEGIN ' ||
    'OPEN :rs_cursor FOR ' ||
    '  SELECT TIME ' ||
    '  FROM TBL_1 ' ||
    '  WHERE TIME BETWEEN :start_tm AND :end_tm ' ||
    '  UNION ' ||
    '  SELECT TIME ' ||
    '  FROM TBL_2 ' ||
    '  WHERE TIME BETWEEN :start_tm AND :end_tm ' ||
    '  ORDER BY TIME; ' ||
    'END;';
  EXECUTE IMMEDIATE plsql_block USING IN OUT :rs_cursor, IN start_tm, IN end_tm
END;
/

print rs_cursor

start_tm and end_tm are only needed to be passed in once, but apply to multiple binds within the PLSQL block

My actual implementation is quite different than this, but it is using that specific functionality of EXECUTE IMMEDIATE binding variables by name to allow me to dynamically create SQL blocks based on whatever data types are provided to the procedure.

John
  • 1,037
  • 11
  • 19