How to use a variable for a table name when running a For Loop within an Oracle pl/sql email send
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11550635/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a variable for a table name when running a For Loop within an Oracle pl/sql email send
提问by user1536113
I am unable to complile this section of Oracle code as the compiler reports "PL/SQL: ORA-00942: table or view does not exist"
I am unable to complile this section of Oracle code as the compiler reports "PL/SQL: ORA-00942: table or view does not exist"
The Oracle table exists but this procedure must pass a table name to the For Loop procedure based on the "Order_ID" parameter. I am working within the Schema where the table exists so I am not addressing a schema name.
The Oracle table exists but this procedure must pass a table name to the For Loop procedure based on the "Order_ID" parameter. I am working within the Schema where the table exists so I am not addressing a schema name.
Example: TEMP_TBL_123 exists in the database and by passing the order_ID of 123 I am attempting to use variable TMP_TBL_NM to hold the table name "TEMP_TBL_123".
Example: TEMP_TBL_123 exists in the database and by passing the order_ID of 123 I am attempting to use variable TMP_TBL_NM to hold the table name "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,'[email protected]');
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;
回答by DCookie
I didn't have much luck making John's example work. Here's something that does:
I didn't have much luck making John's example work. Here's something that does:
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>
I don't think you can return a cursor with an EXECUTE IMMEDIATE statement. This documentation page,second paragraph, seems to indicate not. At any rate, you don't need it, just use the OPEN - FOR syntax.
I don't think you can return a cursor with an EXECUTE IMMEDIATE statement. This documentation page,second paragraph, seems to indicate not. At any rate, you don't need it, just use the OPEN - FOR syntax.
回答by OraNob
The reason why you get the PL/SQL: ORA-00942 is because in your original code the compiler is looking for a table called "TMP_TBL_NM". This doesn't exist in your schema and is really a variable holding the name of the table within your PL/SQL.
The reason why you get the PL/SQL: ORA-00942 is because in your original code the compiler is looking for a table called "TMP_TBL_NM". This doesn't exist in your schema and is really a variable holding the name of the table within your PL/SQL.
I took the liberty of adapting a previous answer and used a bit of dynamic SQL to come up with a variation for your answer.
I took the liberty of adapting a previous answer and used a bit of dynamic SQL to come up with a variation for your answer.
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,'[email protected]');
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);
TYPE ItemRec IS RECORD (
loc_id NUMBER,
qty_cnt NUMBER);
TYPE ItemSet IS TABLE OF ItemRec;
all_items ItemSet;
BEGIN
stmt := 'SELECT LOCATION_ID, TRIM(TO_CHAR(COUNT(*),'9,999')) AS QUANTITY FROM '||TMP_TBL_NM ||' GROUP BY LOCATION_ID';
EXECUTE IMMEDIATE stmt
BULK COLLECT
INTO all_items;
FOR i IN all_items.FIRST..all_items.LAST
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;
回答by John D
The only way to pass variables into SQL statements like this would be to run the query as dynamic sql.
The only way to pass variables into SQL statements like this would be to run the query as dynamic sql.
Your code might be something like:
Your code might be something like:
DECLARE
query_output SYS_REFCURSOR;
query_statement VARCHAR2(1000);
BEGIN
query_statement := 'SELECT LOCATION_ID, TRIM(TO_CHAR(COUNT(*),'9,999')) AS QUANTITY FROM ' || TMP_TBL_NM || ' GROUP BY LOCATION_ID';
execute immediate query_statement using out query_output;
....
END
Now that you have a cursor, fetch the contents, loop over the count and do everything else
Now that you have a cursor, fetch the contents, loop over the count and do everything else
