Forum OpenACS Q&A: Response to Robot detection - source for "Web Robots Database" vanished

Probably we are talking about two different oacs revs and rdbms' as I run oacs 4.5 on Oracle and only pointing to a different file would not help.

here what I found:

the new robot list has many nulls in robot_useragent line what is causing insert statement in ad_replicate_web_robots_db to fail as robots.robot_useragent field in oracle is set to NOTNULL.
string length for that line is longer for some robot entries then 100 char what is max set for that field in robots.robotuseragent and it causes as well ad_replicate_web_robots_db to fail:

ORA-01401: inserted value too large for column SQL:insert into robots(robot_id, robot_name,robot_details_url, robot_useragent) values (:robot_id, :robot_name, :robot_details_url, :robot_useragent)

I have fixed this problem by changing the code in ad_replicate_web_robots_db

before:

 if {[exists_and_not_null robot_id] && [exists_and_not_null 
robot_useragent]} {
                    db_dml insert_new_robot {
                        insert into robots(robot_id, robot_name, 
robot_details_url, robot_userage
nt)
                        values
                        (:robot_id, :robot_name, :robot_details_url, 
:robot_useragent)
                    }
                }

after:

  if {[exists_and_not_null robot_id] && [exists_and_not_null robot_name]} {
                    db_dml insert_new_robot {
                        insert into robots(robot_id, robot_name, robot_details_url, robot_useragent)
                        values
                        (:robot_id, :robot_name, SUBSTR(:robot_details_url, 0, 199), NVL(SUBSTR(:robot_useragent, 0, 99), :robot_name))
                    }

after done this data loaded nicely into table but after restart of the aolserver the other part of same proc kicked in and faild on:
Notice: Running scheduled proc ad_update_robot_list... [11/Jul/2002:18:32:30][24381.2051][-sched-] Warning: APM: RefreshIntervalDays does not exist [11/Jul/2002:18:32:30][24381.2051][-sched-] Error: ora8.c:3930:ora_tcl_command: error in `OCIStmtExecute ()': ORA-00904: invalid column name SQL: select trunc(sysdate - max(nvl !>>>!modified_date, insertion_date))) from robots

there is no "modified_date" field in oracle robots table. I changed then:robot-detection-procs-oracle.xql

before:

 set days_old [db_string days_since_last_update {
                select trunc(sysdate - max(nvl(modified_date, insertion_date))) from robots} ]

after:

  set days_old [db_string days_since_last_update {
                select trunc(sysdate - max(insertion_date)) from robots} ]
                if {$days_old >= $refresh_interval} {