oracle open cursor with string variable gives me errors

Question

I have problems with opening cursor with string variable

here is my oracle database code

FUNCTION f_get_cursor(p_date_to_forecast VARCHAR) RETURN SYS_REFCURSOR AS
v_cursor SYS_REFCURSOR;
v_groupby_stmt VARCHAR(200) := 'GROUP BY ' 
                            || CASE WHEN p_date_to_forecast = 'HOLIDAY' THEN 
                               'DAY, ' ELSE '' END 
                            || 'TNI, FRMP, LR, HH;';

v_select_stmt VARCHAR2(1000) := 'SELECT WEEKDAY, TNI, FRMP, LR, HH, 
                                 AVG(Coalesce(VOLUME, 0)) AS AverageVolume
                                 FROM (SELECT v.TNI, v.FRMP, v.LR, v.DAY, 
                                       v.HH, v.VOLUME, CASE WHEN 
                                       hd.HOLIDAY_DATE is not null 
                                       then ''HOLIDAY''
                                       ELSE trim(to_char(v.DAY, ''Day'')) 
                                       END AS WEEKDAY
                                       FROM v_nem_rm16 v
                                       LEFT JOIN DBP_ADMIN.DBP_HOLIDAY hd 
                                       ON v.DAY = hd.HOLIDAY_DATE
                                       WHERE v.STATEMENT_TYPE !=''FORCAST'')
                                 WHERE WEEKDAY = ''' || p_date_to_forecast 
                                       || '''' || ' ' || v_groupby_stmt;

BEGIN
   OPEN v_cursor FOR v_select_stmt;
   return v_cursor;
END;

I am just trying to open cursor based on the parameter "p_date_to_forcast", which is just string of the name of the week like "Saturday, Tuesday..and so on" and return the cursor.

When I run the query, I got this error
00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. *Action:

What is the problem and How can I fix it???

thanks

I believe that you cannot return ref cursor from a function. Try searching for this online. — Utsav, May 13 '17 at 04:52
@Utsav It is reference cursor I think I can return reference cursor when I just have "select * from dual" for v_select_stmt variable, It works fine — Michael, May 13 '17 at 04:53
Check this http://stackoverflow.com/questions/13690110/function-return-sys-refcursor-call-from-sql-with-specific-columns — Utsav, May 13 '17 at 04:56
@Utsav so how can I dynamically create cursor and return it??? — Michael, May 13 '17 at 05:06

score 0 · Answer 1 · answered May 13 '17 at 04:55

0

Have you tried this

     for r_cur in your_cursor_name(Your_parameter) Loop
     -- your working --
      End loop;

answered May 13 '17 at 04:55

MKhan

1
2

I have just tried. With my original code, I think the cursor is even not opened with giving me the same error. and I have tried with "select 1 from dual" for v_select_stmt variable, then it says "my_cursor_name is undefined or not a procedure". I think the cursor has been opened but the function returns null?? – Michael May 13 '17 at 05:12
please first define your cursor like cursor cr_rs_2 (P_RS_CUST_PO varchar2,P_SHIP_FROM number) is select QUANTITY,CUST_ITEM_CODE from abc where STATUS_ORDER=0 and RS_CUST_PO=P_RS_CUST_PO and SHIP_FROM=P_SHIP_FROM; – MKhan May 13 '17 at 05:14
should I define the cursor "----your working----" here? – Michael May 13 '17 at 05:22
um so sorry but I don't actually understand what you want me to do. The functions just wants to open cursor and return reference of the cursor so that it can be used in like procedure. Where should I put the for loop and what does it mean?? sorry I am kind of new to oracle database and cursor – Michael May 13 '17 at 05:36
actually here ---cursor cr_rs_2 (P_RS_CUST_PO varchar2,P_SHIP_FROM number) is select QUANTITY,CUST_ITEM_CODE from abc where STATUS_ORDER=0 and RS_CUST_PO=P_RS_CUST_PO and SHIP_FROM=P_SHIP_FROM---- I am defining a cursor with parameter – MKhan May 13 '17 at 05:58
Then using it with ----------for r_cur in your_cursor_name(Your_parameter) Loop -- your working -- End loop;---- – MKhan May 13 '17 at 05:59

oracle open cursor with string variable gives me errors

1 Answers1