Oracle 8 timestamp

[Randy Shafer]

Iam using a default value of :

current_timestamp at local

for a field in a table. It works, but displays the Greenwich Time (6 hours earlier).
How would I write the above default statement in my PL/SQL Developer table to give me Central Standard Time?
I have tried everything from the manuals and help
Forums, to no avail. I get errors when I try to APPLY any other changes than the one above.

This is a new table and I am aware that I must delete all of the info in order to change the default for this timestamp column.

The errors I am getting are syntax errors.

any ideas, I can try?

 

[Marco Kalter]

I'm not sure I understand. Is there a problem with the actual default value, or with the way that this value is formatted when you query it?

 

[Randy Shafer]

when I set the default for my timestamp column
to:
current_timestamp(3)at local

I get the following for a timestamp:

30-MAR-04 10.39.05.632 PM +00:00

This was yesterday at 04.39.05.632 PM when I issued an insert into the table.

why is the time wrong?

 

[Randy Shafer]

More explanation:

I have created a table and one of the columns is
set to the following default value, in order to automatically timestamp when a record is posted in my table:

current_timestamp (3) at local

AS i said before the time is wrong in the timestamp, as in the example in my previous posting.

 

[Joachim Rupik]

Could you please check what returns the following query?

select dbtimezone,sessiontimezone from dual;

How did you define column type?

TIMESTAMP WITH TIME ZONE
or
TIMESTAMP WITH LOCAL TIME ZONE

Joachim Rupik

 

[Randy Shafer]

This is the command and result (you asked for):

SQL> select dbtimezone,sessiontimezone from dual
[Oracle][ODBC][Ora]ORA-00942: table or view does not exist

My ONLY options to define the column type(from the drop down box) are: number
varchar2()
date
clob
blob
long
long raw
raw

I used varchar2(60) with a default of:

current_timestamp (3) at local

this automatically timestamps each record.
thanks,
Randy

 

[Joachim Rupik]

Well, create column as usual - using PL/SQL Developer. Then in "Type" column write "TIMESTAMP(6) WITH LOCAL TIME ZONE", in "Default" column you can put what you want (current_timestamp (3) at local), and press Apply button. I hope it helps.

Joachim Rupik

 

[Randy Shafer]

thanks! that worked! since i have made that change
my "oracle SQL worksheet" that i use sometimes gives me this error when I try to select the table:

SQLWKS> select * from diebold.local_crosswalk;
ORA-03115: unsupported network datatype or representation

this is not a real problem, but i would like it to work. And i can see the table just fine with developer.

now I have another problem with one of my other columns. when I insert a record, one of my columns leaves off the leading zeros in the number.

for example 000000088
looks like 88 in the table, I have to have the leading zeros

any ideas?

thanks again!

Randy

 

[Joachim Rupik]

Probably you should upgrade a SQL worksheet:

ORA-03115 unsupported network datatype or representation
Cause: A user bind or define, or an Oracle function, is not supported by this heterogeneous Net8 connection.
Action: Upgrade the older version of Oracle and try again.

Leading zeros you can only store in a column using varchar2 as column type, or using conversion during select.

Joachim Rupik