Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list (Oracle 11g )
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list [message #594568] Fri, 30 August 2013 03:10 Go to next message
pradeepashwathnarayan9
Messages: 1
Registered: August 2013
Location: Bangalore
Junior Member
Hi,

I have a requirement to send a table data through mail,
so am using procedure inside which am using execute statement after opening the connection and am using the following PLSQL code, which am failing to execute successfully.

My code goes like this.

0 10 20 30 40 50
1 CREATE OR REPLACE PROCEDURE SEND_TABLE_DATA( FROMAD IN VARCHAR2,
2 TOAD IN VARCHAR2,
3 SUBJECT IN VARCHAR2,
4 MESSAGE IN VARCHAR2,
5 DOCID IN VARCHAR2,
6 DOCDT IN DATE,
7 PRODOAID IN NUMBER )
8 AS
9 BATCHNO VARCHAR2(32767);
10 PCSBOX NUMBER;
11 AMOUNT NUMBER;
12 SMTPHOST VARCHAR2(255) := 'XXX.XXX.X.XXX' ;
13 A UTL_SMTP.CONNECTION ;
14 BEGIN
15 A :=UTL_SMTP.OPEN_CONNECTION(SMTPHOST,25);
16 UTL_SMTP.HELO(A,SMTPHOST);
17 UTL_SMTP.MAIL(A,FROMAD);
18 UTL_SMTP.RCPT(A,TOAD);
19 UTL_SMTP.OPEN_DATA(A);
20 UTL_SMTP.WRITE_DATA(A, CHR(13) ||CHR(13) || CHR(13) );
21 UTL_SMTP.WRITE_DATA (A,'Date: '|| TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') || CHR(13) );
22 UTL_SMTP.WRITE_DATA(A,'From: '||FROMAD|| CHR(13) );
23 UTL_SMTP.WRITE_DATA(A, 'To: '||TOAD|| CHR(13) );
24 UTL_SMTP.WRITE_DATA(A, 'Subject: '|| SUBJECT || CHR(13) );
25 UTL_SMTP.WRITE_DATA(A,MESSAGE||DOCID||' Documented on '||DOCDT||CHR(13) );
26 UTL_SMTP.WRITE_DATA(A,CHR(13) || CHR(13) || CHR(13) );
27 UTL_SMTP.WRITE_DATA(A,'This is for your information'||CHR(13) );
28 UTL_SMTP.WRITE_DATA (A,' BATCHNO '|| ' -- '||' PCSBOX '||' -- '||' AMOUNT '||CHR(13) );
29 EXECUTE IMMEDIATE
30 'SELECT
31 A.BATCHNO,B.PCSBOX,B.AMOUNT
32 FROM
33 SCHEMA1.TABLEX A,SCHEMA2.TABLEY B
34 WHERE
35 A.BATCHID=B.BATCHNO AND B.PRODOAID='|| PRODOAID
36 BULK COLLECT INTO BATCHNO,PCSBOX,AMOUNT;
37 FOR indx IN 1..BATCHNO.COUNT
38 LOOP
39 UTL_SMTP.WRITE_DATA (A,BATCHNO(indx)|| ' -- '||PCSBOX(indx)||' -- '||AMOUNT(indx)||CHR(13) );
40 END LOOP;
41 UTL_SMTP.WRITE_DATA( A,CHR(13) || CHR(13) || CHR(13) );
42 UTL_SMTP.CLOSE_DATA(A);
43 UTL_SMTP.QUIT(A);
44 EXCEPTION
45 WHEN OTHERS THEN
46 UTL_SMTP.QUIT(A);
47 RAISE;
48 END;
49 /

SELECT * FROM USER_ERRORS

NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
SEND_TABLE_DATA PROCEDURE 3 37 1 PL/SQL: Statement ignored ERROR 0
SEND_TABLE_DATA PROCEDURE 2 37 24 PLS-00487: Invalid reference to variable 'BATCHNO' ERROR 487
SEND_TABLE_DATA PROCEDURE 1 36 25 PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list ERROR 497


Thanks In Advance

Regards
Pradeep.
Re: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list [message #594571 is a reply to message #594568] Fri, 30 August 2013 03:14 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't bulk collect into scaler variables. They can only hold one value at a time, so how do you expect to get multiple values into them?
You need to bulk collect into arrays.
However, I see no reason for the dynamic sql here so you can just replace it all with a cursor for loop, no bulk collect needed.
Re: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list [message #594573 is a reply to message #594571] Fri, 30 August 2013 03:15 Go to previous message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
and before your next post can you please read and follow How to use [code] tags and make your code easier to read?
Previous Topic: compare prev records with current
Next Topic: Difference between two declaration.
Goto Forum:
  


Current Time: Sat Nov 22 23:03:55 CST 2014

Total time taken to generate the page: 0.09132 seconds