在Oracle pl / sql电子邮件发送中运行For循环时如何使用变量作为
发布时间:2021-03-06 13:14:09 所属栏目:站长百科 来源:网络整理
导读:我无法编译Oracle代码的这一部分,因为编译器报告“PL / SQL:ORA-00942:表或视图不存在” Oracle表存在,但此过程必须根据“Order_ID”参数将表名传递给For循环过程.我在表存在的Schema中工作,所以我没有解决模式名称. 示例:TEMP_TBL_123存在于数据库中,通
我无法编译Oracle代码的这一部分,因为编译器报告“PL / SQL:ORA-00942:表或视图不存在” Oracle表存在,但此过程必须根据“Order_ID”参数将表名传递给For循环过程.我在表存在的Schema中工作,所以我没有解决模式名称. 示例:TEMP_TBL_123存在于数据库中,通过传递order_ID为123,我尝试使用变量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; 解决方法我没有太多运气让约翰的榜样奏效.这是做的事情:DECLARE C SYS_REFCURSOR; stmt VARCHAR2(1000); tmp_tbl_nm VARCHAR2(64) := 'USER_TABLES'; the_name VARCHAR2(64); BEGIN stmt := 'SELECT table_name FROM ' || TMP_TBL_NM || ' ORDER BY 1'; OPEN C FOR stmt; LOOP FETCH C INTO the_name; EXIT WHEN C%NOTFOUND; dbms_output.put_line(the_name); END LOOP; END; / CONTINENT COUNTRY COUNTRYINFOIMPORT COUNTRY_LANGUAGE GEONAME LANGUAGE PL/SQL procedure successfully completed. SQL> 我不认为你可以使用EXECUTE IMMEDIATE语句返回游标. This documentation page,第二段,似乎表明没有.无论如何,你不需要它,只需使用OPEN – FOR语法. (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
站长推荐
热点阅读