5 Replies Latest reply: Nov 9, 2010 6:44 AM by 727453 RSS

    sys_refcursor procedure parameters in out

    727453
      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
          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
            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
              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
                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
                  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