Possible to extend static SQL statements with dynamic parts?

I'd like to create a Oracle package where I have a procedure that executes some dynamic SQL. This is no problem if I'm doing it all dynamic with EXECUTE IMMEDIATE but it would be better if the static parts of the query could be coded static (to have compile time checking).

Example of fully dynamic query:

-- v_stmt is built dynamically.
v_stmt := 'SELECT count(*) FROM <here some joins> WHERE <here some conditions>';
EXECUTE IMMEDIATE v_stmt
  USING v_param1, v_param2
  RETURNING INTO v_count;

Example of what I tried to make the FROM-part static:

-- 开发者_JS百科v_stmt is built dynamically.
v_stmt := 'SELECT count(*) FROM my_package.my_function(:param1, :param2) WHERE <here some conditions>';
EXECUTE IMMEDIATE v_stmt
  USING v_param1, v_param2
  RETURNING INTO v_count;

FUNCTION my_function(
  i_param1 IN VARCHAR2,
  i_param2 IN NUMBER
)
RETURN SYS_REFCURSOR
AS
  v_cursor SYS_REFCURSOR;
BEGIN
  -- Open a cursor for different queries depending on params.
  IF i_param2 = 1 THEN
      OPEN v_cursor FOR <some static query>;
  ELSE
      OPEN v_cursor FOR <some other static query>;
  END IF;
  RETURN v_cursor;
END;

This doesn't work because it's not possible to select from a SYS_REFCURSOR (at least that's what I found with Google).

Is there any way to reach this goal?

edit: As requested, here are some examples:

Static queries:

SELECT a.*, ca.CUS_ID FROM adresses a INNER JOIN customer_adresses ca ON (ca.adr_id = a.adr_id);
SELECT p.*, cp.CUS_ID FROM persons p INNER JOIN customer_persons cp ON (cp.per_id = p.per_id);

Then they are extended dynamically like the following examples:

-- Checks if there is an adress in the customer where the zip is null.
SELECT count(*) FROM <static adresses query> q WHERE q.cus_id = :param1 AND a.zip IS NULL;
-- Checks if there is at least one person in the customer.
SELECT count(*) FROM <static persons query> q WHERE q.cus_id = :param1;

Sorry, but why the need to do this? Seems you're over complicating things by introducing a function that will return different types of data/tables depending on the parameter list. Very confusing imo. Besides, you have to do the work somewhere, you're just trying to hide it in this function (inside if param1=this then x if param1=that then y...)

Besides, even if you did implement a cursor function (even pipelined), it would be a bad idea in this case because you'd be forcing Oracle into doing work that it wouldn't necessarily need to do (ignore all the context switching for now). To just get a count, you'd have Oracle grab each an every row result and then count. Many times Oracle can just do a fast full index scan to get the count (depending on the query of course). And often same query run multiple times will not need to do all the work each time if blocks are found in buffer cache. I'd challenge you to run the count multiple times using straight SQL vs using a function returning a cursor. You might be surprised. And to my knowledge (check me on this) the new 11g function result cache won't work on a pipelined functions or a function returning a ref cursor (along with other issues like invalidations due to relies on tables).

So, what I'm saying is why not just do: select count(1) into v_variable from ...;

If you want to hide and modularize, then just know what you're potentially losing.

You may want to open a query in function1 and then pipeline the results of it as a table to function2 which then will add a where clause to this "table"

In this case you'll want to rewrite your function1 as a pipelined table function

v_stmt := 'SELECT count(*) FROM table(my_package.my_function(:param1, :param2)) WHERE <here some conditions>';
EXECUTE IMMEDIATE v_stmt
  USING v_param1, v_param2
  RETURNING INTO v_count;

CREATE TYPE object_row_type AS OBJECT (
  OWNER              VARCHAR2(30),
  OBJECT_TYPE        VARCHAR2(18),
  OBJECT_NAME        VARCHAR2(30),
  STATUS             VARCHAR2(7)
);


CREATE TYPE object_table_type AS TABLE OF object_row_type;


FUNCTION my_function(
  i_param1 IN VARCHAR2,
  i_param2 IN NUMBER
)
RETURN object_table_type PIPELINED AS
BEGIN 

You can have compile time checking of expressions with Oracle expression filter.

It's probably more complicated than the other solutions, but if you really need to verify your conditions it can be helpful.