PL/SQL: Passing lexical parameter to a stored procedure

i want to pass a whole sql query as a lexical parameter to a stored procedu开发者_开发百科re and then execute it. Any suggestions how to do that?

you may try this:

create or replace procedure my_proc(pstring IN varchar2)
is

begin

  if length(pstring)>0 then

     EXECUTE IMMEDIATE pstring;

  end if;

end my_proc;

here is the official oracle documentation on dynamic plsql : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDGJEGD

Not sure what you mean by "lexical" parameter but you can pass the SQL query in as a VARCHAR2 then execute it using EXECUTE IMMEDIATE.

What you are trying to do is almost certainly the wrong way to go.

Execute Immediate is to be used with caution because it can a) impose a security risk and b) cause negative effects on performance when many distinct SQL statements are run that way.

However, see here how to insert records using execute immediate. Note that it's essential to use bind variables.

If you want to run a query (as opposed to a DML [insert, update, delete] or a PL/SQL block of code), you can do something like this:

function get_dataset (p_sql_query in varchar2) return sys_refcursor
as
  l_returnvalue sys_refcursor;
begin

  open l_returnvalue for p_sql_query;

  return l_returnvalue;

end get_dataset;

The return value is a "weakly typed" REF CURSOR.

The calling program (whether it is Java, .NET, PL/SQL, whatever) must then process the function result and close the cursor.