Ive added a new field to a table The new field is called Release_confirmation. How do I add the same value to this field for all rows ie. confirm.
So Id basically be setting Release_confirmation to confirm for all existing rows.
Should I use update or insert?
I am trying to insert values in the timestamp field
system@id.world> create table t(t1 timestamp,t2 timestamp, t3 timestamp, t4 date);
Table created.
system@id.world> insert into t values(timestamp'2011-03-24 11:03:00.05','12-mar-2011 11.03.00.055',systimestamp,localtimestamp);
1 row created.
system@id.world> insert into t values(timestamp'2011-03-24 14:03:00.05','12-mar-2011 14.03.00.055',systimestamp,localtimestamp);
1 row created.
system@id.world> select t1 from t;
T1
---------------------------------------------------------------------------
24-MAR-11 11.03.00.050000
24-MAR-11 14.03.00.050000
system@id.world> select t2 from t;
T2
---------------------------------------------------------------------------
12-MAR-11 11.03.00.055000
12-MAR-11 14.03.00.055000
system@id.world> select t3 from t;
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
[Code]...
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
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);
Table created.
system@id.world> select localtimestamp from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
24-MAR-11 11.31.07.667296
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 row created.
system@id.world> select * from newt;
T1
---------------------------------------------------------------------------
12-JAN-10 11.00.00.061000
12-JAN-10 15.00.00.061000
system@id.world>
My questions are
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
[URL]....
shall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies View RelatedI want to insert excel data into Oracle.
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
i wanna copy data from a table to another one (same structure, only difference is that the second table is partitioned). the origin table consists of about millions of lines so i made an insert :
insert into table2 select * from table1. the insertion doesn't end correctly : TOAD crashes, any line was inserted in table2 : select * from ...etc.
However, the file system becomes full. i would if there is a way to purge something like cache ..., are these lines inserted somewhere : temporary table...,
so i can reduce filesystem size.
I was trying to insert a row into Oracle database consisting 20 columns through IBM Web Experience Factory. I am getting "Array index out of range: 20" error message. To my knowledge insert is working fine till 7 columns. Is it a driver problem or do i need to install any fix pack?
View 14 Replies View Relatedhow can i insert a image by selecting an image from any drives of my system to oracle database and retriving that image for displaying purpose after inserting that image. if possible give me a sample form. like vb 6.0 or vb.net in textbox's change trigger fires instantly when we start typeing in the text boxes.
View 6 Replies View RelatedI would like to know if we can insert 300 million records into an oracle table using a database link. The target table is inproduction and the source table is in development on different servers.The target table will be empty and have its indexes disabled before the insert. if this can be accomplished in less than 1 hour.
View 26 Replies View Relatedi am continuously inserting data to oracle database after some time like 2 hours oracle disconnects,it creates erros like
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0
after restarting the database with shutdown immediate ans startup if i start inserting records it will show erros like
ORA-01653: unable to extend table SYSTEM.GLT_PROT_TRAFFIC_SUM_VOIP by 8192 in tablespace GLCOMM
but i have created the Tablespace with BigFile Auto Extend and max size unlimited, i am having 400GB disk space created Redo logs with 15gb,i have tried serveral times reinstalling the oracle but problem is not solved.
same problem is happenging with small datafiles also,
operating system: windows server 2008 R2 standard
oracle server : 11g
oracle client : 64 bit
Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.
View 13 Replies View RelatedWe have oracle 10 g and a table contains a clob field . The table size is getting increased day by day . We have decided to zip all the clob data inside the table except last 1 month records.
Table : GENERIC_MESSAGE
Column:
message_id(number)
received_date(date)
message_xml(clob)
How we can do that keeping in mind this is huge data is present in table and huge transaction happened (10k transaction/hr)?
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST
(
C VARCHAR2(2000 BYTE),
B NUMBER,
A NUMBER
);
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
[code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN
:M_END_ALL := 'N';
END IF;
--code written on button to update those values which are checked.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
IF :M_END_YN = 'Y' THEN
[code]......
I am New to Apex. Im using Apex Version 4.2.
I have a tab named APPROPRIATIONS it has 10 fields. Among which is PROJECT NUMBER, AUTHORIZAION DATE (CALENDER TYPE POPUP), And FUNDS CHECK FLAG.
The Scenario is depending upon PROJECT NUMBER the Fields are Populated including FUNDS CHECK FLAG .
But the AUTHORIZAION DATE is a MANDATORY field has to be filled by User
The Requirement is to make AUTHORIZAION DATE as MANDATORY only when the FUNDS CHECK FLAG is *"BLANK "* or *“Y”*
If the FUNDS CHECK FLAG is *“N”* then the AUTHORIZAION DATE to be made OPTIONAL.
I need to update a field with the 1st 9 characters of another field in the same record.
View 1 Replies View RelatedI have a table called Customer_Type with following fields
Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011
Tbale 2:Customer
Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
The sql statements are below
CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),
[Code]....
Is there a way to apply changes to a data field on oracle forms for specific users?
For example, I have an oracle form that applies the below masking code for a certain field called CUSTOMER_ID
IF :SYSTEM.BLOCK_STATUS = 'QUERY' THEN
SET_ITEM_PROPERTY('CUSTOMER_ID',CONCEAL_DATA,PROPERTY_TRUE);
ELSE
SET_ITEM_PROPERTY('CUSTOMER_ID',CONCEAL_DATA,PROPERTY_FALSE);
END IF;
But I only want this masking to be applicable to CERTAIN users. For example, the psuedo code would be like
IF :SYSTEM.BLOCK_STATUS = 'QUERY' and USERS_LIST = 'SUPERVISORS' THEN
SET_ITEM_PROPERTY('CUSTOMER_ID',CONCEAL_DATA,PROPERTY_TRUE);
Note that this users_list is a group of oracle database users, so I am a member of this group. Also note that this form is applied via the web via the application server.
I have uploaded some tables from MS Access to Oracle and in the process, some Number fields have been converted to Text. This means that any Queries where there are calculations with those fields (which have been converted to text) don't work properly anymore.
I would like to change the field type from text to number (with cmd and sqlplus) as that's the only way I can access the oracle backend.
I am not sure what commands to use to change this.
Also, if I change the data type from text to number, is it likely I will lose some data?
And how do I backup the table before I do this?
how to write a procedure in oracle to Save pdf file content to BLOB field in Oracle....
View 11 Replies View RelatedWe are facing below issue in Oracle forms 10g( Rel 10.1.2.0.2) with Windows XP professional.Application server J2EE 10.1.2.0.2).
We have a multirecords block. After navigating to last record by clicking mouse in scroll bar and change values in a column by clicking mouse in column to change, cursor control is moving to some other record(where cursor is located previously) and current changed value it is showing as not fully validated.
Eg: We have columns like below in multirecords block
Security Price Date Price
--------------- ------ ----
1 1/1/2011 12.00
2 1/1/2011 16.00
3 1/1/2011 18.00
4 1/1/2011 19.00 (Last Record)
If am changing last records price to 20 then press tab key, cursor will be moved to second record and cursor will be highlighted in security 2. column price where we changed to 20 also not validated to show as 20.00 with 2 decimal format mask.Along with this we are getting below error message ' FRM-41008 - Undefined function key, press Ctrl + F1 for list of valid keys.After changing the price we are pressing the tab key only.
This occurs randomnly and when we querying more than 2000 records with multiple times.
I'm using Microsoft Access 2007 as a front end to Oracle 10g tables using an ODBC Connection. I need to be able to add attachments to the records in one of my linked tables to hold emails, screen shots, log entries etc.
Access 2007 handles this capability with a field format called Attachment. I don't want to store the attachments locally but want them to be stored in Oracle, but I'm not sure what format the field in Oracle needs to be so that it will come across in Access as an Attachment field.
Is there a way to query an oracle database in an automated fashion by a timestamp field based on current timestamp, like: 04/29/08 00:00:00 - 72 hours?
View 3 Replies View Relatedcode to append data of 32k to exisitng CLOB field in an oracle table..
View 1 Replies View RelatedIn a data block(it lists all the records created), I tried to search for the records which are created on a particular date. So I entered the date in query mode in that data block. But it is not returning any rows. But if I searched through other fields in that data block except that date data type it is returning the appropriate records.
View 10 Replies View RelatedI have a table in schema with a BLOB field. I store employee's picture in this field. Fields in this table are emp_id (number) emp_name (varchar2) and emp_photo (BLOB). I want to ask if there is a way in pl / sql that i could empty this BLOB field to null or reset this field so that user can change the saved photo graph and save another one.
what i am looking for is something like
alter table employee set emp_photo = empty_blob() or alter table employee set emp_photo = null
I m using oracle forms 9i in which i want effect on text fields that will change color of text after mouse cursor move on that field . is it possible in oracle form 9i .
View 6 Replies View RelatedI have to date field in detail part of master detail Form. In detail part i have field of From_date and To_date. I want when i go to next record in detail part ,,,from date is initiaze by previous to date+1....eg.like
1.record: From date=01-jan-2011, To_date=10-jan-2011
2.Record: From_date = 11-jan-2011;
I have Attached calender to two feild...and calender is the different block... In Detail block when_record_instance i handled the;; From date=previous to-date +1;
but when i double click the feild calender is open and when_new_record trigger is fired agian. calender called ...the when_new_record is not callled .....
I have a view ( from many tables) , an error view in wich i monitor errors that appear in my project. This view should be empt always but whenever a error occurs the view shows me this. I wanna put a trigger or something like that on that view to send me an e-mail whenever a line is inserted in that view. ( I don't wanna use a job for that to make a count because this will affect the entire database).
View 3 Replies View RelatedMy homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE;
CREATE TABLE BookingDM (
hNo NUMBER(3),
gNo NUMBER(5),
dFrom DATE NOT NULL,
[code]......
This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202');
INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202')
*
ERROR at line 1:
ORA-01843: not a valid month
I need my dates to be in the format "MM/DD/YYYY".
I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'
I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables