SQL & PL/SQL :: Row Modify In Table?

Apr 12, 2013

is there any way we can find out which row is updated in oracle table if we don't have any date/time column in table.

let say.

Create table T1 (ename varchar2(200), age number);

insert into T1 values ('A',20);
insert into T1 values ('B',30);
insert into T1 values ('C',40);
insert into T1 values ('D',50);

commit;

update t1 set age=50 where ename='A'

commit;

looking some oracle query which identify which record is update in table in given time period.

View 5 Replies


ADVERTISEMENT

Modify One Table To Trigger Other Tables

Aug 20, 2012

We are running an Oracle 10g server.

We are adding support for barcode scanner in one part of our information system. So that the mechanics can add parts to an work orders bill of material themselves using barcode scanner to scan the part and enter the qty.

I can via SQL add a part to the bill of materials and reserve the part. But then the part needs to be issued to make it disappear from the stock.

I have found a table that contains information about the part and the bill of material, it has a column named "QTY ISSUED" I have tried via an update command to set the qty issued = 1 for the reserved part. The table is updated, and via SQL everything looks fine, but the part doesn't disappear from the stock. So my guess is that there is a trigger or function somewhere that I need for this.

View 13 Replies View Related

SQL & PL/SQL :: ORA-01779 / Cannot Modify Column Which Maps To Non Key-preserved Table

Mar 8, 2011

i have a problem in my view. when i try to update a column in my view it prompts me with an error. i consulted the database error messages, and the description is as below:

ORA-01779: cannot modify a column which maps to a non key-preserved tableORA-01500 to ORA-02098 4-25
Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
Action: Modify the underlying base tables directly

i dont understand this "non-key-preserved table". is there any way out from this????. why cant i perform update opearation???. parent tables and the view are as follows:

(
create view donations_n_tests as
select dn_regno, dn_blood_gp, bagno, sc_test_date, hbs, hcv, hiv, vdrl, mp, dn_base, camp_name,
camp.branch_id camp_branch, screening_tests.branch_id as testing_branch
from screening_tests left outer join camp using (campno) order by bagno
)

[code]...

View 2 Replies View Related

Exchanging Partition After Doing Alter Table Modify Column Length?

Mar 11, 2011

We have some tables in our database in which for loading data we have the setup in place to do Exchange partition after data load into staging. Today we did changes to column length to one pair of main and staging table. Post that Exchange partition stop working.

View 1 Replies View Related

SQL & PL/SQL :: How To Modify Partition

Nov 16, 2011

There is a customer_account table. It is partitioned by Range. In one of the partition, the range value is given wrongly, and irrelevant data sits in that partition.

How can i modify the range partition value and re-arrange the data ?

View 2 Replies View Related

Modify Trigger Into Procedure

Sep 12, 2011

Moidfy trigger into procedure

I am quite a beginner One of trigerred jobs is freezing and I have to reproduce the issue. Thing is about extracion and export data from one table to another (from Rating to Xtexport_table_1).

I want to build query which will fill xtexport_table_1 with values, partialy declared and partialy fetched from Rating tabble.

I figured out that that I have to get rid of all condition statements(IF, WHEN etc ), and just declare variables for some columns and then use a SELECT statement to fetch data.

create or replace
TRIGGER TRG_RATING_EXP_DLS
BEFORE DELETE OR UPDATE OR INSERT
OF RATING_ID,EXTERNAL_ID_1,DN_RATING_ATTRIBUTE_ID,ALI GNMENT_ID,TEAM_ID,CUSTOMER_ID,AFFILIATION_ID,PRODU CT_ID,PERIOD_ID,VALUE_MIN,VALUE_MAX,TENANT_ID,USER _ACCOUNT_ID
ON RATING
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE

[Code]...

-------------------

Don't play what's there, play what's not there

View 2 Replies View Related

SQL & PL/SQL :: How To Modify Column Data

Jun 8, 2012

i have one table emp in this table already data exist now i have to modity this existing column data so how can i do this

example:existing emp table

