Forum OpenACS Development: Doing things the old-fashione way with ns_ora

Collapse
1: Doing things the old-fashione way with ns_ora
Posted by Janine Ohmer on 09/02/03 01:23 AM
One of the sites we host is based on an ancient version of the ACS.  No database API... it's painful! :)

We're using the latest Oracle driver, but for some reason bind variables don't work unless you use "ns_ora exec_plsql_bind" to execute the statement.  If you do it the "normal" way, you get this error:

[01/Sep/2003:18:38:35][29862.4285446][-conn257-] Error: ora8.c:1500:ora_exec: error in `OCIStmtExecute ()': ORA-01008: not all variables bound

The site's performance is really suffering from the lack of bind variables, but I know they are not going to go through and modify every "ns_db select".  So I wanted to test out setting cursor_sharing to force, which is supposed to simulate using bind variables.  Problem is, I can't figure out how to set it.  I only want to do it on a per-session basis for testing, but this:

ns_ora exec_plsql $db "alter session set cursor_sharing=force"

gives this error:

[01/Sep/2003:19:08:33][29862.4310022][-conn265-] Error: ora8.c:3355:ora_tcl_command: error in `OCIBindByPos ()': ORA-01036: illegal variable name/number

Any suggestions?  Thanks in advance!

Collapse
2: Re: Doing things the old-fashione way with ns_ora (response to 1)
Posted by Janine Ohmer on 09/08/03 06:00 PM
No-one knows how to do this?  Are you sure? :)
Collapse
3: Re: Doing things the old-fashione way with ns_ora (response to 2)
Posted by Raad Al-Rawi on 09/09/03 03:23 PM
Hi Janine

Try:

ns_db dml $db "alter session set cursor_sharing=force"

I *think* it will work :)


<Raad>
Collapse
4: Re: Doing things the old-fashione way with ns_ora (response to 1)
Posted by Dirk Gomez on 09/10/03 04:00 PM
Can't you do an alter system set cursor_sharing=force to test it? (Or is it one of these pesky parameters that can only be changed with restarting the instance?)

Otherwise: you could also look into DDL triggers namely on-login triggers granted they are already available on your Oracle version.

Collapse
5: Re: Doing things the old-fashione way with ns_ora (response to 4)
Posted by Raad Al-Rawi on 09/12/03 05:35 PM
Yep - it's a dynamic parameter, so it is possible to set it instantly for the duration of the instance! :O

<Raad>