PROCEDURE PROCESS_ITEM_EXP_STATUS_UPD(in_inquiry_id IN inquire_all.inquiry_id%TYPE,
in_item_populate_status IN inquire_all.item_populate_status%TYPE)
IS
v_CurMonth number;
v_PrevMonth number;
v_cursor number;
v_row number;BEGIN
v_CurMonth := to_number(to_char(sysdate, 'MM'));
IF (v_CurMonth=1)
THEN
v_PrevMonth := 12;
ELSE
v_PrevMonth := v_CurMonth -1;
END IF; BEGIN
v_cursor := dbms_sql.open_cursor;
/*dbms_sql.parse(v_cursor, 'UPDATE INQUIRE_' || v_PrevMonth || v_sqlStr, dbms_sql.NATIVE);*/
/*dbms_sql.parse(v_cursor, 'UPDATE G3WEB.INQUIRE_ALL_2 SET ITEM_POPULATE_STATUS = '||in_item_populate_status ||',ITEM_POPULATE_DATE = sysdate WHERE INQUIRY_ID = '||in_inquiry_id || ' AND FORM_COMPLETE_STATUS = ''Y''', dbms_sql.NATIVE);*/
dbms_sql.parse(v_cursor, 'UPDATE INQUIRE_' || v_PrevMonth ||' SET ITEM_POPULATE_STATUS = ' || in_item_populate_status || ' , ITEM_POPULATE_DATE = sysdate WHERE INQUIRY_ID = '||in_inquiry_id || ' AND FORM_COMPLETE_STATUS = ''Y''', dbms_sql.NATIVE);
v_row := dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
EXCEPTION
WHEN others THEN
dbms_sql.close_cursor(v_cursor);
RAISE_APPLICATION_ERROR(-20200, '22Error in updating table [INQUIRE_'||v_PrevMonth ||'] SQLERROR: ' || SQLERRM(sqlcode));
END;
COMMIT;
END PROCESS_ITEM_EXP_STATUS_UPD;
问题:
如果把 in_item_populate_status hardcode 成'Y' ,没问题,
但是当传参数in_item_populate_status = 'Y'是不可以。
出现的error 是:SQLERROR: ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_Y'OGC_Y 是什么?
猜可能是个函数吧?
有关系么?
问题补充:
如果传递的参数不是"Y" 例如"I" 等别的,就会出现新的error: SQLERROR: ORA-00904: "I": invalid identifier大家都没有遇到过这种情况么????
dbms_sql.parse(v_cursor, 'UPDATE INQUIRE_' || v_PrevMonth ||' SET ITEM_POPULATE_STATUS = ' || '''' || in_item_populate_status || '''' || ' , ITEM_POPULATE_DATE = sysdate WHERE INQUIRY_ID = '||in_inquiry_id || ' AND FORM_COMPLETE_STATUS = ''Y''', dbms_sql.NATIVE);
v_row := dbms_sql.execute(v_cursor);
在 in_item_populate_status 加上两个单引号。
上面的那个问题也就明了了, 可能如果 不加的时候,把Y 当成Synonym 这样是不可以的,原因如上。
如果不是 "Y",当然就认为是 invalid indentifier 啦~第一次写dynamic SQL/PL ,嗬嗬,遇到这样的问题真得有点哭笑不得:)谢谢!