emp_no ename
1 dk patel
2 sk patel
3 jk patel

now i want to change this ename column data i want output like this below table

emp_no ename

1 dk
2 sk
3 jk

how can i do this.

View 4 Replies View Related

SQL & PL/SQL :: Modify Number Data?

Mar 16, 2011

We have table with number as datatype which starts with number 10 like 1056723. Here we need to update the first 2 digits which is 10 to 04.Digit 10 will be always constant across all the data.

select * from temp;
id
----------
1056723

After update, it should be

id
----------
0456723

What function in pl/sql should be used for achieve the desired results.

View 6 Replies View Related

SQL & PL/SQL :: Modify BLOB Data

Dec 24, 2010

I have a table containing BLOB column which stores scanned images. Due to an application error, few extra data was padded with BLOB data and now we want to remove it.

The table count will be near to 10 million rows.

We need to remove data from 161byte to 167byte of the blob data. I tried to do with DBMS_LOB.ERASE.But it will create blank spaces for the removed data. Here we need to reduce the size of BLOB data by 6 bytes by removing data from 161bytes to 167 bytes.

View 26 Replies View Related

How To Modify Existing Spfile

Jul 15, 2013

I need to know how to modify the spfile - I know how to create it from scratch (from pfile to spfile), but need to modify an existing spfile.

View 3 Replies View Related

SQL & PL/SQL :: Can Modify Oracle Data Dictionary

Feb 6, 2011

can i modify oracle data dictionary

View 6 Replies View Related

SQL & PL/SQL :: How To Alter Reference Partition (add Or Modify)

Apr 18, 2011

I have created the following partition. but i want to alter in future . how to alter the reference partition. is it avilable in reference partition.

