PLS-00455: cursor cannot be used in dynamic

[Creepers]

I am trying to dynamically create SQL and pass it into a cursor. Getting this error:
PLS-00455: cursor 'ORDERSNOTINVOICEDCURSOR' cannot be used in dynamic SQL OPEN statement.

Here is my code:
PROCEDURE p_OrdersNotInvoiced(OrdersNotInvoicedCursor in out OrdersNoInvoicedCur,
p_OrderNum NUMBER:=NULL,
p_OU VARCHAR2:=NULL,
p_CustName VARCHAR2:=NULL,
p_BDateStart VARCHAR2:=NULL,
p_BDateEnd VARCHAR2:=NULL,
p_DDateStart VARCHAR2:=NULL,
p_DDateEnd VARCHAR2:=NULL)
IS
--OutputFile_utl UTL_FILE.file_type;
--cur OrdersNoInvoicedCur;
v_Select VARCHAR2(20000);
v_From VARCHAR2(4000);
v_Where VARCHAR2(4000);
v_OrderBy VARCHAR2(4000);


--NEW
--NoInvoice_rec OrdersNotInvoicedCursor%rowtype;
--Type RefCur is REF CURSOR;
--NoInvoice_cv RefCur;
begin
v_Select := 'SELECT f_od.sales_order_number, f_od.line_number,' || Chr(10);
v_Select := v_Select || 'd_o.operating_unit_name, d_c.customer_name,' || Chr(10);
v_Select := v_Select || 'f_od.standard_item_desc, f_od.booked_date,' || Chr(10);
v_Select := v_Select || 'f_od.actual_ship_date' || Chr(10);

v_From := 'from f_order_details@oa_dw f_od, d_operating_unit@oa_dw d_o, d_customer@oa_dw d_c' || Chr(10);
v_Where := 'WHERE f_od.operating_unit_key = d_o.operating_unit_key AND' || Chr(10);
v_Where := v_Where || 'f_od.customer_key = d_c.customer_key AND' || chr(10);
v_Where := v_Where || 'f_od.line_status = ' || Chr(39) || 'CLOSED' || Chr(39) ||' AND' || chr(10);
v_Where := v_Where || 'trunc(f_od.actual_ship_date) BETWEEN ' || Chr(39) || '01-JAN-04' || Chr(39) ||' AND trunc(sysdate) AND' ||Chr(10);
v_Where := v_Where || 'NOT EXISTS (SELECT 1 from f_ar_sales_details@oa_dw f_asd WHERE f_asd.order_line_id = f_od.line_id) AND' || Chr(10);
v_Where := v_Where || 'NOT EXISTS (SELECT 1 from l_order_invoice_recon@oa_dw l_oir WHERE l_oir.line_id = f_od.line_id) AND' || Chr(10);
If NVL(p_OrderNum,0) <> 0 Then
v_Where := v_Where || 'Sales_Order_Number = ' || p_OrderNum || ' AND' || Chr(10);
End If;
If NVL(p_OU,' ') <> ' ' Then
v_Where := v_Where || 'Operating_Unit_Name LIKE ' || Chr(39) || p_OU || Chr(39) || ' AND' || Chr(10);
End If;
If NVL(p_CustName,' ') <> ' ' Then
v_Where := v_Where || 'Customer_Name LIKE ' || Chr(39) || p_CustName || Chr(39) || ' AND' || Chr(10);
End If;
If NVL(p_BDateStart,' ') <> ' ' Then
v_Where := v_Where || 'Booked_Date >= TO_DATE(' || p_BDateStart || ') AND' || Chr(10);
End If;
If NVL(p_BDateEnd,' ') <> ' ' Then
v_Where := v_Where || 'Booked_Date <= TO_DATE(' || p_BDateEnd || ') AND' || Chr(10);
End If;
If NVL(p_DDateStart,' ') <> ' ' Then
v_Where := v_Where || 'Actual_Ship_Date >= TO_DATE(' || p_DDateStart || ') AND' || Chr(10);
End If;
If NVL(p_DDateEnd,' ') <> ' ' Then
v_Where := v_Where || 'Actual_Ship_Date <= TO_DATE(' || p_DDateEnd || ') AND' || Chr(10);
End If;
v_Where := SUBSTR(v_Where,1,Length(v_Where)-4) || Chr(10);

v_OrderBy := 'ORDER BY d_o.operating_unit_name, f_od.sales_order_number';

-- Error At Next Line

OPEN OrdersNotInvoicedCursor FOR
v_Select || v_From || v_Where || OrderBy;
END p_OrdersNotInvoiced;

Any help would be appreciated... Thanks in advance

 

[carp]

You are really making your code more complex than it needs to be with all of the concatenations and CHR(**)!

