CODEWITHSUNDEEP
oracle-database
user8368945
user8368945

Reputation:

How to create dynamic sql for with sys_refcursor in oracle

I have function which returns sys_refcursor I give you my code's example.

 function myfunc( p_city           IN VARCHAR2,
p_order           IN VARCHAR2)
RETURN SYS_REFCURSOR IS
    v_result          SYS_REFCURSOR;
begin
OPEN v_result FOR WITH all_prb AS(
select * from tableA ta inner join tableB tb)


'select * from all_prb ff where (:p_city is null or (LOWER(ff.city) like ''%''||:p_city||''%'') ) order by ' || p_order || 'asc' using p_city,p_city;
return v_result;
end myfunc;

and when i am trying to compile it i have ORA-00928: missing SELECT keyword and this error targets line where i have dynamic sql 'select * from all_prb ff where ...'

How can i fix it and how can i write correct dynamic sql ? I am writing dynamic sql for ordering .

Upvotes: 0

Views: 2217

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

I'm not sure why you're bothering with the with clause, it's simpler without a CTE; you just need to identify which table the city column is in:

function myfunc(p_city IN VARCHAR2,
  p_order IN VARCHAR2)
RETURN SYS_REFCURSOR IS
  v_result          SYS_REFCURSOR;
begin
  OPEN v_result FOR
    'select * from tableA ta
     inner join tableB tb on tb.some_col = ta.some_col
     where :p_city is null or LOWER(ta.city) like ''%''||:p_city||''%''
     order by ' || p_order || ' asc'
     using p_city, p_city;

  return v_result;
end myfunc;
/

I've guessed it's table A, just change the alias if it's the other one. You also need to specify the join condition between the two tables. (Also noticed I added a space before asc to stop that being concatenated into the order-by string).

This compiles without errors; when run I get ORA-00942: table or view does not exist which is reasonable. If I create dummy data:

create table tablea (some_col number, city varchar2(30));
create table tableb (some_col number);

insert into tablea values (1, 'London');
insert into tablea values (2, 'Londonderry');
insert into tablea values (3, 'East London');
insert into tablea values (4, 'New York');

insert into tableb values (1);
insert into tableb values (2);
insert into tableb values (3);
insert into tableb values (4);

then calling it gets:

select myfunc('lond', 'city') from dual;

  SOME_COL CITY                             SOME_COL
---------- ------------------------------ ----------
         3 East London                             3
         1 London                                  1
         2 Londonderry                             2

If you really want to stick with the CTE for some reason then (as @boneist said) that needs to be part of the dynamic statement:

  OPEN v_result FOR
    'with all_prb as (
       select * from tableA ta
       inner join tableB tb on tb.some_col = ta.some_col
     )
     select * from all_prb ff
     where :p_city is null or LOWER(ff.city) like ''%''||:p_city||''%''
     order by ' || p_order || ' asc'
     using p_city, p_city;

Upvotes: 3

Related Questions