CREATE TABLE EMD_FILE_LOAD_DETAILS_PR
(
FILE_REFERENCE_ID ,
FILE_NAME ,
FILE_TYPE ,
FILE_GROUP_NAME ,

[Code]...

View 1 Replies View Related

Security :: Modify Profile To Use Old Password?

Aug 23, 2010

I have a database in which a user xxxx is assigned a password 'bbbbb'.I want to change the password to the one which was used before which was 'aaaaa'.But when I change the password it was saying "Password cannot be Reused".So I checked in user profile and found out that password_reuse_time=unlimited and password_reuse_max=5.

So what I did was change the password 6 times to something else(Since it is 5) and then tried changing it to 'aaaaa' but still it is saying "Old password cannot be reused".

View 3 Replies View Related

Reports & Discoverer :: Modify REP File

Oct 19, 2010

I am working on Oracle reports 6i. Just for R&D purpose I have opened REP file in notepad. In the notepad file I could find the queries which are there in the data model.

I did a small addition to the query and saved it in the same location. I tried to open the modified REP file from RWRUN60, but it is throwing me error saying "REP-0110- Unable to open file".

Original Query in data model:
SELECT empno, sal, job, hiredate
FROM emp;

Modified Query:
SELECT empno, sal, job, hiredate
FROM emp
WHERE deptno in (10,30);

View 1 Replies View Related

SQL & PL/SQL :: Modify Supertype Without Dropping / Recreating Its Subtypes?

Oct 2, 2013

is there a way to modify supertypes without dropping / recreating its subtypes?

View 6 Replies View Related

Security :: Audit Update / Modify And Insert

Oct 18, 2011

I am using 'Novell Sentinel Log Manager' to collect/fetch logs from my Oracle 11g R2.To enable auditing, first I did following:

login as sys, then
SQL> create user testuser identified by "testuser";
SQL> grant connect to testuser
SQL> grant dba to sharf
SQL> grant CREATE SESSION to testuser;
SQL> grant select on v_$session to testuser;
SQL> grant select on v_$version to testuser;
SQL> grant select on SYS.DBA_AUDIT_TRAIL to testuser;
SQL> grant select_catalog_role to testuser;
SQL> grant select any dictionary to testuser;

Now logon/logof of user 'testuser' are logged , as well as if testuser drops a table or creates a table, its also logged . but when 'testuser' insert a new record, this information does not logged ;( while I need to know exactly what was added SQL> insert into emp (empid, name, salary) values (10002, 'Ron', 6000)

likewise if 'testuser' modify/update an existing record it also does not logged.
SQL> update emp set salary=700 where empid=10001;

which sql statements I have to execute to start auditing 'insert' and 'update', so that I know what was added/inserted and exactly what was updated/ changed/modify by user 'testuser'.

View 12 Replies View Related

Server Administration :: DBMS_Scheduler Modify Time Zone

Mar 1, 2010

I have created a job using DBMS_SCHEDULER with named schedule.

After i have modified the schedule using the below queries,

exec dbms_scheduler.disable( 'JOB1' );
exec dbms_scheduler.set_attribute_null('JOB1','schedule_name');
exec DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'JOB1', attribute => 'repeat_interval', value => 'freq=WEEKLY;BYDAY=SUN,WED,FRI;BYHOUR=05');
exec dbms_scheduler.enable( 'JOB1' );

I am using the client system to change the setting and the time zone differs from that of production. Even though the job is scheduled to run at 5 AM it show the start date as 6:30 PM which is the client system time.

View 1 Replies View Related

Performance Tuning :: Two UNION ALL - Modify Index Or Partition

Jan 27, 2012

We have a large customer table so first thought was to partition.Also we see two union alls in the plan - can we introduce parallelism? Below is the plan - have attached a text file if difficult to read

SELECT V_IDENTIFIER_LOOKUP.UID_V_IDENTIFIER_LOOKUP AS "UID",
V_IDENTIFIER_LOOKUP.ABA, V_IDENTIFIER_LOOKUP.ADDRESS1,
V_IDENTIFIER_LOOKUP.ADDRESS2, V_IDENTIFIER_LOOKUP.ADDRESS3,
V_IDENTIFIER_LOOKUP.ADDRESS4, V_IDENTIFIER_LOOKUP.ALIAS,
V_IDENTIFIER_LOOKUP.CITY, V_IDENTIFIER_LOOKUP.COUNTRYCODE,
V_IDENTIFIER_LOOKUP.CUST_CODE, V_IDENTIFIER_LOOKUP.CUST_NAME,
V_IDENTIFIER_LOOKUP.HEAD_OFFICE_IN,
V_IDENTIFIER_LOOKUP.IDENTIFIER,
V_IDENTIFIER_LOOKUP.IDENTIFIER_TYPE,
[code]...

View 1 Replies View Related

Forms :: Modify Data Will Require Automatic Authentication

May 8, 2012

I have made an application in forms 6i. Now i want if user can update, or modify the data it will required automatic authentication. Administrator will gives his password then the updation will continue otherwise rollback the transaction.

View 1 Replies View Related

Security :: Create Or Modify User Information In OID Using DBMS_LDAP?

Aug 25, 2010

I am trying to create a custom package that creates users as well as modify user information in OID using DBMS_LDAP package. I was able to create the package but I am stuck on specifying the orcladmin password for the procedure DBMS_LDAP.simple_bind_s(my_session,'orcladmin','orcladminpwd');

I need to get the orcladmin password dynamically. Is there a way to achieve this?

We are planning to provide our application users the ability to add new users / modify their own OID information.

View 2 Replies View Related

To Modify Dbca Template To Include Two Control Files

Nov 14, 2012

I'm trying to modify my dbca template to include two control files. One on ASM +RECO and we we're thinking of adding the second one on the database filesystem $ORACLE_BASE/something/something Within the template there are references to controlfiles two places:

Initparameter:
<initParam name="control_files" value="("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/control02.ctl")"/>

and

<StorageAttributes>
<ControlfileAttributes id="Controlfile">
<maxDatafiles>32767</maxDatafiles>
<maxLogfiles>1024</maxLogfiles>
<maxLogMembers>3</maxLogMembers>
<maxLogHistory>32767</maxLogHistory>
<maxInstances>8</maxInstances>
<image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
<image name="control02.ctl" filepath="+RECO"/>
</ControlfileAttributes>

With this configuration there is only one controlfile created and that is on +RECO. What am I doing wrong and also should I add the second controlfile to +DATA instead of fielsystem $ORACLE_BASE?

View 6 Replies View Related

Forms :: Modify Existing Form - Resize Canvas And Window?

Oct 30, 2010

I need to modify an existing form, thus I have to resize a canvas and a window for it. In windows, I run it and I got the right display. When I transfer it to dev server running in linux, then I compile and run it, it display the previous/unmodified size. I'm sure my form is the one that running, I placed some text there and it was displayed.

View 4 Replies View Related

SQL & PL/SQL :: Triggers / Procedures / Functions Used As Prompts To Update And Modify Database

Apr 16, 2013

I have my database structured to where i have my tables, constraints, and Rows populated in each table which is 4 tables in particular. for one I want to be able to List all four tables with its content and constraints all at once. What i want to with the PL/SQL is to be able to program a prompt to where the user can just enter the individual order information and the data goes to what ever appropriate column or Table it needs to go to. Now should I be mostly be using procedures, functions or triggers to make this possible.

View 9 Replies View Related

Client Tools :: Break And Compute Command Usage To Modify Output

Oct 14, 2010

I tried BRK and COMPUTE commands myself after reading the documentation but its not working...The output of my script is correct but What I want is the information to be displayed on a different way:

Original script for the report:

set serveroutput on size 1000000
set pages 10000
set lines 1000
set arraysize 1
set trimspool on
set heading off
[code]....

The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above.

View 3 Replies View Related

Application Express :: Modify Form Layout Of Existing Application?

Feb 22, 2013

I have to modify the Form layout of an existing application (Application Express 4.0.2.) where the data for each field is retrieved using SQL statements

Presently the form looks something like this and the Data for each field is rendered properly in this layout:

UNIT # :         123456
ADMISSION #: 2012101510         DISCHARGE DATE:                 ADMISSION DATE:
FIRST NAME:   JON                     LAST NAME: DOEFIRST NAME, LAST NAME and UNIT # are all "Display Only" item type.

What I'm trying to do is to move "FIRST NAME" underneath "LAST NAME" and "UNIT #" next to "LAST NAME" This part is very simple. However, the issue I'm having is as soon as the "UNIT #" is moved next to "FIRST NAME", the data for "FIRST NAME" and "LAST NAME" simply disappear

This is how it looks
ADMISSION #: 2012101510         DISCHARGE DATE:                 ADMISSION DATE:
FIRST NAME:                             UNIT #: 123456
LAST NAME:   

I have played around for hours and even created from scratch a new page with a new form using SQL statements to pull the data for each field, only to get the same result.

View 7 Replies View Related

SQL & PL/SQL :: How To Modify Null Column To Not Null

Jan 9, 2012

when i follow this steps mention on this website

[URL].........

to modify column from null to not null i got this error and on this website its show successful

my steps are

first i create a table

SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));

Table created.

Then insert some rows into Stu_Table

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');

1 row created.

SQL> select * from Stu_Table;

ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh

Table Structure is like this

SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)

now when i try to modify this Stu_id column to not null its give me error.

SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

and when i try to add new column with not null its also gives me error

SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

View 6 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

know if there is any way to move a partitioned table between table spaces of different block size?

View 14 Replies View Related

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

Jul 11, 2013

I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.

CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)

-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.

ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....

View 24 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Interface Table Compared With Normal Table - Result Dumped If Match Found

Aug 17, 2012

Oracle 10g, Windows XP

There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.

I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..

create table x_interface /* INterface table */ -- 5000 + rows
( name varchar2(80), addr_line1 varchar2(35), addr_line2 varchar2(35), addr_line3 varchar2(35),
addr_line4 varchar2(35), addr_line5 varchar2(35), addr_line6 varchar2(35), suffix varchar2(35),
city varchar2(15), state varchar2(10), zcode varchar2(10))
[code]....

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved