Home
The Toolkit for Online Communities
17368 Community Members, 0 members online, 2251 visitors today
Log In Register
OpenACS Home : Forums : OpenACS Q&A : Request error when doing a user search

Forum OpenACS Q&A: Request error when doing a user search

Icon of envelope Request notifications

Collapse
Posted by tool who on
Your Workspace : Main Site : Site-Wide Administration : Users : Complex search

hey all, i'm encountering a request error when i try to view whatever users i have and then click on a user name, i get a new screen that goes something like this:

Request Error
ora8.c:3909:ora_tcl_command: error in `OCIStmtExecute ()': ORA-00904: invalid column name

SQL: select first_names, last_name, email, !>>>!coalesce(screen_name,'&lt none set up &gt') as screen_name, creation_date, creation_ip, last_visit, member_state, email_verified_p, url
from cc_users
where user_id = :user_id
    while executing
"ns_ora 0or1row nsdb0 {select first_names, last_name, email, coalesce(screen_name,'&lt none set up &gt') as screen_name, creation_date, creation_ip, la..."
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_ora $type $db $sql] $args"
    invoked from within
"db_exec 0or1row $db $full_statement_name $sql"
    invoked from within
"set selection [db_exec 0or1row $db $full_statement_name $sql]"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle db {
    set selection [db_exec 0or1row $db $full_statement_name $sql]
    }"
    (procedure "db_0or1row" line 22)
    invoked from within
"db_0or1row user_infox "select first_names, last_name, email, coalesce(screen_name,'&lt none set up &gt') as screen_name, creation_date, creation_ip, l..."
    invoked from within
"if ![db_0or1row user_infox "select first_names, last_name, email, coalesce(screen_name,'&lt none set up &gt') as screen_name, creation_date, creation_..."
    ("uplevel" body line 27)
    invoked from within
"uplevel {
          ad_page_contract {
    One user view by an admin
    rewritten by philg@mit.edu on October 31, 1999
    makes heavy use of procedures..."
    (procedure "code::tcl::/home/user/web1/server1/packages/acs-admin/www/..." line 2)
    invoked from within
"code::tcl::$__adp_stub"
    invoked from within
"if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init tcl $__adp_stub
..."
    ("uplevel" body line 3)
    invoked from within
"uplevel {

    if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init t..."
    (procedure "adp_prepare" line 2)
    invoked from within
"adp_prepare "
    (procedure "template::adp_parse" line 30)
    invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
    (procedure "adp_parse_ad_conn_file" line 7)
    invoked from within
"$handler"
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
    $handler
      } ad_script_abort val {
    # do nothing
      }"
    invoked from within
"rp_serve_concrete_file [ad_conn file]"
    (procedure "rp_serve_abstract_file" line 60)
    invoked from within
"rp_serve_abstract_file "$root/$path""
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
    rp_serve_abstract_file "$root/$path"
    set tcl_url2file([ad_conn url]) [ad_conn file]
    set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."

any ideas on what is generating this error?
Thanks

Collapse
Posted by Jun Yamog on
Hi tool,

For some weird reason its running the postgres query.  "coalesce" does not exists in Oracle.  Try to look the cause why its running the PG query and not the Oracle one.  Good Luck.

Collapse
Posted by tool who on
Thanks, i will dig into it.
Collapse
Posted by tool who on
what if, using the package manager i remove all the postgres files under acs-admin. Might that help in solving the problem?
Collapse
Posted by Jeff Davis on
This is a bug I created when templating the pages.  Brad Duell
submitted a patch for it which I will apply.
http://openacs.org/bugtracker/openacs/bug?bug%5fnumber=135
Collapse
Posted by tool who on
sorry to be of bother but how do i download the patch content(as a tcl file???) and implement it? Thank you
Collapse
Posted by Jeff Davis on
- means delete the line and + means insert the line.

In this case you should just change "user_infox" to "user_info" in one.tcl and restart the server.

You don't need to (nor should you) delete the queries from
one.xql.

Collapse
Posted by tool who on
in the patch file does the - before the code mean "before" and the + before the code mean "after" or is my logic just really messed up ;)
Collapse
Posted by tool who on
thanks dude, thats exactly what i thought, was gonna delete the lines from the .xql but thanks for the heads up.
Collapse
Posted by tool who on
i just installed and loaded the site-wide search package and am getting a similar error when doing a search:

ora8.c:3909:ora_tcl_command: error in `OCIStmtExecute ()': ORA-00904: invalid column name
SQL: select !>>>!im_convert(:query_string) from dual
    while executing
"ns_ora 0or1row nsdb0 {select im_convert(:query_string) from dual}"
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_ora $type $db $sql] $args"
    invoked from within
"db_exec 0or1row $db $full_name $sql"
    invoked from within
"set selection [db_exec 0or1row $db $full_name $sql]"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle db {
set selection [db_exec 0or1row $db $full_name $sql]
    }"

Collapse
Posted by Brian Fenton on
Tool, you need Intermedia installed to use the site wide search  in Oracle. The im_convert function should be created after you installed site-wide-search - did you get any errors when you installed it? Check in SQL*Plus to see if im_convert is there by typing this:

select * from user_objects
where object_name = 'IM_CONVERT';

Collapse
Posted by tool who on
no its not there, and i don't think i have intermedia installed(is there anywhere i can download it from) but thanks for all the help. you guys are great, cheers
Collapse
Posted by Brian Fenton on
You need the Enterprise Edition of Oracle - Intermedia comes with that. I'm surprised you didn't see any errors when you installed Site Wide Search. Can you try installing it again?
Collapse
Posted by C. R. Oldham on
Actually Intermedia comes with Oracle Standard Edition, but some of the INSO filters (that enable indexing of rich content like PDFs and Word documents) might not be.