[Closed] PL/SQL Issues

MyEclipse Archived

Database Tools (DB Explorer, Hibernate, etc.)

This topic has 10 replies, 5 voices, and was last updated 18 years, 1 month ago by mjmotal.

Viewing 11 posts - 1 through 11 (of 11 total)

Author

Posts
February 9, 2006 at 12:54 pm #246433 Reply

bgblanch
Member

Perhaps it’s just me – although I suspect not – but I am having a lot of issues with pl/sql packages. I am able to run them in SQL Worksheet, SQL*Plus, and JDeveloper (10.1.2, 10.1.3ea, 10.1.3). However, just trying to run an anonymous pl/sql block in database explorer, I get an ORA-00900:invalid SQL statement error. The exact same code runs in other IDEs but not database explorer. I’m unable to post the specific block but it consists of a cursor and a dbms_output.put_line within a loop. Any thoughts.

February 9, 2006 at 1:36 pm #246446 Reply

Riyad Kalla
Member

Can you please post all the information we request in the [URL=http://www.myeclipseide.com/PNphpBB2+file-viewtopic-t-393.html]Posting Guidelines[/URL] thread at the top of this forum? That will give us some context so that we can determine if this is an installation issue, a configuration problem, or a bug. Thanks.

February 9, 2006 at 3:14 pm #246462 Reply

bgblanch
Member

*** Date: Thu Feb 09 14:02:51 MST 2006

*** System properties:
OS=WindowsXP
OS version=5.1
Java version=1.5.0_06

*** MyEclipse details:
MyEclipse Enterprise Workbench

Version: 4.1.0 GA
Build id: 20060122-4.1-GA

*** Eclipse details:
Eclipse SDK

Version: 3.1.0
Build id: I20050627-1435

Eclipse Java Development Tools

Version: 3.1.0
Build id: I20050627-1435

Eclipse Project SDK

Version: 3.1.0
Build id: I20050627-1435

Eclipse Graphical Editing Framework

Version: 3.1
Build id: 200509301327

Eclipse RCP

Version: 3.1.0
Build id: I20050627-1435

Eclipse Plug-in Development Environment

Version: 3.1.0
Build id: I20050627-1435

External plug-ins
PHPEclipse
JSEclipse

Error Log (some info omitted)
!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.132
!MESSAGE Error processing query: ‘DECLARE
–This will get the title, page id, site id, and path to the document for
— documents the user is subscribed to.
—
–Need to figure out how to get the path for a bread crumb trail
v_title VARCHAR2(100)’
!STACK 0
java.sql.SQLException: ORA-06550: line 6, column 23:
PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following:

:= ; not null default character

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.132
!MESSAGE Error processing query: ‘
v_pageid NUMBER’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.132
!MESSAGE Error processing query: ‘
v_siteid NUMBER’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.148
!MESSAGE Error processing query: ‘
v_path VARCHAR2(300)’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.148
!MESSAGE Error processing query: ‘
CURSOR userDocSubs IS
SELECT [omitted]
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.148
!MESSAGE Error processing query: ‘
BEGIN
OPEN userDocSubs’
!STACK 0
java.sql.SQLException: ORA-06550: line 3, column 19:
PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following:

. ( % ; for

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.164
!MESSAGE Error processing query: ‘
LOOP
FETCH userDocSubs INTO v_title, v_pageid, v_siteid, v_path’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.164
!MESSAGE Error processing query: ‘
EXIT WHEN userDocSubs%NOTFOUND’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.164
!MESSAGE Error processing query: ‘
dbms_output.put_line(v_title || ‘ ‘ || v_pageid || ‘/’ || v_siteid || ‘ ‘ || v_path)’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.164
!MESSAGE Error processing query: ‘

END LOOP’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.164
!MESSAGE Error processing query: ‘
CLOSE userDocSubs’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-02-09 14:07:40.179
!MESSAGE Error processing query: ‘
END’
!STACK 0
java.sql.SQLException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.processQuery(SqlExecProgress.java:204)
at com.genuitec.eclipse.sqlexplorer.views.SqlExecProgress.run(SqlExecProgress.java:161)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

February 10, 2006 at 12:03 am #246496 Reply

Brian Fernandes
Moderator

bgblanch,

Support for anonymous PL/SQL blocks has been rolled into 4.1.1 already. Sorry for the inconvenience.

Could you give us a template or mock up of the query you wish to run as a block which we can use to internally test this?

Best,
Brian.

February 16, 2006 at 11:49 am #246891 Reply

bgblanch
Member

I’m using 4.1.0 so when 4.1.1 becomes a stable release I’ll upgrade and try it then.

February 16, 2006 at 11:55 am #246892 Reply

bgblanch
Member

I’m having another problem now. I’m using large VARCHAR2s in my PL/SQL Package (VARCHAR2(32700)). When I try to run the “CREATE OR REPLACE PACKAGE BODY…” I get a setString can only process strings of less than 32766 characters. Since my largest VARCHAR2 is 32700, why am I getting this error? The only reference that I have to setString in the package body is in a function header comment (cs.setString(3, msg2)). Since it’s in a comment, it shouldn’t matter to the code? I tried erasing the comment but still get the error. I can paste the package body into a sqlplus session and run it without any problems.

February 17, 2006 at 1:10 am #246926 Reply

Brian Fernandes
Moderator

bgblanch,

I tried to reproduce this locally but could not.
Could you post a minimal PL/SQL block here which exhibits the problem?

Best,
Brian.

February 17, 2006 at 7:52 am #246935 Reply

bgblanch
Member

Here is the code. I had to replace a number of things with “*”s to protect myself.

CREATE OR REPLACE PACKAGE BODY PIMS.MAIL_NOTIFICATIONS AS
v_from VARCHAR2(40) := ‘*******@*****.com’;
v_****_devel VARCHAR2(40) := ‘******.******@*****.com’;
v_cdl_help VARCHAR2(40) := ‘*******@*****.com’;
v_****_url VARCHAR2(100) := ‘*************************************************’;
v_testing BOOLEAN := TRUE; –Set to FALSE when in testing/production
v_errormail_buffer VARCHAR2(300) :=
‘x-sender: ‘ || v_from || ‘\nx-receiver: ‘|| v_from || ‘\nx-receiver: ‘ || v_****_devel || ‘\nFrom: ‘ || v_from || ‘\nTo: ‘ || v_****_devel || ‘, ‘ || v_cdl_help || ‘\nSubject: ERROR: ****.send_comment_resolution_mail\n\n’;

/***************************************************************************
* This function is used by the Comment Resolution webApp to write emails
* to the database for use in the nightly batch job
*
* Here is an example of how to call this function from java
*
* ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( “***************”, conn );
* ARRAY array_to_pass = new ARRAY( descriptor, conn, ids );
*
* cs = conn.prepareCall(“begin ? := ****.mail_notifications.send_immediate(?,?,?); end;”);
* cs.registerOutParameter(1,OracleTypes.VARCHAR);
* cs.setArray(2,array_to_pass);
* cs.setString(3, msg2);
* cs.setString(4, testUrl2);
*
* cs.execute();
*
***************************************************************************/
FUNCTION add_email(p_userid IN a_user_id_array, p_message IN VARCHAR2, p_url IN VARCHAR2) RETURN VARCHAR2 AS
v_message VARCHAR2(4000);
v_url VARCHAR2(300);
v_formatted_message VARCHAR2(4000);
v_maxmsgval NUMBER;
v_maxemailval NUMBER;
missing_input EXCEPTION;
BEGIN
–Ensure that no null values were passed into the procedure
IF (p_userid IS NOT NULL AND p_message IS NOT NULL) THEN

v_message := p_message;
v_url := p_url;

–Replace all single quotes with two single quotes
v_formatted_message := REPLACE(v_message, ””, ”””);

–Get the nextval from the msgidsequence for the email_message pk
SELECT ****.msgidsequence.NEXTVAL INTO v_maxmsgval FROM dual;

–Insert the values into email_message
INSERT INTO ****.email_message VALUES
(
v_maxmsgval, SYSDATE, v_formatted_message, v_url, 1
);

FOR i IN 1..p_userid.COUNT LOOP
–Get the nextval from the emailidsequence for the email_recipients pk
SELECT ****.emailidsequence.NEXTVAL INTO v_maxemailval FROM dual;

–Insert the values into email_recipients
INSERT INTO ****.email_recipients VALUES
(
v_maxemailval, p_userid(i), v_maxmsgval, 0
);
END LOOP;
RETURN ‘Insert Successful’;
ELSE
raise_application_error(-20000,’Missing input parameter’);
END IF;

–Commit the transaction
COMMIT;
EXCEPTION
WHEN missing_input THEN
RETURN ‘Invalid Input’;
WHEN NO_DATA_FOUND THEN
RETURN ‘Invalid Input’;
WHEN OTHERS THEN
RETURN ‘Unhandled Exception in PL/SQL Function ****.mail_notifications.add_email’;
END;

/***************************************************************************
* This function is used by the Comment Resolution webApp to write emails
* to the database for use in the nightly batch job
*
* This is the same as add_email above (overloaded) except that it takes in
* one userid rather than an array type.
*
***************************************************************************/
FUNCTION add_email(p_userid IN NUMBER, p_message IN VARCHAR2, p_url IN VARCHAR2) RETURN VARCHAR2 AS
v_message VARCHAR2(4000);
v_url VARCHAR2(300);
v_formatted_message VARCHAR2(4000);
v_maxmsgval NUMBER;
v_maxemailval NUMBER;
missing_input EXCEPTION;
BEGIN
–Ensure that no null values were passed into the procedure
IF (p_userid IS NOT NULL AND p_message IS NOT NULL) THEN

v_message := p_message;
v_url := p_url;

–Replace all single quotes with two single quotes
v_formatted_message := REPLACE(v_message, ””, ”””);

–Get the nextval from the msgidsequence for the email_message pk
SELECT ****.msgidsequence.NEXTVAL INTO v_maxmsgval FROM dual;

–Insert the values into email_message
INSERT INTO ****.email_message VALUES
(
v_maxmsgval, SYSDATE, v_formatted_message, v_url, 1
);

–Get the nextval from the emailidsequence for the email_recipients pk
SELECT ****.emailidsequence.NEXTVAL INTO v_maxemailval FROM dual;

–Insert the values into email_recipients
INSERT INTO ****.email_recipients VALUES
(
v_maxemailval, p_userid, v_maxmsgval, 0
);

RETURN ‘Insert Successful’;
ELSE
raise_application_error(-20000,’Missing input parameter’);
END IF;

–Commit the transaction
COMMIT;
EXCEPTION
WHEN missing_input THEN
RETURN ‘Invalid Input’;
WHEN NO_DATA_FOUND THEN
RETURN ‘Invalid Input’;
WHEN OTHERS THEN
RETURN ‘Unhandled Exception in PL/SQL Function ****.mail_notifications.add_email’;
END;

/***************************************************************************
* For use with Comment Resolution Application when an array of users is
* passed in
***************************************************************************/
FUNCTION send_immediate(p_userid IN a_user_id_array, p_message IN VARCHAR2, p_url IN VARCHAR2) RETURN VARCHAR2 AS
v_message VARCHAR2(20000);
v_url VARCHAR2(300);
v_message_buffer VARCHAR2(32700);
v_header_buffer VARCHAR2(32700);
v_formatted_message VARCHAR2(30000);
v_add_email_return VARCHAR2(300);
v_success VARCHAR2(300);
missing_input EXCEPTION;
v_firstname ****.VW_CONTACTS_EXPANDED.firstname%TYPE;
v_lastname ****.VW_CONTACTS_EXPANDED.lastname%TYPE;
v_user_email ****.VW_CONTACTS_EXPANDED.email%TYPE;
v_username ****.VW_CONTACTS_EXPANDED.user_name%TYPE;
f_log_file UTL_FILE.FILE_TYPE;
BEGIN
–Ensure that no null values were passed into the procedure
IF (p_userid IS NOT NULL AND p_message IS NOT NULL) THEN

–****.synchronize_security_tables.sync_******_sec_tables(1);

v_message := p_message;
IF(p_url IS NOT NULL) THEN
v_url := p_url;
END IF;

–Replace all single quotes with two single quotes
v_formatted_message := REPLACE(v_message, ””, ”””);

FOR i IN 1..p_userid.COUNT LOOP
SELECT firstname, lastname, email, user_name
INTO v_firstname, v_lastname, v_user_email, v_username
FROM ****.VW_CONTACTS_EXPANDED
WHERE id = p_userid(i);

–Create the header information for the current user
v_header_buffer := v_header_buffer || ‘x-sender: ‘ || v_from || ‘\n’;
IF(v_testing) THEN
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_****_devel || ‘\n’;
ELSE
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_user_email || ‘\n’;
END IF;
v_header_buffer := v_header_buffer || ‘From: ‘ || v_from || ‘\n’;
v_header_buffer := v_header_buffer || ‘To: ‘|| v_user_email || ‘\n’;
v_header_buffer := v_header_buffer || ‘Subject: New **** Comment Resolution Notification\n\n’;

–Set up the email information
v_message_buffer := v_message_buffer || v_lastname || ‘, ‘ || v_firstname || ‘\n\n’;

IF(v_url IS NOT NULL) THEN
v_message_buffer := v_message_buffer || v_message || ‘ ‘ || v_url || ‘\n\n’;
ELSE
v_message_buffer := v_message_buffer || v_message || ‘\n\n’;
END IF;

v_success := send_email_message(v_username, v_header_buffer, v_message_buffer);

–If email is sent successfully, write the information to the email_archive
–table for a permanent record of the email.
IF(v_success = ‘success’) THEN
INSERT INTO ****.email_archive VALUES(p_userid(i),0,SYSDATE,v_message,v_url,2);
ELSE
–Open the email file for write only
f_log_file := utl_file.fopen(
location => ‘EMAILNOTIFICATION_LOG_DIR’,
filename => ’email_log_file.txt’,
open_mode => ‘A’);

–Write the log info to the file
utl_file.putf(f_log_file, TO_CHAR(SYSTIMESTAMP, ‘DD-Mon-YYYY_HH24.MI.SS’) ||
‘ – Unable to write email to ediws, adding to batch job\n’ ||
v_lastname || ‘, ‘ || v_firstname || ‘\n’ || v_message || ‘\n\n’);

–Close the file
utl_file.fclose(f_log_file);

v_add_email_return := add_email(p_userid(i), v_message, v_url);
END IF;

–Empty out the buffers;
v_header_buffer := NULL;
v_message_buffer := NULL;
END LOOP;
IF(v_success = ‘success’) THEN
RETURN ‘Successfully Sent Email’;
ELSE
RETURN ‘Unsuccessful’;
END IF;
ELSE
raise_application_error(-20000,’Missing input parameter’);
END IF;

EXCEPTION
WHEN missing_input THEN
RETURN ‘Invalid Input’;
WHEN NO_DATA_FOUND THEN
RETURN ‘Invalid Input’;
WHEN OTHERS THEN
RETURN ‘Unhandled Exception in PL/SQL Function ****.mail_notifications.send_immediate’;
END;

/***************************************************************************
* For general use when an array of users is passed in
***************************************************************************/
FUNCTION send_immediate(p_userid IN a_user_id_array, p_message IN VARCHAR2) RETURN VARCHAR2 AS
v_message VARCHAR2(20000);
v_message_buffer VARCHAR2(32700);
v_header_buffer VARCHAR2(32700);
v_formatted_message VARCHAR2(30000);
v_add_email_return VARCHAR2(300);
v_success VARCHAR2(300);
missing_input EXCEPTION;
v_firstname ****.VW_CONTACTS_EXPANDED.firstname%TYPE;
v_lastname ****.VW_CONTACTS_EXPANDED.lastname%TYPE;
v_user_email ****.VW_CONTACTS_EXPANDED.email%TYPE;
v_username ****.VW_CONTACTS_EXPANDED.user_name%TYPE;
f_log_file UTL_FILE.FILE_TYPE;
BEGIN
–Ensure that no null values were passed into the procedure
IF (p_userid IS NOT NULL AND p_message IS NOT NULL) THEN

–****.synchronize_security_tables.sync_******_sec_tables(1);

v_message := p_message;

–Replace all single quotes with two single quotes
v_formatted_message := REPLACE(v_message, ””, ”””);

FOR i IN 1..p_userid.COUNT LOOP
SELECT firstname, lastname, email, user_name
INTO v_firstname, v_lastname, v_user_email, v_username
FROM ****.VW_CONTACTS_EXPANDED
WHERE id = p_userid(i);

–Create the header information for the current user
v_header_buffer := v_header_buffer || ‘x-sender: ‘ || v_from || ‘\n’;
IF(v_testing) THEN
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_****_devel || ‘\n’;
ELSE
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_user_email || ‘\n’;
END IF;
v_header_buffer := v_header_buffer || ‘From: ‘ || v_from || ‘\n’;
v_header_buffer := v_header_buffer || ‘To: ‘|| v_user_email || ‘\n’;
v_header_buffer := v_header_buffer || ‘Subject: New **** Comment Resolution Notification\n\n’;

–Set up the email information
v_message_buffer := v_message_buffer || v_lastname || ‘, ‘ || v_firstname || ‘\n\n’;

v_message_buffer := v_message_buffer || v_message || ‘\n\n’;

v_success := send_email_message(v_username, v_header_buffer, v_message_buffer);

–If email is sent successfully, write the information to the email_archive
–table for a permanent record of the email.
IF(v_success = ‘success’) THEN
INSERT INTO ****.email_archive VALUES(p_userid(i),0,SYSDATE,v_message,’No URL’,2);
ELSE
–Open the email file for write only
f_log_file := utl_file.fopen(
location => ‘EMAILNOTIFICATION_LOG_DIR’,
filename => ’email_log_file.txt’,
open_mode => ‘A’);

–Write the log info to the file
utl_file.putf(f_log_file, TO_CHAR(SYSTIMESTAMP, ‘DD-Mon-YYYY_HH24.MI.SS’) ||
‘ – Unable to write email to ediws, adding to batch job\n’ ||
v_lastname || ‘, ‘ || v_firstname || ‘\n’ || v_message || ‘\n\n’);

–Close the file
utl_file.fclose(f_log_file);

v_add_email_return := add_email(p_userid(i), v_message, NULL);
END IF;

–Empty out the buffers;
v_header_buffer := NULL;
v_message_buffer := NULL;
END LOOP;
IF(v_success = ‘success’) THEN
RETURN ‘Successfully Sent Email’;
ELSE
RETURN ‘Unsuccessful’;
END IF;
ELSE
raise_application_error(-20000,’Missing input parameter’);
END IF;

EXCEPTION
WHEN missing_input THEN
RETURN ‘Invalid Input’;
WHEN NO_DATA_FOUND THEN
RETURN ‘Invalid Input’;
WHEN OTHERS THEN
RETURN ‘Unhandled Exception in PL/SQL Function ****.mail_notifications.send_immediate’;
END;

/***************************************************************************
* For use with Comment Resolution (or other app) when a single user
* id is passed in
***************************************************************************/
FUNCTION send_immediate(p_userid IN NUMBER, p_message IN VARCHAR2, p_url IN VARCHAR2) RETURN VARCHAR2 AS
v_message VARCHAR2(20000);
v_url VARCHAR2(300);
v_message_buffer VARCHAR2(32700);
v_header_buffer VARCHAR2(32700);
v_formatted_message VARCHAR2(30000);
v_add_email_return VARCHAR2(300);
v_success VARCHAR2(300);
missing_input EXCEPTION;
v_firstname ****.VW_CONTACTS_EXPANDED.firstname%TYPE;
v_lastname ****.VW_CONTACTS_EXPANDED.lastname%TYPE;
v_user_email ****.VW_CONTACTS_EXPANDED.email%TYPE;
v_username ****.VW_CONTACTS_EXPANDED.user_name%TYPE;
f_log_file UTL_FILE.FILE_TYPE;
BEGIN
–Ensure that no null values were passed into the procedure
IF (p_userid IS NOT NULL AND p_message IS NOT NULL AND p_url IS NOT NULL) THEN

–****.synchronize_security_tables.sync_******_sec_tables(1);

v_message := p_message;
IF(p_url IS NOT NULL) THEN
v_url := p_url;
END IF;

–Replace all single quotes with two single quotes
v_formatted_message := REPLACE(v_message, ””, ”””);

SELECT firstname, lastname, email, user_name
INTO v_firstname, v_lastname, v_user_email, v_username
FROM ****.VW_CONTACTS_EXPANDED
WHERE id = p_userid;

–Create the header information for the current user
v_header_buffer := v_header_buffer || ‘x-sender: ‘ || v_from || ‘\n’;
IF(v_testing) THEN
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_****_devel || ‘\n’;
ELSE
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_user_email || ‘\n’;
END IF;
v_header_buffer := v_header_buffer || ‘From: ‘ || v_from || ‘\n’;
v_header_buffer := v_header_buffer || ‘To: ‘|| v_user_email || ‘\n’;
v_header_buffer := v_header_buffer || ‘Subject: New **** Comment Resolution Notification\n\n’;

–Set up the email information
v_message_buffer := v_message_buffer || v_lastname || ‘, ‘ || v_firstname || ‘\n\n’;

IF(v_url IS NOT NULL) THEN
v_message_buffer := v_message_buffer || v_message || ‘ ‘ || v_url || ‘\n\n’;
ELSE
v_message_buffer := v_message_buffer || v_message || ‘\n\n’;
END IF;

v_success := send_email_message(v_username, v_header_buffer, v_message_buffer);

–If email is sent successfully, write the information to the email_archive
–table for a permanent record of the email.
IF(v_success = ‘success’) THEN
INSERT INTO ****.email_archive VALUES(p_userid,0,SYSDATE,v_message,v_url,2);
ELSE
–Open the email file for write only
f_log_file := utl_file.fopen(
location => ‘EMAILNOTIFICATION_LOG_DIR’,
filename => ’email_log_file.txt’,
open_mode => ‘A’);

–Write the log info to the file
utl_file.putf(f_log_file, TO_CHAR(SYSTIMESTAMP, ‘DD-Mon-YYYY_HH24.MI.SS’) ||
‘ – Unable to write email to ediws, adding to batch job\n’ ||
v_lastname || ‘, ‘ || v_firstname || ‘\n’ || v_message || ‘\n\n’);

–Close the file
utl_file.fclose(f_log_file);

v_add_email_return := add_email(p_userid, v_message, v_url);
END IF;
IF(v_success = ‘success’) THEN
RETURN ‘Successfully Sent Email’;
ELSE
RETURN ‘Unsuccessful’;
END IF;
ELSE
raise_application_error(-20000,’Missing input parameter’);
END IF;

EXCEPTION
WHEN missing_input THEN
RETURN ‘Invalid Input’;
WHEN NO_DATA_FOUND THEN
RETURN ‘Invalid Input’;
WHEN OTHERS THEN
RETURN ‘Unhandled Exception in PL/SQL Function ****.mail_notifications.send_immediate’;
END;

/***************************************************************************
* For general use to send a message to a user
* We are unable to use smtp_utl due to the current setup of the servers
***************************************************************************/
FUNCTION send_immediate(p_userid IN NUMBER, p_message IN VARCHAR2) RETURN VARCHAR2 AS
v_message VARCHAR2(20000);
v_message_buffer VARCHAR2(32700);
v_header_buffer VARCHAR2(32700);
v_formatted_message VARCHAR2(30000);
v_add_email_return VARCHAR2(300);
v_success VARCHAR2(300);
missing_input EXCEPTION;
v_firstname ****.VW_CONTACTS_EXPANDED.firstname%TYPE;
v_lastname ****.VW_CONTACTS_EXPANDED.lastname%TYPE;
v_user_email ****.VW_CONTACTS_EXPANDED.email%TYPE;
v_username ****.VW_CONTACTS_EXPANDED.user_name%TYPE;
f_log_file UTL_FILE.FILE_TYPE;
BEGIN
–Ensure that no null values were passed into the procedure
IF (p_userid IS NOT NULL AND p_message IS NOT NULL) THEN

–****.synchronize_security_tables.sync_******_sec_tables(1);

v_message := p_message;

–Replace all single quotes with two single quotes
v_formatted_message := REPLACE(v_message, ””, ”””);

SELECT firstname, lastname, email, user_name
INTO v_firstname, v_lastname, v_user_email, v_username
FROM ****.VW_CONTACTS_EXPANDED
WHERE id = p_userid;

–Create the header information for the current user
v_header_buffer := v_header_buffer || ‘x-sender: ‘ || v_from || ‘\n’;
IF(v_testing) THEN
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_****_devel || ‘\n’;
ELSE
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_user_email || ‘\n’;
END IF;
v_header_buffer := v_header_buffer || ‘From: ‘ || v_from || ‘\n’;
v_header_buffer := v_header_buffer || ‘To: ‘|| v_user_email || ‘\n’;
v_header_buffer := v_header_buffer || ‘Subject: New **** Comment Resolution Notification\n\n’;

–Set up the email information
v_message_buffer := v_message_buffer || v_lastname || ‘, ‘ || v_firstname || ‘\n\n’;
v_message_buffer := v_message_buffer || v_message || ‘\n\n’;

v_success := send_email_message(v_username, v_header_buffer, v_message_buffer);

–If email is sent successfully, write the information to the email_archive
–table for a permanent record of the email.
IF(v_success = ‘success’) THEN
INSERT INTO ****.email_archive VALUES(p_userid,0,SYSDATE,v_message,’No URL’,2);
ELSE
–Open the email file for write only
f_log_file := utl_file.fopen(
location => ‘EMAILNOTIFICATION_LOG_DIR’,
filename => ’email_log_file.txt’,
open_mode => ‘A’);

–Write the log info to the file
utl_file.putf(f_log_file, TO_CHAR(SYSTIMESTAMP, ‘DD-Mon-YYYY_HH24.MI.SS’) ||
‘ – Unable to write email to ediws, adding to batch job\n’ ||
v_lastname || ‘, ‘ || v_firstname || ‘\n’ || v_message || ‘\n\n’);

–Close the file
utl_file.fclose(f_log_file);

v_add_email_return := add_email(p_userid, v_message, NULL);
END IF;

–Empty out the buffers;
v_header_buffer := NULL;
v_message_buffer := NULL;

IF(v_success = ‘success’) THEN
RETURN ‘Successfully Sent Email’;
ELSE
RETURN ‘Unsuccessful’;
END IF;
ELSE
raise_application_error(-20000,’Missing input parameter’);
END IF;
EXCEPTION
WHEN missing_input THEN
RETURN ‘Invalid Input’;
WHEN NO_DATA_FOUND THEN
RETURN ‘Invalid Input’;
WHEN OTHERS THEN
RETURN ‘Unhandled Exception in PL/SQL Function ****.mail_notifications.send_immediate’;
END;

/***************************************************************************
* This procedure is used as a batch job to send emails to all users with
* emails generated by the Comment Resolution webApp and written to the
* database by add_email
***************************************************************************/
PROCEDURE send_comment_resolution_mail AS
v_header_buffer VARCHAR2(1000);
v_cdrl_buffer VARCHAR2(32700);
v_comment_buffer VARCHAR2(32700);
v_error_buffer VARCHAR2(32700);
v_log_buffer VARCHAR2(32700);
v_reminder_buffer VARCHAR2(32700);
v_hasMail BOOLEAN := false;
v_has_info NUMBER;
v_cur_user NUMBER;
v_sent_mail VARCHAR2(4000);
l_string VARCHAR2(32700);
v_send_success VARCHAR2(300);
v_log_success VARCHAR2(300) := ”;
–To get the users
v_firstname ****.VW_CONTACTS_EXPANDED.firstname%TYPE;
v_lastname ****.VW_CONTACTS_EXPANDED.lastname%TYPE;
v_user_email ****.VW_CONTACTS_EXPANDED.email%TYPE;
v_username ****.VW_CONTACTS_EXPANDED.user_name%TYPE;
–For Comment Resolution
v_msgid ****.VW_EMAIL.msg_id%TYPE;
v_message ****.VW_EMAIL.message%TYPE;
v_url ****.VW_EMAIL.url%TYPE;
v_cr_count NUMBER;
v_vals DBMS_LDAP.STRING_COLLECTION;
v_i PLS_INTEGER;
TYPE userIdTable IS TABLE OF NUMBER;
userTable userIdTable;
CURSOR getUserCursor IS
SELECT DISTINCT fk_userid FROM ****.email_recipients;
CURSOR getComments(inUserId IN NUMBER) IS
SELECT msg_id, message, url FROM ****.vw_email WHERE userid = inUserId;
BEGIN
v_log_buffer := v_log_buffer || ‘Calling ****.synchronize_security_tables.sync_******_sec_tables(1)\n’;
****.synchronize_security_tables.sync_******_sec_tables(1);

v_log_buffer := v_log_buffer || ‘Getting users with subscriptions or comments\n’;
–Get the users with subscriptions or comments
OPEN getUserCursor;
LOOP
FETCH getUserCursor INTO v_cur_user;
EXIT WHEN getUserCursor%NOTFOUND;

v_log_buffer := v_log_buffer || ‘Compiling email for: ‘ || v_cur_user || ‘\n’;

SELECT COUNT(*) INTO v_has_info FROM ****.VW_CONTACTS_EXPANDED WHERE id = v_cur_user;
IF(v_has_info > 0) THEN
–Get the personal info for the user
SELECT firstname, lastname, email, user_name
INTO v_firstname, v_lastname, v_user_email, v_username
FROM ****.VW_CONTACTS_EXPANDED
WHERE id = v_cur_user;

v_log_buffer := v_log_buffer || v_firstname || ‘ ‘ || v_lastname || ‘: ‘ || v_user_email || ‘\n’;

–Create the header information for the current user
v_header_buffer := v_header_buffer || ‘x-sender: ‘ || v_from || ‘\n’;
IF(v_testing) THEN
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_****_devel || ‘\n’;
ELSE
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_user_email || ‘\n’;
END IF;
v_header_buffer := v_header_buffer || ‘From: ‘ || v_from || ‘\n’;
v_header_buffer := v_header_buffer || ‘To: ‘|| v_user_email || ‘\n’;
v_header_buffer := v_header_buffer || ‘Subject: New **** Comment Resolution Notification\n\n’;

–Set up the email information
v_header_buffer := v_header_buffer || v_lastname || ‘, ‘ || v_firstname || ‘\n\n’;

–Check to see if the current user has any comments
select count(*) into v_cr_count from ****.vw_email where userid = v_cur_user;

–v_temp_buffer := getReminders(v_cur_user);

OPEN getComments(v_cur_user);
IF(v_cr_count > 0) THEN
–Get the Comment Resolution Notifications
v_comment_buffer := v_comment_buffer || ‘The following notifications have been created for you by the Comment Resolution system:\n\n’;

v_hasMail := TRUE;
LOOP
v_url := null;
FETCH getComments INTO v_msgid, v_message, v_url;
EXIT WHEN getComments%NOTFOUND;

–Set the has mail flag
v_hasMail := true;

v_log_buffer := v_log_buffer || ‘Adding Comment Resolution Notification Message: ‘ || v_msgid || ‘\n’;
v_comment_buffer := v_comment_buffer || v_message || ‘ ‘ || v_url || ‘\n\n’;

END LOOP;
ELSE
v_log_buffer := v_log_buffer || ‘No New Comment Resolution Notifications\n\n’;
END IF;
CLOSE getComments;

–If the user has mail, then try to send the email
IF(v_hasMail) THEN
v_send_success := send_email_message(v_username, v_header_buffer, v_comment_buffer);
IF(v_send_success = ‘success’) THEN
v_log_buffer := v_log_buffer || ‘Email Sent Successfully\n\n’;
–Change the sent flag to indicate the notification was sent successfully
IF((v_testing = FALSE) AND (v_msgid != -1)) THEN
update ****.email_recipients set sent_flag = 1 where fk_userid = v_cur_user and fk_msgid = v_msgid;
v_log_buffer := v_log_buffer || ‘Updating sent_flag for message #: ‘ || v_msgid || ‘\n’;
END IF;
ELSE
v_log_buffer := v_log_buffer || ‘\nError sending email to: ‘ || v_firstname || ‘ ‘ || v_lastname || ‘: ‘ || v_user_email || ‘\n’ || v_send_success || ‘\n\n’;
v_error_buffer := v_error_buffer || ‘Error sending email to: ‘ || v_firstname || ‘ ‘ || v_lastname || ‘: ‘ || v_user_email || ‘\n’ || v_send_success || ‘\n\n’;
END IF;
END IF;

–Empty out the buffers
v_header_buffer := NULL;
v_comment_buffer := NULL;
v_firstname := NULL;
v_lastname := NULL;
v_user_email := NULL;
v_username := NULL;
v_hasMail := false;
END IF;
END LOOP;
CLOSE getUserCursor;

v_log_buffer := v_log_buffer || ‘\n\nFinished Successfully\n’;

–Write the log buffer to the log file
v_log_success := write_email_log(v_log_buffer);

–Send an email with the error message if there is a problem
IF(v_log_success != ‘success’ OR v_error_buffer IS NOT NULL) THEN
v_error_buffer := v_error_buffer || ‘\n’ || v_log_success;
v_send_success := send_email_message(‘errormail’, v_errormail_buffer, v_error_buffer);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_log_success := write_email_log(v_log_buffer);
END;

FUNCTION get_initiate_review_reminder(p_userid IN NUMBER) RETURN VARCHAR2 AS
v_reminder ****.REVIEWABLE_ITEM.send_init_review_reminders%TYPE;
v_itemid ****.REVIEWABLE_ITEM.ITEM_ID%TYPE;
v_doc ****.REVIEWABLE_ITEM.DOC_NUMBER%TYPE;
v_return_buffer VARCHAR2(4000);
CURSOR isGDM IS
SELECT i.item_id, r.doc_number
FROM ****.ITEM_GOVT_DOC_MGRS i, ****.REVIEWABLE_ITEM r
WHERE cid = p_userid
AND i.item_id = r.item_id;
BEGIN
OPEN isGDM;
LOOP
FETCH isGDM INTO v_itemid, v_doc;
EXIT WHEN isGDM%NOTFOUND;

IF(v_itemid IS NOT NULL) THEN
–Check to see if the reminder is turned on
SELECT send_init_review_reminders INTO v_reminder FROM ****.REVIEWABLE_ITEM WHERE item_id = v_itemid;

IF(v_reminder IS NOT NULL AND v_reminder = 1) THEN
RETURN ‘Please initiate the review on ‘ || v_doc || ‘\n\n’;
ELSE
RETURN NULL;
END IF;
END IF;
END LOOP;
CLOSE isGDM;
END;

FUNCTION get_review_end_reminder(p_userid IN NUMBER) RETURN VARCHAR2 AS
v_reminder ****.REVIEWABLE_ITEM.send_init_review_reminders%TYPE;
v_end_date ****.ITEM_REVIEWER.REVIEW_END_DATE%TYPE;
v_start_date ****.ITEM_REVIEWER.review_start_date%TYPE;
v_reminder_time ****.ITEM_REVIEWER.end_date_reminder_freq%TYPE;
v_doc ****.REVIEWABLE_ITEM.DOC_NUMBER%TYPE;
v_itemid ****.REVIEWABLE_ITEM.ITEM_ID%TYPE;
v_reminder_type ****.ITEM_REVIEWER.end_date_reminder_type%TYPE;
v_comments NUMBER;
CURSOR isGDMReviewer IS
SELECT item_id
FROM ****.ITEM_REVIEWER
WHERE id = p_userid
AND parent_id = 0;
BEGIN
OPEN isGDMReviewer;
LOOP
FETCH isGDMReviewer INTO v_itemid;
EXIT WHEN isGDMReviewer%NOTFOUND;

SELECT r.send_end_date_reminders, r.review_end_date, r.review_start_date, r.end_date_reminder_freq, i.doc_number, r.end_date_reminder_type
INTO v_reminder, v_end_date, v_start_date, v_reminder_time, v_doc, v_reminder_type
FROM ****.ITEM_REVIEWER r, ****.REVIEWABLE_ITEM i
WHERE r.id = p_userid
AND r.item_id = v_itemid
AND r.item_id = i.item_id;

–Check to see if they’ve made any comments on the current item
SELECT COUNT(*) INTO v_comments FROM ****.ITEM_COMMENT WHERE item_id = v_itemid AND reviewer_id = p_userid;

–If the user hasn’t made any comments on the current item and the reminder is turned on
IF(v_comments <= 0 AND v_reminder IS NOT NULL AND v_reminder = 1) THEN
–If the reminder type is “repeating every X days”
IF(v_reminder_type = 1) THEN
–Check to see if it is a multiple of X days past the review start date
IF(MOD(TRUNC(SYSDATE) – TRUNC(v_start_date),v_reminder_time) = 0) THEN
RETURN v_reminder_time || ‘ days have past since your review date began. Please make any comments you feel necessary’;
ELSE
RETURN NULL;
END IF;
–v_reminder_type = 2 and reminder type is “X days before”
ELSE
–If the sysdate is greater than or equal to the review end date minus the X days before
IF(v_end_date IS NOT NULL AND (TRUNC(SYSDATE) >= (TRUNC(v_end_date) – v_reminder_time))) THEN
RETURN ‘Your review end date is nearing on ‘ || v_doc || ‘. Please make any comments you feel necessary’;
ELSE
RETURN NULL;
END IF;
END IF;
END IF;
END LOOP;
CLOSE isGDMReviewer;
END;

FUNCTION no_sub_reviewers(p_userid IN NUMBER) RETURN VARCHAR2 AS
v_reminder ****.ITEM_REVIEWER.send_add_subreviewer_reminders%TYPE;
v_has_reviewers NUMBER;
v_reminder_time ****.ITEM_REVIEWER.add_subreviewer_reminder_freq%TYPE;
v_doc ****.REVIEWABLE_ITEM.DOC_NUMBER%TYPE;
v_itemid ****.REVIEWABLE_ITEM.ITEM_ID%TYPE;
v_reminder_type ****.ITEM_REVIEWER.end_date_reminder_type%TYPE;
v_comments NUMBER;
CURSOR isGDMReviewer IS
SELECT item_id
FROM ****.ITEM_REVIEWER
WHERE id = p_userid
AND parent_id = 0;
BEGIN
OPEN isGDMReviewer;
LOOP
FETCH isGDMReviewer INTO v_itemid;
EXIT WHEN isGDMReviewer%NOTFOUND;

SELECT send_add_subreviewer_reminders
INTO v_reminder
FROM ****.ITEM_REVIEWER
WHERE id = p_userid
AND item_id = v_itemid;

–Check to see if the user has set up any subreviewers
SELECT COUNT(*) INTO v_has_reviewers FROM ****.ITEM_REVIEWER WHERE parent_id = p_userid AND item_id = v_itemid;

–If no sub-reviewers have been set up
IF(v_has_reviewers IS NOT NULL AND v_has_reviewers = 0) THEN
RETURN ‘Something’;
–If
ELSE
RETURN NULL;
END IF;
END LOOP;
CLOSE isGDMReviewer;

END;

/***************************************************************************
* This procedure is used as a batch job to send notifications of new/
* updated documents and CDRLs on pages that the user has subscribed to
***************************************************************************/
PROCEDURE send_batch_mail AS
v_header_buffer VARCHAR2(1000);
v_doc_buffer VARCHAR2(15000);
v_cdrl_buffer VARCHAR2(15000);
v_email_buffer VARCHAR2(32000);
v_log_buffer VARCHAR2(32700);
v_doclog_buffer VARCHAR2(10000);
v_cdrllog_buffer VARCHAR2(10000);
v_error_buffer VARCHAR2(32700);
v_hasDocs BOOLEAN := false;
v_hasCDRLs BOOLEAN := FALSE;
v_cur_user NUMBER;
v_docs_count NUMBER;
v_sent_mail VARCHAR2(4000);
l_string VARCHAR2(32700);
v_email_success VARCHAR2(300) := ”;
v_log_success VARCHAR2(300) := ”;
–To get the users
v_userid ****.VW_CONTACTS_EXPANDED.ID%TYPE;
v_firstname ****.VW_CONTACTS_EXPANDED.firstname%TYPE;
v_lastname ****.VW_CONTACTS_EXPANDED.lastname%TYPE;
v_user_email ****.VW_CONTACTS_EXPANDED.email%TYPE;
v_username ****.VW_CONTACTS_EXPANDED.user_name%TYPE;
–For Documents
v_title ******.wwpob_page$.title%TYPE;
v_page_id ******.WWSBR_SUBSCRIPTION$.page_id%TYPE;
v_site_id ******.WWSBR_SUBSCRIPTION$.object_context_id%TYPE;
v_path ******.WWDAV$PATH.path%TYPE;
–For CDRLs
v_cdrlnumber *********.TBLCDRL_UNNORM.cdrlnumber%TYPE;
v_reporttitle *********.TBLCDRL_UNNORM.reporttitle%TYPE;
v_cdrl *********.TBLCDRL_UNNORM.cdrl%TYPE;
v_filename *********.TBLCDRL_UNNORM.softcopyfilename%TYPE;
v_archived *********.TBLCDRL_UNNORM.softcopyarchived%TYPE;
v_url ****.VW_EMAIL.url%TYPE;
v_vals DBMS_LDAP.STRING_COLLECTION;
v_i PLS_INTEGER;
CURSOR getUsers IS
SELECT id, firstname, lastname, email, user_name
FROM ****.VW_CONTACTS_EXPANDED
WHERE ******_user = ‘Y’;
CURSOR getUserDocs(inUserId IN NUMBER) IS
SELECT g.title, s.page_id, s.object_context_id, av.path
FROM ******.wwsbr_subscription$ s, ****.VW_CONTACTS_EXPANDED p,
******.wwpob_page$ g, ******.wwdoc_document$ d,
******.wwpth_path_element$ e, ******.wwv_things t,
******.wwpob_item$ i, ******.wwdav$path av
WHERE s.page_id = g.id
and g.siteid = s.object_context_id
and t.cornerid = s.page_id
and t.itemtype in (‘basefile’, ‘basetext’, ‘baseurl’)
and t.updatedate > trunc(sysdate) – 1
and (t.expireMode = ‘PERMANENT’
or (t.expireMode = ‘DATE’ and t.expireDate > sysdate)
or (t.expireMode = ‘NUMBER’
and (t.expireNumber + trunc(t.publishDate) > trunc(sysdate))))
and t.active = 1
and t.masterthingid=i.master_thing_id and i.include = 1
and s.page_id = e.object_id
and s.object_context_id = e.object_context_id
and t.filename = d.name(+)
and t.iscurrentversion = 1
and not t.id in (SELECT item_id
FROM ******.wwsbr_item_approval$
where page_id = s.page_id
and site_id = s.object_context_id
and status <> 1)
and s.page_id = g.id
and p.id = s.recipient_id
and not p.email is null
and s.recipient_id = inUserId
and av.doc_id = d.dav_id
UNION
SELECT g.title, g.id, g.siteid, av.path
FROM ******.wwpob_page$ g, ******.wwdoc_document$ d,
******.wwpth_path_element$ e, ******.wwv_things t,
****.VW_CONTACTS_EXPANDED p, ******.wwpob_item$ i, ******.wwdav$path av
WHERE t.cornerid = g.id
and t.itemtype in (‘basefile’, ‘basetext’, ‘baseurl’)
and t.updatedate > trunc(sysdate) – 1
and (t.expireMode = ‘PERMANENT’
or (t.expireMode = ‘DATE’ and t.expireDate > sysdate)
or (t.expireMode = ‘NUMBER’
and (t.expireNumber + trunc(t.publishDate) > trunc(sysdate))))
and t.active = 1
and t.masterthingid=i.master_thing_id
and i.include = 1
and g.id = e.object_id
and g.siteid = e.object_context_id
and t.filename = d.name(+)
and t.iscurrentversion = 1
and not t.id in (SELECT item_id
FROM ******.wwsbr_item_approval$
WHERE page_id = g.id
and site_id = g.siteid and status <> 1)
and g.id not in (SELECT pageid
FROM ****.USER_SUBSCRIPTION_LOCKS
WHERE userid = inUserId)
and p.id = inUserId
and t.guid not in (SELECT itemid
FROM ****.user_notification_removal
WHERE userid = inUserId
and remove_date > trunc(sysdate)-1)
and g.id in (SELECT pageid
FROM ****.GROUP_SUBSCRIPTIONS
WHERE ****.subscribe_api.is_group_subscribed(inUserId, siteid, pageid) > 0 )
order by 3;
CURSOR getUserCdrls(inUserId IN NUMBER) IS
select x.cdrlnumber, x.reporttitle, x.cdrl, x.softcopyfilename, x.softcopyarchived
from *********.TBLCDRL_UNNORM x, ****.SERIES_SUBSCRIPTIONS s, ****.VW_CONTACTS_EXPANDED p
where s.userid = inUserId
and p.id = inUserId
and s.userid = p.id
and (x.cdrl = s.cdrl or x.view_id = s.view_id)
and x.cdrlnumber not in (select r.itemid from ****.USER_NOTIFICATION_REMOVAL r where r.userid = inUserId)
and x.datedelivered >= trunc(sysdate) – 1;
BEGIN
–Synchronize the ****** tables to ensure that all of the new users are in the ****** tables
–and deleted users aren’t in the ****** tables.
v_log_buffer := v_log_buffer || ‘Calling ****.synchronize_security_tables.sync_******_sec_tables(1)\n’;
****.synchronize_security_tables.sync_******_sec_tables(1);

–Get the users
OPEN getUsers;
LOOP
FETCH getUsers INTO v_cur_user, v_firstname, v_lastname, v_user_email, v_username;
EXIT WHEN getUsers%NOTFOUND;

–Create the header information for the current user
v_header_buffer := v_header_buffer || ‘x-sender: ‘ || v_from || ‘\n’;
IF(v_testing) THEN
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_****_devel || ‘\n’;
ELSE
v_header_buffer := v_header_buffer || ‘x-receiver: ‘ || v_user_email || ‘\n’;
END IF;
v_header_buffer := v_header_buffer || ‘From: ‘ || v_from || ‘\n’;
v_header_buffer := v_header_buffer || ‘To: ‘|| v_user_email || ‘\n’;
v_header_buffer := v_header_buffer || ‘Subject: New **** Notification (Do Not Reply To This Message)\n\n’;

–Set up the email information
v_header_buffer := v_header_buffer || v_lastname || ‘, ‘ || v_firstname || ‘\n\n’;

OPEN getUserDocs(v_cur_user);
LOOP
–g.title, s.page_id, s.object_context_id, av.path
FETCH getUserDocs INTO v_title, v_page_id, v_site_id, v_path;
EXIT WHEN getUserDocs%NOTFOUND;
v_hasDocs := TRUE;
v_path := REPLACE(v_path, ‘ ‘, ‘%20’);
–Add the information to the email buffer
v_doclog_buffer := v_doclog_buffer || ‘Adding Document: ‘ || v_title || ‘\n’;
v_doc_buffer := v_doc_buffer || v_title || ‘\n ‘ || v_****_url || v_path || ‘\n\n’;
END LOOP;
IF(v_hasDocs = TRUE) THEN
v_email_buffer := v_email_buffer || ‘NEW DOCUMENTS\n\n’ || v_doc_buffer;
END IF;
CLOSE getUserDocs;

OPEN getUserCdrls(v_cur_user);
–Get the CDRL Notifications
LOOP
v_url := NULL;
FETCH getUserCdrls INTO v_cdrlnumber, v_reporttitle, v_cdrl, v_filename, v_archived;
EXIT WHEN getUserCdrls%NOTFOUND;

–Set the has mail flag
v_hasCDRLs := TRUE;

IF((v_filename IS NOT NULL) OR (v_archived IS NOT NULL)) THEN
v_url := *********.rep_utl_api.CREATE_CDRL_URL(v_filename,v_cdrlnumber,v_archived,v_cur_user);
–Check to see if the user has permission to view the CDRL
IF(LOWER(SUBSTR(v_url, 1, 5)) != ‘https’) THEN
v_url := ‘RESTRICTED’;
END IF;
–If the filename is null or v_archived is not null, the item is rescinded
ELSE
v_url := ‘RESCINDED’;
END IF;

–Add the CDRL information to the log buffer
IF(v_url = ‘RESTRICTED’) THEN
v_cdrllog_buffer := v_cdrllog_buffer || ‘Adding CDRL: ‘ || v_cdrlnumber || ‘ ‘ || v_reporttitle || ‘ ‘ || v_url || ‘\n’;
ELSIF(v_url = ‘RESCINDED’) THEN
v_cdrllog_buffer := v_cdrllog_buffer || ‘Adding CDRL: ‘ || v_cdrlnumber || ‘ ‘ || v_reporttitle || ‘ ‘ || v_url || ‘\n’;
ELSE
v_cdrllog_buffer := v_cdrllog_buffer || ‘Adding CDRL: ‘ || v_cdrlnumber || ‘ ‘ || v_reporttitle || ‘\n’;
END IF;
v_cdrl_buffer := v_cdrl_buffer || v_cdrlnumber || ‘ ‘ || v_reporttitle || ‘ ‘ || v_cdrl || ‘ ‘ || v_filename || ‘ ‘ || v_url || ‘\n\n’;

END LOOP;
IF(v_hasCDRLs = TRUE) THEN
v_email_buffer := v_email_buffer || ‘NEW CDRLs\n\n’ || v_cdrl_buffer;
END IF;
CLOSE getUserCdrls;

IF(v_hasCDRLs OR v_hasDocs) THEN
v_log_buffer := v_log_buffer || ‘Compiling email for: ‘ || v_cur_user || ‘\n’ ||
v_firstname || ‘ ‘ || v_lastname || ‘: ‘ || v_user_email || ‘\n’ || v_doclog_buffer || v_cdrllog_buffer;
v_email_buffer := ‘One or more new items have been placed ‘ ||
‘on the SCNC **** page(s) you have subscribed to. To view the item(s), ‘ ||
‘click on the link(s) below. The items will also be listed in the ‘ ||
‘Subscription region of the main page.\n\n’ || v_email_buffer;
v_email_buffer := v_email_buffer || ‘Reminder: Items stay in the Subscription ‘ ||
‘section on the page for seven(7) days, or until you remove them.\n\n’;
v_email_success := send_email_message(v_username, v_header_buffer, v_email_buffer);
IF(v_email_success = ‘success’) THEN
v_log_buffer := v_log_buffer || ‘Email Sent Successfully\n\n’;
ELSE
v_log_buffer := v_log_buffer || ‘Error sending email to: ‘ || v_firstname || ‘ ‘ || v_lastname || ‘: ‘ || v_user_email || ‘\n’;
v_error_buffer := v_error_buffer || ‘Error sending email to: ‘ || v_firstname || ‘ ‘ || v_lastname || ‘: ‘ || v_user_email || ‘\n’;
END IF;
END IF;

–Empty out the buffers
v_header_buffer := NULL;
v_email_buffer := NULL;
v_doc_buffer := NULL;
v_cdrl_buffer := NULL;
v_firstname := NULL;
v_lastname := NULL;
v_user_email := NULL;
v_username := NULL;
v_hasDocs := FALSE;
v_hasCDRLs := FALSE;
v_email_success := ”;
v_cur_user := null;
v_doclog_buffer := null;
v_cdrllog_buffer := null;
END LOOP;
CLOSE getUsers;
v_log_buffer := v_log_buffer || ‘\n\nFinished Successfully\n’;

–Write the log buffer to the log file
v_log_success := write_email_log(v_log_buffer);

–Send an email with the error message if there is a problem
IF(v_log_success != ‘success’ OR v_error_buffer IS NOT NULL) THEN
v_error_buffer := v_error_buffer || ‘\n’ || v_log_success;
v_email_success := send_email_message(‘errormail’, v_errormail_buffer, v_error_buffer);
END IF;
END;

/***************************************************************************
* This procedure is used by send_comment_resolution_mail and
* send_batch_mail to write the emails to ediws but could also be used by
* external programs to send emails given our server setup.
***************************************************************************/
FUNCTION send_email_message (p_username IN VARCHAR2, p_header IN VARCHAR2, p_msgbuffer IN VARCHAR2) RETURN VARCHAR2 AS
v_email_file VARCHAR2(60);
f_email_file UTL_FILE.FILE_TYPE;
BEGIN
–Create the file name
v_email_file := TO_CHAR(SYSTIMESTAMP, ‘DD-Mon-YYYY_HH24.MI.SS.FF’) || ‘_’ || p_username || ‘.eml’;

–Open the email file for write only
f_email_file := utl_file.fopen(
location => ‘EMAILNOTIFICATION_EML_DIR’,
filename => v_email_file,
open_mode => ‘W’,
max_linesize => 32767);

–Write the formatted output to the file
IF(p_header IS NOT NULL AND p_msgbuffer IS NOT NULL) THEN
utl_file.putf(f_email_file, p_header);
utl_file.putf(f_email_file, p_msgbuffer);
END IF;

–Close the file
utl_file.fclose(f_email_file);

RETURN ‘success’;

EXCEPTION
WHEN utl_file.access_denied THEN
RETURN ‘Unable to access email directory from send_email_message\n’ || SQLERRM;
WHEN utl_file.invalid_path THEN
RETURN ‘Invalid path used in directory from send_email_message\n’ || SQLERRM;
WHEN utl_file.write_error THEN
RETURN ‘Unable to write to email file from send_email_message\n’ || SQLERRM;
WHEN OTHERS THEN
RETURN ‘Unhandled error in ****.mail_notifications.send_email_message\n’ || SQLERRM;
END;

/***************************************************************************
* This procedure is used by send_comment_resolution_mail and
* send_batch_mail to write the log files to E:\email on the infrastructure
* server but could also be used by external programs to write log files
* given our server setup.
***************************************************************************/
FUNCTION write_email_log (p_logbuffer IN VARCHAR2) RETURN VARCHAR2 AS
v_log_file VARCHAR2(40);
f_log_file UTL_FILE.FILE_TYPE;
BEGIN
–Create the file name
v_log_file := TO_CHAR(SYSDATE, ‘DD-Mon-YYYY_HH24.MI.SS’) || ‘_Email.log’;

–Open the log file
f_log_file := utl_file.fopen(
location => ‘EMAILNOTIFICATION_LOG_DIR’,
filename => v_log_file,
open_mode => ‘W’);

–Write the formatted output to the file
utl_file.putf(f_log_file, p_logbuffer);

–Close the file
utl_file.fclose(f_log_file);

RETURN ‘success’;

EXCEPTION
WHEN utl_file.access_denied THEN
RETURN ‘Unable to access email directory from write_email_log\n’ || SQLERRM;
WHEN utl_file.invalid_path THEN
RETURN ‘Invalid path used in directory from write_email_log\n’ || SQLERRM;
WHEN utl_file.write_error THEN
RETURN ‘Unable to write to email file from write_email_log\n’ || SQLERRM;
WHEN OTHERS THEN
RETURN ‘Unhandled error in ****.mail_notifications.write_email_log\n’ || SQLERRM;
END;
END Mail_Notifications;

February 17, 2006 at 3:09 pm #246961 Reply

Haris Peco
Member

bgblanch,

This is problem with JDBC drivers – it handle 4000 chars only.We can do nothing.I have tested with data direct drivers (commercial drivers) and it’s same – error is same for little callable statement (runing procedure/function)

Suggestion for you : oracle documentation tell that pl/sql varchar variabla have limit 32767 – your example work, but it can be unstable see http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#sthref732

Best

March 1, 2006 at 6:09 am #247516 Reply

Haris Peco
Member

bgblanch,

my previous post is correct for oracle jdbc 10g rel2 drivers, only (handle plsql varchar <= 4000).This is probably bug in drivers

Oracle’s jdbc 9i and 10g rel 1 and DataDirect drivers handle variable <= 32512 (this is OCI limit link http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci02bas.htm#sthref350 )

I don’t sure if this can help you, but this is drivers limit.

Best

September 19, 2006 at 4:25 pm #258764 Reply

mjmotal
Member

How is SQLDeveloper (also a Java based app) able to get around this limit?
Author

Posts

Viewing 11 posts - 1 through 11 (of 11 total)