Re: alter session

Hi,

that's all ok thanks.
My Problem:

I have nearly 100Meg Records and I have to build a group by query by date. The stored date is a timestamp with group by I receive a group for each second. On monthly base, where this query has to work, I will have ~ 10 Meg Records and I have more than one record for each second.
Using to_char to format the date will take hours for the SQL script. If I run sqlplus by doing the following: alter session set nls_date_format = 'YYYY.MM.DD' select count(*), trn_dt from v_trx where trn_dt between to_date('19980401','YYYYMMDD') and to_date('19980430','YYYYMMDD') group by trn_dt;

will give me the result I expected. For each day a number. The performance of this query is ok (1 min). I thought, by running the alter session command before the select statement will give me the correct results - but ODBC runs the alter session command and the select statement is grouped by seconds. That means, that ODBC uses a new connect with each statement.

Now I'm looking for a solution to get right values. A new index and/or new column is impossible (not enough space).

Thanks again

Detlev

Billy Verreynne schrieb:

> Detlev Goebel wrote in message <363DD1E4.FE221178_at_www.gzs.de>...
>
> >we have a connection to oracle 7.3 via ODBC. Now we like to have an
> >alter session command to the database.
> >The ODBC driver isn't capabla to provide SQLP Plus Commands to the
> >server, login.sql works with sqlplus.
>
> Yes, if by that you mean SQL*Plus specific commands like DESCR, SET, SPOOL
> etc. However, the ALTER SESSION is an Oracle SQL command.
>
> >Is there any idea to do an alter session command within a profile or
> >something similar ?
>
> ODBC drivers (should?) use the current PC's regional settings to set the
> date format, currency format etc. If you trace the ODBC calls made (or even
> better, sniff the ODBC network traffic), you'll see what ODBC does when it
> connects to a database. (and this can be scary sometimes, especially if you
> do not use the correct "method" in Visual Basic!)
>
> With Oracle SQL you can however change any of the NLS parameters useing the
> ALTER SESSION SQL command. If you get an error executing this command in VB
> (or whatever language you're using) then you either have the syntax wrong,
> or you're attempting to run the SQL command as if it's returning a cursor
> handle (like with a SELECT command). Simply run the ALTER SESION command
> using the EXECSQL or whatever method is used to run a DDL command.
>
> regards,
> Billy
Received on Tue Nov 03 1998 - 04:23:44 CST