我无法编译这段Oracle代码,因为编译器报告“PL / SQL:ORA-00942:表或视图不存在”。
Oracle表存在,但此过程必须根据“Order_ID”参数将表名传递给For Loop过程。我正在使用表存在的模式下工作,因此我不会指定模式名称。
例如:TEMP_TBL_123存在于数据库中,并通过传递123的order_ID来尝试使用变量TMP_TBL_NM来保存表名“TEMP_TBL_123”。
.................................................
CREATE OR REPLACE PROCEDURE EMAIL_DEPT_BLAST_TEST (SUBJECT VARCHAR2,MAIL_FROM VARCHAR2, MAIL_TO VARCHAR2,
L_MESSAGE VARCHAR2, L_MESSAGE2 VARCHAR2, ORDER_ID NUMBER)
IS
MAIL_HOST VARCHAR2(30):='XX.XX.XX.XX';
MAIL_CONN UTL_SMTP.CONNECTION;
TMP_TBL_NM VARCHAR2(30);
BEGIN
TMP_TBL_NM := 'TEMP_TBL_' || ORDER_ID;
MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(MAIL_HOST, 25);
UTL_SMTP.HELO(MAIL_CONN, MAIL_HOST);
UTL_SMTP.MAIL(MAIL_CONN,'XXX@XXXXXX.com');
UTL_SMTP.RCPT(MAIL_CONN, MAIL_TO);
UTL_SMTP.OPEN_DATA(MAIL_CONN);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Date: '||to_char(trunc(SYSDATE))||utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'From: '|| mail_from ||utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'To: '|| mail_to || utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Subject: '||subject||utl_tcp.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, ''|| L_MESSAGE || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Order details:' || UTL_TCP.crlf);
UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Quantity - Location ID - Address' || UTL_TCP.crlf);
BEGIN
FOR I IN (SELECT LOCATION_ID, TRIM(TO_CHAR(COUNT(*),'9,999')) AS QUANTITY FROM TMP_TBL_NM GROUP BY LOCATION_ID)
LOOP
UTL_SMTP.WRITE_DATA(MAIL_CONN, I.QUANTITY || ' - ');
UTL_SMTP.WRITE_DATA(MAIL_CONN, I.LOCATION_ID || ' ');
UTL_SMTP.WRITE_DATA(MAIL_CONN,UTL_TCP.CRLF);
END LOOP;
END;
UTL_SMTP.WRITE_DATA(MAIL_CONN, L_MESSAGE2 || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(MAIL_CONN,UTL_TCP.CRLF);
UTL_SMTP.CLOSE_DATA(MAIL_CONN);
UTL_SMTP.QUIT(MAIL_CONN);
END SCT_CNTS_EMAIL_DEPT_BLAST_TEST;