Ref cursor with Execute immediate
You need to create a type to keep the data you select is going to return,for example.Imagine this is yours users table
user(id number, name varchar2), your type should look like:create type users_type as object (id number, name varchar2);Create a type table to keep object to created above.
create type users_table_object as table of users_type;create the procedure:
create the procedure test(cv_1 OUT SYS_REFCURSOR....)asusers_table_object;v_query VARCHAR2(2000); beginv_query := N'select users_type(id, name) from users ';execute immediatebulk collect into users_table_object;open cv_1 for table(users_table_object)