StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is a bigger topic really, but this is the approach that I think is easiest to implement and works well. The trick is to use dynamic SQL, but implement it so that you always pass the same number of parameters (needed), AND you allow Oracle to short-circuit when you don't have a value for a parameter (what you are lacking in your current approach). For example:</p> <pre><code>set serveroutput on create or replace procedure test_param(p1 in number default null, p2 in varchar2 default null) as l_sql varchar2(4000); l_cur sys_refcursor; l_rec my_table%rowtype; l_ctr number := 0; begin l_sql := 'select * from my_table where 1=1'; if (p1 is not null) then l_sql := l_sql || ' and my_num_col = :p1'; else -- short circuit for optimizer (1=1) l_sql := l_sql || ' and (1=1 or :p1 is null)'; end if; if (p2 is not null) then l_sql := l_sql || ' and name like :p2'; else -- short circuit for optimizer (1=1) l_sql := l_sql || ' and (1=1 or :p2 is null)'; end if; -- show what the SQL query will be dbms_output.put_line(l_sql); -- note always have same param list (using) open l_cur for l_sql using p1,p2; -- could return this cursor (function), or simply print out first 10 rows here for testing loop l_ctr := l_ctr + 1; fetch l_cur into l_rec; exit when l_cur%notfound OR l_ctr > 10; dbms_output.put_line('Name is: ' || l_rec.name || ', Address is: ' || l_rec.address1); end loop; close l_cur; end; </code></pre> <p>To test, simply run it. For example:</p> <pre><code>set serveroutput on -- using 0 param exec test_param(); -- using 1 param exec test_param(123456789); -- using 2 params exec test_param(123456789, 'ABC%'); </code></pre> <p>On my system, the table used is over 100mm rows with an index on the number field and name field. Returns almost instantly. Also note that you may not want to do a select * if you don't need all columns, but I'm being a bit lazy and using %rowtype for this example.</p> <p>Hope that helps</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload