Creating SQL Script That Can Update Info From One Table In Dbase1 To Another Table In Dbase2?

May 16, 2013

creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?

View 3 Replies


Error In Creating Update Table Materialized View

Aug 28, 2013

we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.

There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.

in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:

SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh

how to create such MV on this scenario?

our environment is:


View 1 Replies View Related

SQL & PL/SQL :: Getting Info About A Table Through Query?

Mar 10, 2011

Is there a way to retrieve information about a table through an sql query? In particular I need to retrieve the size of some varchar columns.

View 3 Replies View Related

Retrieving Info From Database And List Out In HTML Table?

Feb 21, 2011

I am having a little trouble retrieving info from my mock database.

here are my definitions:
response.setHeader("pragma", "no-cache");
response.setHeader("Cache-control", "no-cache, no-store, must-revalidate");
response.setHeader("Expires", "01 Apr 1995 01:10:10 GMT");
response.addHeader("Cache-Control", "post-check=0, pre-check=0");


and here is my select statement.
ds = (DataSource) context.lookup(dSource);
conn = ds.getConnection();
stmt = conn.prepareStatement("select add_date, campus, name, leader from TABLENAME where id = ? order by xbbo_add_date");
stmt.setString(1, colleagueID);
result = stmt.executeQuery();

***basically I am trying to retrieve this info from my db, and list it out in a HTML table. I am writing this on a JSP page. Also, where i want the retrieved data listed, I am using for ex: <%=name%>

View 5 Replies View Related

SQL & PL/SQL :: Retrieve All Data From Table And Print Out Each Customer Info One By One

Nov 15, 2010

How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:

CURSOR all_customer IS
FROM customer;

but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.

View 13 Replies View Related

SQL & PL/SQL :: Create Trigger To Update Table B After Update On Table A

Jul 21, 2011

I have table test1(id,name) and table test2(id,,name)

Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.

So I wrote this trigger but its not working

create trigger test_trigger after update on test1 for each row
update test2 set where

View 9 Replies View Related

Creating Table From Existing Table In Another Schema?

Jan 4, 2009

I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?

View 4 Replies View Related

Create / Update Query To Use As Trigger For Updating Info In DB Dependent Upon Time

Dec 7, 2010

I am trying to create and update query to use as a trigger for updating information in a database which is dependent upon time.

I have a severity level which needs to be decreased in value after a set amount of time. the time of the action is recorded automatically on the creation of the the record. after an hour anything at severity level 2 needs to go up to 1, after 2 hours level 3 goes up to 2 and 3 hours level 4 goes up to 3

so far this is what I have

UPDATE calllog
SET timedate = current_timestamp, severityid = severityid - 1
WHERE ((severityid = 2 and timedate >= current_timestamp + (1/24)) or
(severityid = 3 and timedate >= current_timestamp + (2/24)) or
(severityid = 4 and timedate >= current_timestamp + (3/24)))

I am not getting any errors back from this sql but it is not updating the relevent information. I am using one cell for time and date in format = DD-MON-YYYY HH24:MM:SS.

View 3 Replies View Related

Application Express :: Insert Into History Table When Update Action Is Performed On Tabular Form View Or Table

Aug 24, 2013

My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.

So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).

View 2 Replies View Related

Performance Tuning :: Update Columns Of One Table Using Another Table

Feb 6, 2011

I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:

Query 1

Update TableA A
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)

Query 2
Update TableA A
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)

When i execute these two above queries, it keeps executing indefinitely.

View 4 Replies View Related

Oracle 10g - Take Snapshot Of Table Before Insert Or Update Happens To That Table

Dec 28, 2010

I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..


how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?

assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?

create materialized view my_view refresh fast as select * from t1;

create materialized view log on my_view;

and then schedule in a dbms_jobs?

View 1 Replies View Related

SQL & PL/SQL :: Update A Table Using Another Schema Table Of Same Style?

Aug 14, 2011

Can we update a table using another schema table of same style?


Update employees
set employee_name=hr.employees.employees_name
where employee_id=100;

View 8 Replies View Related

SQL & PL/SQL :: Update Table From Other Table Over Database Link

Aug 14, 2010

i have two databases and created the link between them. I can easily query the data but when i need to update my local records from the remote its showing an error

SQL> update laptop set name =
2 (select name from laptop@ora_link1 where id between 2 and 4)
3 where id between 2 and 4;

(select name from laptop@ora_link1 where id between 2 and 4)
ERROR at line 2: ORA-01427: single-row subquery returns more than one row

select multiple rows from the remote db and update them in the local db.

View 2 Replies View Related

Forms :: Insert And Update Directly Into Table From Pre Update Trigger Of Block?

May 14, 2010

I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:

If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.

Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?

View 4 Replies View Related

SQL & PL/SQL :: Create Trigger That Will Update Table When There Is Insert / Update

May 29, 2012

i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".

*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

*Action: Rewrite the trigger (or function) so it does not read that table.

