This content has been marked as final. Show 5 replies
I'm afraid that is not possible, because the parameters have different type.
You want to give in a varchar2 and retrieve a refcursor.
Thanks for your reply. Do know if there are another solution ?
The SQL statement receive is dynamic. Is for this cause that I must receive it. The client calling this procedure pass a sql statment and I return the resultset.
Why do you not want to use two parameters?
Alternatively you could use a function (something like this)
create or replace function f_my_test(VAL1 in number, val2 in number, val3 in number, val4 in VARCHAR2) return sys_refcursor is v_c sys_refcursor; begin open v_c for 'select * FROM (select x_x.* ,rownum rn FROM ('||val4 ||') x_x where rownum <= '|| to_char(val3)||' ) where rn >='|| to_char(val2); return v_c; end;
Can we have more detail please?
A sys_refcursor is not a SQL statement that you can add predicates to. So not possible to combine the two arguments. Not at all.
To receive a refcursor and try to do it doesn't make sense.
See Refcursor 101 thread:
PL/SQL 101 : Understanding Ref Cursors
If you want to receive a SQL statement as a VARCHAR2/CLOB/LONG and you want to wrap it with further predicates then you can do that just with string manipulation - as you're doing.
However, if you do receive a sql statement as a string and want to wrap it with a select and further predicates, you should use binds for the predicate values not string concatenation of literals which is likely to be poorly performant and a possible security issue (also see DBMS_ASSERT).
Edited by: DomBrooks on Nov 9, 2010 11:00 AM
The procedure must received a sql query that we don't no the structure and must be return it on a range of record (between row_x to row_y).
The client program is in java. It's a old application that access an AS400 server and we are migrating to ORACLE 11g. But the client must access the both dependend the profile of the user.