Select dinâmico em PL/SQL

não entendi, pode tentar explicar de novo.

minha procedure recebe alguns parametros de entrada,
preciso concatena-los em uma variavel que conterá meu select depois preciso executar o conteudo essa variável que terá minha query montada.

do help (manual de pl/sql 8i)

Using DDL and Dynamic SQL
This section explains why PL/SQL does not support SQL data definition language
(DDL) or dynamic SQL, then shows how to solve the problem.
Efficiency versus Flexibility
Before a PL/SQL program can be executed, it must be compiled. The PL/SQL
compiler resolves references to Oracle schema objects by looking up their
definitions in the data dictionary. Then, the compiler assigns storage addresses to
program variables that will hold Oracle data so that Oracle can look up the
addresses at run time. This process is called binding.
Using DDL and Dynamic SQL
5-8 PL/SQL User’s Guide and Reference
How a database language implements binding affects runtime efficiency and
flexibility. Binding at compile time, called static or early binding, increases efficiency
because the definitions of schema objects are looked up then, not at run time. On
the other hand, binding at run time, called dynamic or late binding, increases
flexibility because the definitions of schema objects can remain unknown until then.
Designed primarily for high-speed transaction processing, PL/SQL increases
efficiency by bundling SQL statements and avoiding runtime compilation. Unlike
SQL, which is compiled and executed statement-by-statement at run time (late
binding), PL/SQL is processed into machine-readable p-code at compile time (early
binding). At run time, the PL/SQL engine simply executes the p-code.
Some Limitations
However, this design imposes some limitations. For example, the p-code includes
references to schema objects such as tables and stored procedures. The PL/SQL
compiler can resolve such references only if the schema objects are known at
compile time. In the following example, the compiler cannot process the procedure
because the table is undefined until the procedure is executed at run time:
CREATE PROCEDURE create_table AS
BEGIN
CREATE TABLE dept (deptno NUMBER(2), ...); -- illegal
...
END;
In the next example, the compiler cannot bind the table reference in the DROP
TABLE statement because the table name is unknown until the procedure is
executed:
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
DROP TABLE table_name; -- illegal
...
END;
Overcoming the Limitations
However, the package DBMS_SQL, which is supplied with Oracle, allows PL/SQL
to execute SQL data definition and data manipulation statements dynamically at
run time. For example, when called, the following stored procedure drops a
specified database table:
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
cid INTEGER;
Managing Cursors
Interaction with Oracle 5-9
BEGIN
/* Open new cursor and return cursor ID. */
cid := DBMS_SQL.OPEN_CURSOR;
/* Parse and immediately execute dynamic SQL statement built by
concatenating table name to DROP TABLE command. */
DBMS_SQL.PARSE(cid, ’DROP TABLE ’ || table_name, dbms_sql.v7);
/* Close cursor. */
DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
/* If an exception is raised, close cursor before exiting. */
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
RAISE; -- reraise the exception
END drop_table;
For more information about package DBMS_SQL, see Oracle8 Application Developer’s
Guide.
Managing Cursors
Recall from Chapter 1 that PL/SQL uses two types of...

DBMS_SQL.PARSE , creio que seja isto que vc procura

Motta era isso mesmo, mas acabei encontrando tb, essa referencia.
Mas todas as referencias que falam sobre isso só dão exemplo para insert, delete ou update e eu queria para um select que retornasse mais de uma linha, então descobrir outra maneira bem mais.

TYPE TCURSOR IS REF CURSOR;
v_query VARCHAR2(3000);

v_query := ´SELECT * FROM USUARIO´ ;

meu_cursor TCURSOR;

OPEN meu_cursor FOR
v_query;

Pronto ele executa a query dentro da minha variável VARCHAR2

Valeu pela força e se alguem tinha essa msm dúvida taí minha solução

sua solução pode servir para alguns problemas que tenho aqui.