BEFORE INSERT OR UPDATE OF issued ON shares_amount
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;

View 3 Replies View Related

Using Trigger On One Table To Update Another Table

Aug 28, 2011

My problem is the following:

At the time when P_delivery_date changes in P_ORDERS I want to transfer P_delivery_date to S_delivery_date in S_ORDERS for corresponding records.


purchase orders table P_ORDERS:

sales orders table S_ORDERS:

My question is:

Is it possible and what would be the solution using the TRIGGER on table P_ORDERS to update S_delivery_date with P_delivery_date in S_ORDERS?

View 2 Replies View Related

SQL & PL/SQL :: Update Table After Insert To Another Table

Aug 8, 2012

how to adjust a total (counter) after a record is inserted into a table.

the dilemma i am facing is we are using third party software for our fundraising operations so I have no control over what gets done in the background as users process their daily batches into the system. below is the scenario:

create table paytable(
idnumber number(12),
appealcode varchar2(20),
batchno number(8),
trantype varchar2(3),
transnum number(8),
split_transnum number(8));


---------- -------------------- ---------- --- ---------- --------------
16084 DVFG1206 20120808 PP 23853576 1133821
16084 DVFG1206 20120808 PPO 23853577 1133821
1234 DVFG1206 20120808 PP 23853578 1133822

create table appealtable
(appealcode varchar2(20),
total# number(9),
total$ number(13,2));


-------------------- ---------- ----------
DVFG1206 100 2500

during batch posting records are inserted into the paytable, on some pledge donations donors will send overpayments when fulfilling a PLEDGE(as is the case with donor 16084) therefore the system will split the payment during the process and will assign a trantype of 'PP' to the exact pledge amount and a 'PPO'(pledge payment overage) towards the balance. additionally as records get inserted into paytable there is counter of those paytable records going into the appealtable for that particular appealcode so in the case above when batchno 20120808 is completed will show 103 and total$ will show $2532($10,$12,$10,,,I did not include payment$ since that is not the focus of this issue and will not change).

mgt wants the counter into the appealtable to be 2 instead of 3 records since the two records that were split(same split_transnum) should be recorded as one response not two.

I have tried writing an after insert trigger(dreaded mutating table error) and can't seem to figure out how to update the counter to the appealtable after records are inserted into paytable. below is some code I've been working with but it's not working.


p_cnt NUMBER :=0;


show errors

View 1 Replies View Related

PL/SQL :: Update Table Based On The Value Of Another Table

Apr 21, 2013

Using Oracle 11g SOE R2...

I used to have two tables to store details of PROPERTIES e.g UNIT_COMMERCIAL , UNIT_RESIDENTIAL. I need to combine both of them into one table called UNIT. I moved the data to the new table, but now I am stuck how to update the values of the child table which called MARKETING.


Now, I have two tables:


I need to update the value of OLD_UNIT_ID in Marketing table to be equal to NEW_ID in the table of UNIT. As you can see the values of OLD_UNIT_ID in both tables are the same.

I used this statement

update ( SELECT  distinct  M.UNIT_ID , U.OLD_ID , U.ID FROM MARKETING M INNER JOIN UNIT U ON (M.UNIT_ID = U.OLD_ID))  set unit_id = idBut I got this error:

ORA-01732: data manipulation operation not legal on this view??

View 8 Replies View Related

PL/SQL :: Update Table With Values From Another Table

Dec 19, 2012

I've tried to Update table with values from another table, but I couldn't.Is there a single way to do that with just one query?Below goes an example:

     DCR VARCHAR2(10) );

     DCR VARCHAR2(10),
     DCR2 VARCHAR2(10) );

I want to UPDATE the field DCR of the table TEST1 with the VALUE of the field DCR2 of the table TEST2.At the end, after the update, the table TEST1 would be like that:


1 'TESTE3'

View 6 Replies View Related

PL/SQL :: Update Row In Table And Insert Same Row Into Another Table?

Sep 6, 2013

I want to update a row in a table say Table A and the updated row should be inserted into another table say Table B. I need to do it in a single SQL query and i don't want to do it in PL/SQL with triggers. And i tried with MERGE statement but its working with this scenario.

(Note: I'm using Oracle Database 10g Enterprise Edition Release

View 8 Replies View Related

SQL & PL/SQL :: How To Update Multiple Table With Single UPDATE

Jul 19, 2011

I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).

View 4 Replies View Related

Creating Table With A Constraint?

Oct 16, 2006

how to i insert a constraint of words into the table example below.I am new to this stuff man.

Create table Orders
orderID Number(8) Primary Key,
orderDate Date Not Null,
methPmt Varchar2(10),
custID Number(5),
orderSource Number(2),
Foreign Key(custID) Reference Customer(custID),
Foreign Key(ordersource) Reference OrderSource(ordersource)

The catch is I am required to enter a constraint of the methPmt will only take values of "CASH", "CREDIT" or "CHEQUE" only.

