This discussion is archived
5 Replies Latest reply: Nov 9, 2010 4:44 AM by 727453 RSS

sys_refcursor procedure parameters in out

727453 Newbie
Currently Being Moderated
Hi,
I have created a procedure that receive the SQL statment in VARCHAR" datatype parameters and return the Resultset into a refcursor :
create or replace
procedure my_test(VAL1 in number, val2 in number, val3 in number, val4 in VARCHAR2, vla5 out nocopy SYS_REFCURSOR )
is
begin
  open vla5 for 'select * FROM (select x_x.*  ,rownum rn  FROM ('||val4 ||') x_x where rownum <= '|| to_char(val3)||' ) where rn >='|| to_char(val2);
end;
I like to pass and receive the val4 and val5 in one parameters :
procedure my_test(VAL1 in number, val2 in number, val3 in number, val4 in out nocopy SYS_REFCURSOR )
I like to pass the SQL statment in VARCHAR2 datatype to my procedure and return the result in the same variable parameters.
I don't know if my english was understand, but could you please help me to find a solution?

thanks a lot.

Calà Salvatore
  • 1. Re: sys_refcursor procedure parameters in out
    hm Expert
    Currently Being Moderated
    I'm afraid that is not possible, because the parameters have different type.

    You want to give in a varchar2 and retrieve a refcursor.
  • 2. Re: sys_refcursor procedure parameters in out
    727453 Newbie
    Currently Being Moderated
    Hi,
    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.

    Best regards
  • 3. Re: sys_refcursor procedure parameters in out
    hm Expert
    Currently Being Moderated
    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;
  • 4. Re: sys_refcursor procedure parameters in out
    Dom Brooks Guru
    Currently Being Moderated
    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
  • 5. Re: sys_refcursor procedure parameters in out
    727453 Newbie
    Currently Being Moderated
    Hi,
    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.

    Best regards,

    Salvatore

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points