T3
---------------------------------------------------------------------------
24-MAR-11 11.29.04.491927
24-MAR-11 11.29.17.085396
system@id.world> select t4 from t;
T4
---------
24-MAR-11
24-MAR-11
system@id.world> select * from nls_instance_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
system@id.world> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TERRITORY AMERICA
NLS_CURRENCY $
[Code]...
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
[Code]...
20 rows selected.
system@id.world> show parameter nls_timestamp_fo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_timestamp_format string
system@id.world> create table newt(t1 timestamp);
system@id.world> insert into newt values('12-jan-2010 11:00:00:068801');
insert into newt values('12-jan-2010 11:00:00:068801')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
system@id.world> insert into newt values('12-jan-2010 11.00.00.061');
1 row created.
system@id.world> insert into newt values('12-jan-2010 15.00.00.061');
1) what is significance of '.' here? insert into t values(timestamp'2011-03-24 11:03:00.05','12-mar-2011 11.03.00.055',systimestamp,localtimestamp);
2) while inserting using "values(timestamp'2011-03-24 11:03:00.05'" where this yyyy-mm-dd hh24:mi:ss.FF format comes from?
3)what is role of nls_timestamp_format of nls_database_parameters in this? how it allowed me to insert value "'12-mar-2011 14.03.00.055'" which has hh24 format?
4) Does the format of localtimestamp is decided by nls_database_parameters?
Note : I have not set nls_date_format in my session
I tried to read the link below but could not understand above
I get data from one table into a gridview and one of the columns is a date. I get it with date and time stamp but I want solely the date in my gridview.
I am wanting to write a procedure which takes in a TIMESTAMP datatype as a parameter. I only really want the time, not the date part.I am struggling to understand however what format the inserted TIMESTAMP would take. E.g
I have table named purchage with 2 columns (order_no number,order_date date) in my database. I want to load the data from a file into that table. The below is the file format
100,4/3/2013 1:18:18 AM 101,4/3/2013 1:18:18 AM 102,4/3/2013 1:18:18 AM 103,4/3/2013 1:18:18 AM 104,4/3/2013 1:18:18 AM 105,4/3/2013 1:18:18 AM 106,4/3/2013 1:18:18 AM
how to load the date filed along with the time stamp.
I have a ssn field where the user enter value to search for the ssn. I want to format the field so the user can enter in the xxx-xx-xxxx format only. If the user enters a wrong format and tries to search i should prompt a message saying wrong format. How can i achieve this.
I have one NUMBER field with size 15. I have set format mask 999,999,999,999,999 on this field. This is formatting the data I am entering in that field. But the problem is if I enter a value in that field and moves to another field. And if I want to edit the previous field again I have to keep the cursor at the left end of the value.
For example I enter 3,000 in the field. If I want to edit the field again, I have to edit it from left like 1,233,000. I cannot edit it at the end(like 3,000,123).
Even I can click anywhere in that field where I have the format mask. check the screen shot in the attachment. In the red square, you can find a value with space in between.
I am having past data in a table say for one year. each row is having a timestamp column. Now i need to calcute avg of data for every time interval betwwen start date and end date.
time interval can be in minute, hours, days or months..in case of hours,days and months , i need to convert to minutes and group based on that interval.
I'm trying to deduct x hours from a timestamp value. Everything works fine if x yields a result that is on the same day as the value I'm deducting it from. However, if it yields the day before then it gives the wrong answer using DML (although in SQL it works fine). Let me explain with an example:
Let's say current_timestamp is 20-MAR-12 09.40.00.000000 +00:00
If I deduct 8 hours from this I get the correct answer: 20-mar-12 01:40:00 (syntax: show current_timestamp-8/24)
If however, I deduct 10 hours from it (which takes it to the day before) I get garbage: 20-mar-12 00:20:00 (syntax: show current_timestamp-10/24). It should say: 19-mar-12 23:40:00
In SQL I get the correct result every time. So, my question is, is this a bug or intended behaviour? If the latter then what is it doing and how can I get it to give the answer I'm looking for?
What I'm trying to do is convert a timestamp from one timezone to another using DML. The NEW_TIME function will only give me what I want if I know whether the "to" timezone is currently in daylight saving or not. eg. Must I use CST or CDT?
The full syntax I'm using to try and achieve my aim is below.
show to_char( to_date( '2012-03-19--15-37-23' 'YYYY-MM-DD--HH24-MI-SS' )-(convert( extchars( tz_offset( 'AMERICA/CHICAGO' ) 3, 1 ) int )/24) 'YYYY-MM-DD--HH24-MI-SS' )
where 2012-03-19--15-37-23 is a user-supplied argument.
Oracle Database Version : 9.2.0.8.0 Some of the datafiles status have been changed to 'RECOVER', because the datafiles are physically missing.
Now, how can i find that when (timestamp) the status of the datafiles have been changed, as i am unable to find when the datafiles have been physically lost?
Please consider both the case :
1) when the database is in ARCHIVELOG Mode. 2) when the database is in NOARCHIVELOG Mode.
I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.
The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.
What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.
I am inserting XMLTYPE data using DBLINK I am getting the following error.
INSERT INTO APSP.SALES_HISTORY@APSP_LINK SELECT * FROM KMBS.SALES_HISTORY
ORA-22804: remote operations not permitted on object tables or user-defined type columns
Source table structure
Name Null? Type ----------------------------------------- -------- ---------------------------- SC_NO NOT NULL NUMBER(25) LT_DATE TIMESTAMP(6) METHOD XMLTYPE
Target table structure(another DB)
Name Null? Type ----------------------------------------- -------- ---------------------------- SC_NO NOT NULL NUMBER(25) LT_DATE TIMESTAMP(6) METHOD XMLTYPE
I used decode & pivot insert for this,but the result is a failure.
SQL>INSERT INTO test22 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
SQL> sno sname -------- 1 null null arun
AND
SQL> INSERT ALL 2 INTO test22 VALUES(no) 3 INTO test22 VALUES(name) 4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p; INTO test22 VALUES(name) * ERROR at line 3: ORA-00904: "NAME": invalid identifier
In my organization, I have a table and in that there is a column named "code".I want to restrict some insertion to that particular column. suppose that code column values are 12 and 1245 then i cant insert the value 12,1245, 1 ,124 and so on but i can insert 2 ,123,15,12456 and so on.
that means the new values should not be any substring of the existing data from left. making that column primary key and then I had a logic to compare the existing value which are longer than the new value and then to perform this.But dont know how to make it happen correctly.
I am trying to insert a column into a variable from a trigger.
Here is the code that i have:
CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer FOR EACH ROW DECLARE v_key varchar2(10); BEGIN select ID into v_key from buyer; insert into message_log_table (table_name, message_comments) values ('Buyer', 'Buyer '||v_key||' has been updated'); end; /
When I run the above I get the following compiler error:
Since ID is defined in my BUYER table I do not understand what the error means.
Here is my create table statement:
CREATE TABLE BUYER ( ID VARCHAR(50) NOT NULL PRIMARY KEY, FNAME VARCHAR(50) NOT NULL, LNAME VARCHAR(50) NOT NULL, ADDRESS VARCHAR(50) NOT NULL, CITY VARCHAR(50) NOT NULL, STATE VARCHAR(2) NOT NULL, ZIP_CODE NUMBER(5) NOT NULL );
I am facing problem while inserting data into Core Table from staging (both Core Table and Staging are on same database but different schema)
I am using below command:
INSERT /*+ APPEND PARALLEL("CORE TABLE", DEFAULT, DEFAULT) */ INTO SELECT DATA from Staging
CORE TABLE is quit big contains millions of record partition on date and having old stats of 2007. Data fetching from staging is very fast appx 1 million record in 2 mins. we are inserting one day data daily into CORE TABLE from staging and its taking 3 Hrs.
How to Insert the data in Repeating Tables. I mean Suppose One Student has many Addresses like home,office,permanent etc.
There is one column in this table sequence_no. while Inserting a record how to insert this sequence no I don't know It maintains unique sequence no for each student.
If student has 3 addresses then Its seq no is 3.
I am inserting through a procedure where multiple students data is to be inserted.
Excel File Name : Product 01 Excel columns File Send on 13/02/2011
Arrival Date Product Code Gate Pass Quantity Inpection 01/02/2011 00002 Y 2 Y 03/02/2011 00001 Y 10 Y 04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y
File Send on 14/02/2011
Arrival Date Product Code Gate Pass Quantity Inpection 01/02/2011 00002 Y 2 Y 03/02/2011 00001 Y 10 Y 04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y ---New Updated Data 05/02/2011 00002 Y 2 Y 06/02/2011 00001 Y 10 Y 05/02/2011 00005 Y 14 Y 05/02/2011 00006 Y 74 Y
I just want to insert data according to my structure But if again the same file send with updated data it will only update the new data because previous data is imported.
Oracle Structure Arrival Date Date, Product Code char(5), Quantity Number
