SQL & PL/SQL

Hi,

I have a Function and I am trying to execute an "Insert" statement into the Function. It gives me an error saying that-

ERROR:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.POP3", line 243

I then tried adding : PRAGMA AUTONOMOUS_TRANSACTION to the Function and then it gives me an error saying-

ERROR:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "SCOTT.POP3", line 245

My code snippet is as follows :

CREATE OR REPLACE FUNCTION pop3 (
username VARCHAR2,
PASSWORD VARCHAR2,
msgnum NUMBER
)
RETURN tstrings PIPELINED
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
pop3_server CONSTANT VARCHAR2 (100) := 'pop.secureserver.net';
pop3_port CONSTANT NUMBER := 110;
pop3_ok CONSTANT VARCHAR2 (10) := '+OK';
e_pop3_error EXCEPTION;
socket UTL_TCP.connection;
line VARCHAR2 (30000);
line2 VARCHAR2 (30000);
BYTES INTEGER;

msg_from varchar2(4000) := '';
msg_to varchar2(4000) := '';
msg_date varchar2(4000) := '';
msg_sub varchar2(4000) := '';
msg_body clob := NULL;

hyphen_checker number := 0;
msg_body_flag number := 0;
crlf varchar2(2) := chr(13) || chr(10);

-- send a POP3 command
-- (we expect each command to respond with a +OK)
FUNCTION writetopop (command VARCHAR2)
RETURN VARCHAR2
IS
len INTEGER;
resp VARCHAR2 (30000);
BEGIN
len := UTL_TCP.write_line (socket, command);
UTL_TCP.FLUSH (socket);
-- using a hack to check the popd response
len := UTL_TCP.read_line (socket, resp);

IF SUBSTR (resp, 1, 3) != pop3_ok
THEN
RAISE e_pop3_error;
END IF;

RETURN (resp);
END;
BEGIN
DBMS_LOB.CREATETEMPORARY(msg_body,true);
PIPE ROW ('pop3:' || pop3_server || ' port:' || pop3_port);
-- Just to make sure there are no previously opened connections
UTL_TCP.close_all_connections;

-- open a socket connection to the POP3 server
socket :=
UTL_TCP.open_connection (remote_host => pop3_server,
remote_port => pop3_port,
--tx_timeout => POP3_TIMEOUT,
CHARSET => 'US7ASCII'
);
-- read the server banner/response from the pop3 daemon
PIPE ROW (UTL_TCP.get_line (socket));
-- authenticate with the POP3 server using the USER and PASS commands
LOOP
...
...
EXIT WHEN LENGTH (line) = 1 AND line = '.';
END LOOP;

INSERT INTO email (em_issue, em_date, em_from, em_to, em_subject, em_body)
VALUES(seq_issue.nextval, msg_date, msg_from, msg_to, msg_sub, msg_body);

DBMS_LOB.freetemporary(msg_body);
msg_from := '';
msg_to := '';
msg_date := '';
msg_sub := '';
msg_body := NULL;

-- close connection
PIPE ROW ('QUIT');
PIPE ROW (writetopop ('QUIT'));
UTL_TCP.close_connection (socket);

EXCEPTION
WHEN e_pop3_error
THEN
PIPE ROW ('There are no mails !');
END;

Message was edited by:
Monk