OPEN Sales_Order FOR 'SELECT * FROM TMP_Sales_Order_new' || str;
strsql:='SELECT * FROM TMP_Sales_Order_new' || strOPEN Sales_Order FOR strsql;
Compilation errors for PROCEDURE TAPPS.SP_SALES_ORDERError: PLS-00455: cursor 'SALES_ORDER' cannot be used in dynamic SQL OPEN statement Line: 190 Text: OPEN Sales_Order FOR 'SELECT * FROM TMP_Sales_Order_new ' || s12_where;Error: PL/SQL: Statement ignored Line: 190 Text: OPEN Sales_Order FOR 'SELECT * FROM TMP_Sales_Order_new ' || s12_where;
SQL: s12_where:=' Order by S_O,DUE_DATE'; -- OPEN Sales_Order FOR 'SELECT * FROM TMP_Sales_Order_new ' || s12_where; strsql:='SELECT * FROM TMP_Sales_Order_new' || s12_where;OPEN Sales_Order FOR strsql; Error:Compilation errors for PROCEDURE TAPPS.SP_SALES_ORDERError: PLS-00455: cursor 'SALES_ORDER' cannot be used in dynamic SQL OPEN statement Line: 194 Text: OPEN Sales_Order FOR strsql;Error: PL/SQL: Statement ignored Line: 194 Text: OPEN Sales_Order FOR strsql; 还是不行,兄弟们继续努力!!
Sales_Order 是一个type 定义一个变量 v_sale_order Sales_Order; begin s12_where:='Order by S_O,DUE_DATE'; -- OPEN Sales_Order FOR 'SELECT * FROM TMP_Sales_Order_new ' ¦¦ s12_where; strsql:='SELECT * FROM TMP_Sales_Order_new'¦¦s12_where;
OPEN v_Sales_Order FOR strsql;
PLS-00455 cursor 'string' cannot be used in dynamic SQL OPEN statement Cause: A REF CURSOR was dynamically opened that has RETURN type. However, only REF CURSOR without RETURN type can be opened by an embedded dynamic OPEN statement. Action: define a REF CURSOR without return type, and use it in the statement.
有个笨办法: 先用execute immediate 'insert into tmp_table SELECT * FROM TMP_Sales_Order_new '|| str; 把查询结果导到一个临时表里,然后再用游标从临时表中取。
我自己解决了,谢谢各位,结贴 方法分享如下: create or replace procedure SP_Sales_Order (
in_sortby1 in varchar2, in_sortby2 in varchar2, Sales_Order IN OUT pack_rpt_sales.Sales_Order ) as insert into tmp_sales_order_new select * from tmp_sales_order; OPEN Sales_Order FOR SELECT * FROM TMP_Sales_Order_new Order by decode(IN_sortby1,'SO',S_O,'CUST_CODE',CUST_CODE,'PO',P_O,'CUST_PN',CUSTOMER_P_N,'EPC_PN',P_N,'DUE_DATE',TO_CHAR(DUE_DATE,'YYYYMMDD'),'SCH_DATE',TO_CHAR(SCH_DATE,'YYYYMMDD'),'ENT_DATE',TO_CHAR(ENT_DATE,'YYYYMMDD'),'SHIP_LOCATION',SHIP_LOCATION),decode(IN_sortby2,'SO',S_O,'CUST_CODE',CUST_CODE,'PO',P_O,'CUST_PN',CUSTOMER_P_N,'EPC_PN',P_N,'DUE_DATE',TO_CHAR(DUE_DATE,'YYYYMMDD'),'SCH_DATE',TO_CHAR(SCH_DATE,'YYYYMMDD'),'ENT_DATE',TO_CHAR(ENT_DATE,'YYYYMMDD'),'SHIP_LOCATION',SHIP_LOCATION);
Line: 190
Text: OPEN Sales_Order FOR 'SELECT * FROM TMP_Sales_Order_new ' || s12_where;Error: PL/SQL: Statement ignored
Line: 190
Text: OPEN Sales_Order FOR 'SELECT * FROM TMP_Sales_Order_new ' || s12_where;
s12_where:=' Order by S_O,DUE_DATE';
-- OPEN Sales_Order FOR 'SELECT * FROM TMP_Sales_Order_new ' || s12_where;
strsql:='SELECT * FROM TMP_Sales_Order_new' || s12_where;OPEN Sales_Order FOR strsql;
Error:Compilation errors for PROCEDURE TAPPS.SP_SALES_ORDERError: PLS-00455: cursor 'SALES_ORDER' cannot be used in dynamic SQL OPEN statement
Line: 194
Text: OPEN Sales_Order FOR strsql;Error: PL/SQL: Statement ignored
Line: 194
Text: OPEN Sales_Order FOR strsql;
还是不行,兄弟们继续努力!!
定义一个变量
v_sale_order Sales_Order;
begin
s12_where:='Order by S_O,DUE_DATE';
-- OPEN Sales_Order FOR 'SELECT * FROM TMP_Sales_Order_new ' ¦¦ s12_where;
strsql:='SELECT * FROM TMP_Sales_Order_new'¦¦s12_where;
OPEN v_Sales_Order FOR strsql;
Cause: A REF CURSOR was dynamically opened that has RETURN type.
However, only REF CURSOR without RETURN type can be opened by an
embedded dynamic OPEN statement.
Action: define a REF CURSOR without return type, and use it in the statement.
先用execute immediate 'insert into tmp_table SELECT * FROM TMP_Sales_Order_new '|| str;
把查询结果导到一个临时表里,然后再用游标从临时表中取。
方法分享如下:
create or replace procedure SP_Sales_Order
(
in_sortby1 in varchar2,
in_sortby2 in varchar2,
Sales_Order IN OUT pack_rpt_sales.Sales_Order
)
as
insert into tmp_sales_order_new select * from tmp_sales_order;
OPEN Sales_Order FOR SELECT * FROM TMP_Sales_Order_new Order by decode(IN_sortby1,'SO',S_O,'CUST_CODE',CUST_CODE,'PO',P_O,'CUST_PN',CUSTOMER_P_N,'EPC_PN',P_N,'DUE_DATE',TO_CHAR(DUE_DATE,'YYYYMMDD'),'SCH_DATE',TO_CHAR(SCH_DATE,'YYYYMMDD'),'ENT_DATE',TO_CHAR(ENT_DATE,'YYYYMMDD'),'SHIP_LOCATION',SHIP_LOCATION),decode(IN_sortby2,'SO',S_O,'CUST_CODE',CUST_CODE,'PO',P_O,'CUST_PN',CUSTOMER_P_N,'EPC_PN',P_N,'DUE_DATE',TO_CHAR(DUE_DATE,'YYYYMMDD'),'SCH_DATE',TO_CHAR(SCH_DATE,'YYYYMMDD'),'ENT_DATE',TO_CHAR(ENT_DATE,'YYYYMMDD'),'SHIP_LOCATION',SHIP_LOCATION);