How am I suppose to enter this constraint value into the creation of this table?

View 3 Replies View Related

Creating Table In A Directory

May 2, 2013

I have a directory defined as TDM_IN where a directory has been created. There is a table which has been created as

create table abc.IFP_GRP_D_INT


Now how can i select the records from this table?

View 3 Replies View Related

Error While Creating Table In Oracle 10g?

Aug 19, 2008

If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...

ORA-00911: invalid character
create table shopper
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,


View 1 Replies View Related

Creating A Trigger To Populate Another Table

Jan 4, 2007

Having trouble creating a trigger to populate another table.

The SQL:



I get an invalid trigger specification.

View 3 Replies View Related

Returning A Table Without Creating Objects?

May 4, 2010

I'm creating a stored procedure where i get to return (OUT parameter) a cursor that points to a custom table. If I create an object, I could just do something like:

PROCEDURE tmp_proc (...,
out_param_resultset OUT g_cursor_type )
OPEN out_param_resultset FOR
SELECT * FROM TABLE(CAST(tmpObjTbl AS TmpObjTblType));

How do I return the table (referenced by a cursor) without creating objects?

View 1 Replies View Related

Getting Random Results Creating Table

Jan 19, 2012

I am trying to create a csv file with summarized data. We have a huge table with claim details that is constantly being updated. I am pulling a subset of records that match my criteria into a tempory table (not technically an Oracle temporary table, a regular table that will only exist until I drop it when I am done). This table has multiple entries per claim with different effective dates, paid dates and amounts paid. The result file needs to have one entry per claim with the oldest effective and paid dates and the total of all of the amounts paid on that claim.

Originally I was doing create table new_table as select claim_nbr,other data...,min(ymdeff),min(ymdpaid),sum(amtpay) from my_table group by claim_nbr,other data...

If I ran a select sum(amtpay) from my_table and select sum(amtpay) from new_table I was not getting the same results... If I ran select count (*) from (select distinct claim_nbr from my_table) and select count (*) from (select distinct claim_nbr from new_table) or select count (*) from new_table I was getting the same number of rows. So I wasn't completely losing claims from one table to the next, just some of the details. So, I tried running this:

select * from
(select claim_nbr,sum(amtpay) paysum from my_table
group by claim_nbr
order by claim_nbr) m,
(select claim_nbr,sum(amtpay) paysum from new_table
group by claim_nbr
order by claim_nbr) n
m.claim_nbr = n.claim_nbr and
m.paysum <> n.paysum;

It came back with the claim number causing the issue. I looked at all the entries in my_table for that claim and every field was identical except the ymdeff, ymdpaid and amtpay. There were 4 records in my_table however the amtpay in new_table was only a sum of 2 of the records... I our admin look over my shoulder to see what was wrong and they wanted me to recreate new_table. So I dropped new_table and ran the exact same SQL to recreate the table. The number of distinct claim numbers was still the same in both tables and the sum of new_table was off but not by the same amount. I ran my comparison to see which claim was off and now there were two claims where the totals didn't match and neither were the same as the claim that was wrong that first time. We dropped new_table and recreated it several times and every time we got different results... No one else knows the name of my_table so no one was messing with it at the same time plus the sum of amtpay in my_table always comes back the same.

Our admin said he thought he remembered there being something "funny" with the min function sometime so he had me remove those fields. Ran the query several times and the total came out correct each time. Well I still need the dates so I came up with another way (very convoluted) using subqueries and ranking. It seemed to work at first then it started losing random numbers of claims (fewer rows in new_table than distinct claims in my_table) or keeping all the claims but dropping detail lines like I had using the min functions.

Here is the backwards way around using min that drops whole claims sometimes but works fine other times:

CREATE table new_table
(select claim_nbr,other data...,amtpay,ymdeff,ymdpaid
(select claim_nbr,other data... ,sum(amtpay) amtpay
from my_table
group by claim_nbr,other data...

[Code] ...........

View 8 Replies View Related

SQL & PL/SQL :: Specify Condition While Creating External Table

Feb 10, 2011

While creating external table how can I specify a particular decode condition for a date field that comes in as '2099-99-99' i want to change it to '2099-01-0001', how i can translate it

I already have this in the access parameters..


View 2 Replies View Related

Security :: Creating Table In Other Schema

Dec 29, 2011

I have a query ,

I need to create a structure DATABASE=>SCHEMA=>TABLE as

DB=>SC=>EMPLOYEE ...but after connecting database i could create table only user my user schema(own schema)only . I want to create a new schema called SC as public and need to create a table .

View 2 Replies View Related

SQL & PL/SQL :: Getting Error While Creating Backup Table?

Oct 17, 2013

While creating temp backup table it shows error ORA-00904 'invalid identifier'

CREATE TABLE xxom_valvelink_vlvs_temp AS
SELECT msie.inventory_item_id,FDFC.application_column_name,


View 10 Replies View Related

Copyrights 2005-15, All rights reserved