I have approximately 1200 transaction to be updated to a master table. There are other columns in the master table but only one column is being updated. I would like to use sqlloader if possible or any other efficient means. Those 1200 record is stored in an excel spreadsheet. The col1 of the excel spreadsheet have to match col1 of the master table inorder for update col2 from the excel spreadsheet. Here is an example of the data. My operation system is HPUX and database is Oracle 10g.
Master table
col1 col2 col3 col 4
4238 susan 56e
5879 h698c rich 12g
7091 joyce 34b
0876 mike 25n
7501 k956b robert 87c
9498 angela 67r
3645 doris 92y
Submitting transaction / master data through API without exposing DB credentials...I am developing a scenario where I will submit transaction / master data like sales order / master item to Oracle apps (EBS) through MS Excel via API / interface table but like to provide only apps (EBS) user credentials rather then DB credentials so DB password could not expose to normal end user.
I want to remove the master site which is in the multi master replication environment.
I have a doubt here. When I try to suspend the master activity,I need to give the gname.
BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'NAGADMIN'); END; /
Where gname=master group name.But I have 9 master groups in my database. If i give one of the gname present in the master site will suspend the replication of the entire database from the replication.
eg; i am only giving NAGADMIN gname in the suspend activity script.. I have other gnames like, NAGUSER,NAGAUTH, etc....
Let's say we have Table - A and we would like to replicate specific row transaction to Table B.
Here are the rows in *Table A* Time: Lets say 15:00
A1 Just Updated @15:00 A2 Just inserted @15:01 A3
B1 - Daily Delete Row -i.e just deleted a while back - Non scheduled process --executed by application @15:02 B2 - B3 - Daily Delete Row - i.e just deleted a while back -- Non Schduled process --executed by application @15:05 B4 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10 B5 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10 B6 -Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
Current Data in Table B (Before Replication) @15:00
A1 (without updates) A3 B1 B2 B3 B4 B5 B6
Expected rows in Table B (via replication/snapshot/materialized view / or any other method)
*Replication at 15:30* Table B - Read Only
Expected rows after replication-
A1 -- Newly updated details A2 -- Newly inserted row A3 B1 - Daily delete row is expected to be replicated B2 B3 - Daily delete row is expected to be replicated
***Note row B4 is not expected to be replicated to table B.
Questions:
1) How can we get updates, inserts and daily deletes replicated while ignore large purges? 2) How can large purge changes be reflected in replicated tables as well without deleting daily deletes?
I want to know that How to find which table got last updated and how to find last DDL and DML operation obtained in which table? here I know the table name
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_ --------- 20-MAR-12
SQL> TRUNCATE TABLE PREM;
Table truncated.
SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';
LAST_DDL_ --------- 10-APR-12
Note: With out enable the auditing I want to know that .
There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.
TABLE A COL_A1COL_A2
AAAMOBILE BBBTABLET CCCDESKTOP DDDUNKNOWN
TABLE B COL_B1COL_B2
AAAMOBILE BBBUNKNOWN CCCDESKTOP
The select statement should return following rows from TABLE A
when the tables are updated, the following detals must be correct to ensure that the links in the affected tables are in place.
PLUPDATE_NEW(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_NEW (PLUP_NEW_BENEF_SAVE_SEQ field value) PLUPDATE_OLD(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_OLD (PLUP_OLD_BENEF_SAVE_SEQ field value)?
[Code]....
i tried this code, what should i do in the link for this tables?
The requirement is, the combination of col1,col2,col3 and col4 should always be unique, and wherever the col1, col2,col3 are same then col4 should be the sequence, starting from 1. Likewise the data should be updated back to the table.I'm able to do this using PL/SQL. Can I do the same using a single update statement?
I want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0
sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table using another procedure Named X that Merge the data from Global Temporary against the Normal Table (inserting if not exist and updating some fields if exist).
(X isn´t important on the new flow)
Now, i need to add some steps on the normal flow:
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table. Using the Data from Global Temporary Teble i must to Store some fields on another normal table, for this i use another Procedure named C that merge the data from Global Temporary Table against the data from normal table, and i must to commit at this point. X Merge the data from Global Temporary Table and the data from the Normal table con the procedure "C" against another Normal Table (inserting if not exist and updating if exist).
The issue that i´m expecting is that i can´t use "C" for merge and commit, because this truncate the data on the global temporary table. I can´t change the on commit delete rows option, because another procedures are using this Global Temporary Table on production.
Before you ask, i try using AUTONOMOUS_TRANSACTION on "C" and didn´t works because "C" can´t found data on the Global Temporary table ( i use a select count on "C" from Global Temporary), this is because The Autonomous_Transaction (i think). So, what i can do? I tried to trace the session Number on C and A and with the AT is the same ( so isn´t session change problem).
I need:Commit on the Procedure "C" without Truncating Global Temporary Table because i need this data for X.
The Scenario is that we have Master and detail table (With Foreign key enabled), we want to TRUNCATE Master table.
1) Is there any option which can Truncate the table without disabling the constraints for child tables...we want to Truncate the table forcefully.. 2) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are records in child table) 3) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are NO records in child table
I have a multi record block . I need to implement a functionality where the user updates a field in the record but the form saves the previous version of the record in another table ..
I am having a table with 4 columns as mentioned below
For a particular prod the value greater less than 5 should be rounded to 5 and value greater than 5 should be rounded to 10. And the rounded quantity should be adjusted with in a product starting with order by of rank with in a prod else leave it
I have taken all the records in to a cursor. Once after rounding the request of 1st rank and adjusting the values of next rank is done. Trying to round the value for 2nd rank as done for 1st rank. Its not taking the recently updated value(i,e adjusted value in rounding of 1st rank).
This is because of using a cursor having a value which is of old value. Is there any way to handle such scenario's where cursor records gets dynamically updated when a table record is updated.
I have two table master and detail i want to update both tables with update command in master i want to update voc_date and in detail i want to update item_code in one command but i am not understand how to control this query for example when i use this command
1). update master set voc_date=sysdate it works very well 2). update detail set item_code='12345' it also work very well but i want to update master,detail table in one query pls guide me with some query example
we are creating item_codes in our master table called om_item ,and then at one point of time every day we are shipping these newly created items based on date and tmie stamp as items.sql to our brance office and the person there will run these scripts there.Actually there are two persons involved in this process and i want to remove this manual intervention.All i need is to create a trigger for this insert statements and write it in text file as .sql.
create table om_item (item_code varchar2(20), item_name varchar2(60),item_cr_dt date) insert into om_item values ('a','aaaa',sysdate); commit; insert into om_item values('b','bbbb',sysdate+1); commit;
I have a contact table that will be used for more than one entity (Customers, Sites, Suppliers, etc.) Using APEX, when we define the master-detail, we need to add a FK to the Detail table -- otherwise the Master-Detail Forms can not be implemented.
I am looking for a way to defined/implemented tables so that I can utilize the same table (Contacts) for more than one Master-details relationship.
I have created a wallet (11g R2 OEL 5.5) using the OWM.Tried opening the wallet (encryption_wallet_location set in sqlnet.ora). then while creating a table it said the master encryption key is not present. Have created the master key using the following command.
alter system set encryption key identified by "Password";
Here the strange thing i observed is that when we create a wallet using the OWM, it asks for the password and when i open the same wallet the master key is not created and it allows the master key to be generated with the same password that i have created the wallet in the first place with the OWM, with any other passwords it says that the wallet is not open.
After creating the wallet and creating the master key... I have the following questions, and its becoming quite hard to find the solutions as well.
1. Can we have multiple encryption keys... say i want to encrypt a table or column with one key and other with an another key. 2. How many keys can we have for objects in the table? or can we have only one key and many certificates. 3. wallet created, and encrypted tables present, the wallet is not in auto open mode, but somehow the database open after it is shutdown, here no encrypted tablespaces are present. 4. while creating an encrypted tablespace the default storage (encrypt ) has to be added to the add tablespace clause.
