Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - Errors while using Oracle
  FAQ FAQ  Forum Search   Register Register  Login Login

Errors while using Oracle

 Post Reply Post Reply
Author
HBr View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote HBr Quote  Post ReplyReply Direct Link To This Post Topic: Errors while using Oracle
    Posted: 01 September 2003 at 9:02am

I tried to use spamfilter with MSSQL and it was ok. Next, I tried to use Oracle. First I met the error during creating tables (table tblServers is not included in the script and table tblQuarantine is missing one column - serverid). I corrected these errors. I met another errors in activity log:

09.01.03 14:55:06:625 -- Exception occurred during DoDeleteExpiredQuarantineList: ORA-00911: chybnı znak
09.01.03 14:55:12:875 -- Exception occurred during UpdateListViewQuarantine: ORA-00911: chybnı znak

What should I do?

Thanks.

Jan Brychta

 

 

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4074
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 01 September 2003 at 9:31am

Jan,

If you could please try to translate the error "chybnı znak" in english we can try to understand what is happening.

Roberto F.
LogSat Software

Back to Top
HBr View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote HBr Quote  Post ReplyReply Direct Link To This Post Posted: 01 September 2003 at 9:38am

I found this in Oracle manual:

ORA-00911: invalid character
Cause: Special characters are valid only in certain places. If special characters
other than $, _, and # are used in a name and the name is not enclosed in double
quotation marks (”), this message will be issued. One exception to this rule
is for database names; in this case, double quotes are stripped out and ignored.
Action: Remove the invalid character from the statement or enclose the object
name in double quotation marks.

I can send the reworked oracle script.

Jan

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4074
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 01 September 2003 at 10:17pm

Jan,

We tested the scripts with Oracle 8i, can you please let us know what version you are using? Yes, if you can send us the scripts that work for you we can see if the same correction will work for the other errors you are seing.

Roberto F.
LogSat Software

Back to Top
HBr View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote HBr Quote  Post ReplyReply Direct Link To This Post Posted: 02 September 2003 at 7:03am

We are using Oracle 8.0.4 version. It is quite old version :-)

I made these corrections in your scripts:

CREATE TABLE TBLSERVERS (
  SERVERID NUMBER(10) NOT NULL,
  SERVERNAME VARCHAR2(50) NOT NULL
);

and

CREATE TABLE TBLQUARANTINE (
       QUARID               NUMBER(10) NOT NULL
                                   CHECK (("QUARID" IS NOT NULL)),
       EMAILFROM            VARCHAR2(100) NULL,
       EMAILTO              VARCHAR2(100) NULL,
       SUBJECT              VARCHAR2(100) NULL,
       MSGDATE              DATE NULL,
       MSGID                NUMBER(10) NULL,
       REJECTID             NUMBER(10) NULL,
       REJECTDETAILS        VARCHAR2(200) NULL,
       DELIVER              NUMBER(2) NULL,
       EXPIRE               NUMBER(2) NULL,
       SERVERID             NUMBER(10) NULL
);

nothing else (no indexes, constraints...)

We are a sw-developer company and we have some expiriences with oracle. I thing we are able to try and correct your queries used in DoDeleteExpiredQuarantineList and UpdateListViewQuarantine - in case you can send them to us.

Jan

 


 

Back to Top
HBr View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote HBr Quote  Post ReplyReply Direct Link To This Post Posted: 02 September 2003 at 7:08am

Sorry, I forgot this trigger:

create trigger TBLSERVERS_AUTOINCREMENT BEFORE INSERT on TBLSERVERS for each row
DECLARE l_pkval number(22)\;   
BEGIN 
  select ID_SEQ.nextval into l_pkval from dual\;   
  :new.SERVERID :=  l_pkval\;   
END\;
;

Back to Top
HBr View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote HBr Quote  Post ReplyReply Direct Link To This Post Posted: 05 September 2003 at 4:43am

Anything new with this problem ?

Jan

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4074
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 05 September 2003 at 10:24pm

Jan,

We've only tested with 8i and 9i, older versions are untested and we may not able to support them.

We use a lot of ADO objects in our code to try to make generic calls and let ADO take care of converting them to specific SQL for the various platforms. In case of the "UpdateListViewQuarantine" error, we do issue a simple SQL statement:

SELECT Count(QuarID) FROM tblQuarantine WHERE tblQuarantine.Expire = 0;

followed by:

    'SELECT tblQuarantine.QuarID, tblQuarantine.Deliver, tblQuarantine.Expire, tblQuarantine.EmailFrom, tblQuarantine.EmailTo, tblQuarantine.Subject, tblQuarantine.MsgDate, tblRejectCodes.RejectDesc, ' + '       tblQuarantine.MsgID, tblQuarantine.RejectDetails, tblQuarantine.ServerID ' +  'FROM   tblQuarantine INNER JOIN ' +'  tblRejectCodes ON tblQuarantine.RejectID = tblRejectCodes.RejectID ' +
    'WHERE tblQuarantine.Expire = 0 ' +
    'ORDER BY tblQuarantine.MsgDate DESC;';

In the case of the "DeleteExpiredQuarantine" things are more complex since we use a parameterized query (:MsgDate is parameter):

'UPDATE tblQuarantine SET Expire = 1 WHERE MsgDate <= :MsgDate AND ' +  '( (ServerID = ' + IntToStr(tblServersServerID) + ') OR (ServerID = 0) OR (ServerID IS NULL) );'

followed by a simple SQL:

DELETE tblMsgs FROM tblMsgs INNER JOIN tblQuarantine ' + 'ON tblMsgs.MsgID = tblQuarantine.MsgID WHERE tblQuarantine.Expire <> 0;'

If you turn on tracing in SQLnet you should be able to see how ADO converts these calls into SQL. Also, which provider are you using to connect? Microsoft OLE DB for Oracle, Oracle's OLE DB? Others? Have you tried switching providers?

Finally, which verison of SpamFilter are you using?

Roberto F.
LogSat Software

 

Back to Top
HBr View Drop Down
Guest Group
Guest Group
Post Options Post Options   Thanks (0) Thanks(0)   Quote HBr Quote  Post ReplyReply Direct Link To This Post Posted: 09 September 2003 at 8:02am

I tried to turn on sqlnet tracing and I think the problem is in one extra semicolon ";" at the end of some commands.

I compared commands sent during Settings - Quarantine DB - Step 2 (these are without ;) with first command sent during Quarantine DB - Refresh (SELECT Count(QuarID) FROM tblQuarantine WHERE tblQuarantine.Expire = 0;). I am able to simulate the error in SQLPlus by adding one extra ; after the command.

I am using spamfilter v. 178 (non-registered) and MS Provider for Oracle (I dont know its version).

I watched the commands sent by you and I thing that Oracle doesnt use syntax "INNER JOIN" in any version. But it is possible that the ADO is able to translate the command properly :-o

I am going to try another OLE DB provider.

Bye.

Jan

 

 

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4074
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 10 September 2003 at 11:18pm

Jan,

We may have fixed this problem for the older Oracle releases. I emailed you privately for further info.

Roberto F.
LogSat Software

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.070 seconds.

Copyright © 2002-2016 LogSat Software LLC - Sales: sales@LogSat.com
Contact Us