How to create dynamic sql for with sys_refcursor in oracle

Question

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 .

Why are you using a `with` clause, and why doesn't that have a join condition? (The code you posted gets ORA-00905, not ORA-00928, so this doesn't seem to be what you actually ran...) — Alex Poole, Aug 11 '17 at 08:29
i tried execute immediate ('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;) too and did not word i am writing first time dynamic sql like this so if you know can you write me code solution how can i write ? — , Aug 11 '17 at 08:30
You've created part of your cursor as static and part as dynamic. It must be all static or all dynamic sql - i.e. you need to include the with clause in your dynamic sql. — Boneist, Aug 11 '17 at 08:31
Boneist i fixed all problems just i need one more help when i am trying to call other function in dynamic sql it writes invalid identifier 'functionName' and when i am deleteing this call in dynamic sql everything works fine. can you help me how can i call function in this code ? — , Aug 11 '17 at 09:00

Alex Poole · Accepted Answer · 2017-08-11T08:46:32.040

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;

thank you it works . Now i have other problem, I want to call other function in this dynamic sql i.e 'select col1,col2,otherFunc(col3) from all_prb ff' and in this case i have Error ORA-00904: "otherFunc": invalid identifier . Can you help me how can i call function in this case ? — , Aug 11 '17 at 09:07
@giorgigoginashvili - if you have a new problem you should ask a new question. If `otherFunc` exists and you have permission to execute it (if it isn't yours, then execute granted directly to you not through a role) then it will just work. I could hazard a guess - it looks like you might be defining `myfunc` in a package, so is `otherFunc` also in a package? If so you have to use the package name when calling it from SQL, even within the same package. — Alex Poole, Aug 11 '17 at 09:11
yaaah i have both function in same package and i wrote package name too but i wore it incorrect :X sorry man now it is OK everything works fine thank you very much — , Aug 11 '17 at 09:14
nope man i edited my older comment you can read it again . thank you one more time — , Aug 11 '17 at 09:20

How to create dynamic sql for with sys_refcursor in oracle

1 Answers1

Linked