Try something like this - at least it will be easier to read:

PROCEDURE p_OrdersNotInvoiced(OrdersNotInvoicedCursor in out OrdersNoInvoicedCur,
                                p_OrderNum NUMBER:=NULL,
                              p_OU         VARCHAR2:=NULL,
                              p_CustName VARCHAR2:=NULL,
                              p_BDateStart VARCHAR2:=NULL,
                              p_BDateEnd   VARCHAR2:=NULL,
                              p_DDateStart VARCHAR2:=NULL,
                              p_DDateEnd   VARCHAR2:=NULL)
IS
  --OutputFile_utl UTL_FILE.file_type;
  --cur OrdersNoInvoicedCur;
  v_Select VARCHAR2(20000);
  v_From VARCHAR2(4000);
  v_Where VARCHAR2(4000);
  v_OrderBy VARCHAR2(4000);
  
  
  --NEW
  --NoInvoice_rec OrdersNotInvoicedCursor%rowtype;
  --Type RefCur is REF CURSOR;
  --NoInvoice_cv RefCur;
begin
     v_Select := 'SELECT f_od.sales_order_number, f_od.line_number,
                  d_o.operating_unit_name, d_c.customer_name,
                  f_od.standard_item_desc, f_od.booked_date,
                  f_od.actual_ship_date';
     
     v_From := ' from f_order_details@oa_dw f_od, d_operating_unit@oa_dw d_o, d_customer@oa_dw d_c';
     v_Where := ' WHERE f_od.operating_unit_key = d_o.operating_unit_key AND 
                        f_od.customer_key = d_c.customer_key AND '
                        f_od.line_status = ''CLOSED'' AND
                        trunc(f_od.actual_ship_date) BETWEEN ''01-JAN-04'' AND trunc(sysdate) AND 
                        NOT EXISTS (SELECT 1 from f_ar_sales_details@oa_dw f_asd 
                                     WHERE f_asd.order_line_id = f_od.line_id) 
                                       AND NOT EXISTS (SELECT 1 from l_order_invoice_recon@oa_dw l_oir 
                                                        WHERE l_oir.line_id = f_od.line_id) 
                                       AND ';
     If NVL(p_OrderNum,0) <> 0 Then
         v_Where := v_Where || 'Sales_Order_Number = ' || p_OrderNum || ' AND ';
     End If;
     If NVL(p_OU,' ') <> ' ' Then
         v_Where := v_Where || 'Operating_Unit_Name LIKE ' || Chr(39) || p_OU || Chr(39) || ' AND ';
     End If;
     If NVL(p_CustName,' ') <> ' ' Then
         v_Where := v_Where || 'Customer_Name LIKE ' || Chr(39) || p_CustName || Chr(39) || ' AND ';
     End If;
     If NVL(p_BDateStart,' ') <> ' ' Then
         v_Where := v_Where || 'Booked_Date >= TO_DATE(' || p_BDateStart || ') AND ';
     End If;
     If NVL(p_BDateEnd,' ') <> ' ' Then
         v_Where := v_Where || 'Booked_Date <= TO_DATE(' || p_BDateEnd || ') AND ';
     End If;
     If NVL(p_DDateStart,' ') <> ' ' Then
         v_Where := v_Where || 'Actual_Ship_Date >= TO_DATE(' || p_DDateStart || ') AND ';
     End If;
     If NVL(p_DDateEnd,' ') <> ' ' Then
         v_Where := v_Where || 'Actual_Ship_Date <= TO_DATE(' || p_DDateEnd || ') AND ';
     End If;

     v_Where := SUBSTR(v_Where,1,Length(v_Where)-4);
     
     v_OrderBy := 'ORDER BY d_o.operating_unit_name, f_od.sales_order_number';

-- Error At Next Line
     
     OPEN OrdersNotInvoicedCursor FOR
           v_Select || v_From || v_Where || OrderBy;     
END p_OrdersNotInvoiced;

Regardless of whether you coose to simplify or not, here are some general approaches I find helpful with dynamic SQL:

1. Whether you think it's necessary or not, use a space between your initial and final characters and an opening/closing tick. In other words, looking at your code, there are several places where I see things like
AND' - you might want to change these to look like
AND '.

2. When in doubt, have the code display your SQL statement. In your case, you would add a line like:

dbms_output.put_line(v_Select || v_From || v_Where || OrderBy);

Inside an exception handler would be a useful place for this. If you do that, I suspect you will find you have an extra "AND" between your WHERE clause and your ORDER BY clause.

Speaking of your ORDER BY clause, shouldn't you be opening a cursor for
v_Select || v_From || v_Where || v_OrderBy
instead of
v_Select || v_From || v_Where || OrderBy: