Using A Non-Char Field In A Ref-Cursor

Hi,

i'm using a non_char field in a dynamic SQL-String i'm building up, to eventually use it in a Ref-Cursor.

The String is the following:
 'select vw1.externe_code externe_code,'||
 ' vw1.mob_id mob_id,'||
 ' vw1.geometrie geometrie '||
 ' from riv_view_'||to_char(r_laag_geo.oty_id)||' vw1'||
 ' where vw1.tech_eind_datum is null'||
 ' and   (   vw1.func_eind_datum is null'||
 '       or  (   vw1.func_eind_datum is not null'||
 '           and vw1.func_eind_datum > sysdate) ) '||
 ' where sdo_filter(vw1.geometrie, '||r_cel.geometrie||' , '||
          ''''||'querytype=window )'||''''||') = '||''''||'TRUE'||''''||';

For the people who haven't seen this before... sdo_filter is an Oracle Spatial function and the variable r_cel.geometrie is a spatial field.

The problem is that this spatial field cannot be converted to char, and i need a char field to use this construction in ref_cursor.

I need this construction i believe because of my "table_name". In the example it's  

riv_view_'||to_char(r_laag_geo.oty_id)||' vw1'||

There are appr. a 1000 views this way, and i don't want to build the same cursor a 1000 times to avoid the uncovertable field.

I need your help, Because i believe that there are more field that cannot be converted.

I don't mind not using a refcursor, but then give me another solution.

My team leader spoke of a way to go around by building a procedure that had the return value "REF Cursor"... i haven't got a clue what he is meaning, but he just went on a trip and can't be disturbed...


I think this is a challenge, so 300 points are in order here. And if anyone can give both answers i'll raise it another 200....

pls pls pls help me... Start Free Trial