PL/SQL :: Replicating Both The Tables (Master And Feed)
Jun 13, 2012
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
There are two tables one Master and another Feed table, both with 2 columns: ID & Price. Feed table gets truncated and re-populated every day.
Master Table Feed Table
ID, Price ID, Price
1 100 1 200
3 200 2 250
5 300 4 500
6 400 6 750
7 500 7 800
Create a query with that will update the Master table by the Feed table.
View 7 Replies
ADVERTISEMENT
Mar 4, 2011
while replicating the master site via materialized views, in mViewGroups i issued:
BEGIN
DBMS_REFRESH.MAKE (
name => 'mvadmin.hamza_refg',
list => '',
next_date => SYSDATE,
interval => 'SYSDATE + 1/1440', -- for test purpose i used such a small interval
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
in my previous attempt it worked all fine... i had a change in senerio, so i did my replication again from scractch using the same old spool in a new installation but even after 3 tries on replicating from scratch my objects are not getting refreshed automatically. even when i attempt to refresh the group it does not work. it works only when i refresh each of the participating object of the group manually.
View 4 Replies
View Related
Sep 17, 2012
We have an APEX application that leverage's APEX and UCM API to create a RSS feed that is displayed on the Content Portal.URL>....
The feed XML is converted to a JSON object and displayed on the home page using jquery.Up until last week this feed was displaying fine but now its broken and can no longer be seen on the home page.Our logs indicate that the feed content is being pulled as before without errors. URL....
A test page created to investigate the issue shows the following error -Resource interpreted as Script but transferred with MIME type application/rss+xml: URL>....
URL to the test page is :URL..... Could this possibly have to do with the recent upgrade on the APEX server as this worked perfectly earlier?Could the JSON support for RSS feed might have been affected?
View 1 Replies
View Related
Jan 31, 2012
I'm looking for a way to make CRLFs show in a CLOB.I'm feeding the insert statement with a concatinated string like this:
insert into table(Data) values (MyConc)
(MyConc is a string put togheter by another application)
Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string.Is there a character I can set in my string that automatically translates to a CRLF?
My CLOB-data should look something like this:
1;blue;Woodstock;;
34;giant;squid;attack;
5;blue;squid;;
And in this case the "MyConc" would look like this "1;blue;Woodstock;;[X]34;giant;squid;attack;[X]5;blue;squid;;[X]
where [X]=the character I need for CRLF =)
View 11 Replies
View Related
Sep 16, 2008
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....
View 3 Replies
View Related
Oct 31, 2008
I have 8 databases in multi master replication. I need to replicate only one rep object in to master sites. But I have 8 rep objects in the master definition site. is it possible to replicate the required rep object only without bringing the other rep objects in to replication.
View 10 Replies
View Related
Dec 24, 2011
Table script:
CREATE TABLE TEST_ITEM_BU_ID
(
CCN VARCHAR2(100 CHAR),
SKU VARCHAR2(100 CHAR),
BU_ID NUMBER
)
select * from test_item_bu_id;
CCN SKU BU_ID
------------------------------
M10000616-10502414545
M10000600-11437414545
M10000205-113380
M10000205-113390
M10000600-114370
The requirement is to replicate the bu_id records with bu_id=0 as bu_id=414545 ( there is no lookup available) so the same table should act as a lookup table to populate bu_id for the records where bu_id=0
i.e., here it will replicate for the sku set with bu_id value=0
M10000205-113380
M10000205-113390
M10000600-114370
will be replicated against
M10000616-10502414545M10000600-11437414545
so the output should be :
CCN SKU BU_ID
------------------------------
M10000205-11338414545
M10000205-11338414545
M10000205-11339414545
M10000205-11339414545
The below query is supposed to do this.
select a.ccn,b.bu_id,a.sku,b.sku
from test_item_bu_id a ,
( select distinct ccn,sku_num, bu_id
from test_item_bu_id
where bu_id in (414545) and CCN in ('M10000') ) b
where a.bu_id = 0 and a.sku <> b.sku and a.ccn= b.ccn
But we have wrong result here.
CCN BU_ID SKU SKU_1
----------------------------------------------
M10000414545205-11338600-11437
M10000414545205-11338616-10502
M10000414545205-11339600-11437
M10000414545205-11339616-10502
M10000414545600-11437616-10502
How can we avoid the last record, i.e., SKU=600-11437 since it is already having bu_id no need to replicate it, but it is getting replicated since the extra record with bu_id=0 exist for the same sku.
View 1 Replies
View Related
May 10, 2012
I have a SQL statement that returns a set of columns...but...when I create table as <SQL statement> I get the same columns but with 2 of the columns containing each others data, e.g:
SQL Select:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-4CG-3
Create Table as <SQL Select>:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-3CG-4
The SQL Select is correct and the Create Table As <SQL Select> is wrong.
Here is my SQL:
Create table ALTERNATENUMBERS as
SELECT ctry,
id,
MAX(DECODE(tp,'EN', RN)) EN,
MAX(DECODE(tp,'RN', RN)) RN,
MAX(DECODE(tp,'AN', RN)) AN
[code]....
Unfortunately I cannot give you any data (too much of it) and small scale testing works, it's only when I run it on the 11million records do I get some (not all), just some of the data being mixed up between columns.
Now, I've tried:
1. Using SQLPLus - no joy
2. Creating the Table and then inserting the data into a blank table - also no joy
3. Using a VIEW - no joy, listagg doesn't work in VIEW tables
I do understand that without data it's hard to replicate the issue but why this statement works as a SELECT but when written to a table has data anomolies?
View 4 Replies
View Related
Jul 3, 2013
I'm basically experimenting with the concept of replicating data from one instance to the other. Using this thread as a guide, I was wondering if I could create a service on an Apex 4.0 instance, and have an application apex.oracle.com consume that service?
View 0 Replies
View Related
May 3, 2010
I created one table with out primary key like well_1 .in that table have have more than 30 columns.
SQL> select uwi,analysis_obs_no,fluid_type,top,base,date_sampled from well_1
2 where uwi ='1000 and base =2871.5;
UWI obs_NO F TOP BASE
---------------- ---- ------ ----- - -----------------------------
1000 1 G 2870.5 2871.5
1000 3 G 2516 2871.5
1000 4 G 2870.5 2871.5
1000 2 G 2870.5 2871.5
1000 6 G 2516 2871.5
1000 5 G 2516 2871.5
1000 7 G 2516 2871.5
1000 1 L 2516 2871.5
1000 2 L 2516 2871.5
7 rows selected.
i did code like this in from in search panel. in taht form i will enter uwi and base values and iwill click search button.then it will display all record values in master block .after taht i will click next button then it will display next values(all values).
i am getting all values (1 G to 7 G values).but iam not getting last 2 records when i click next button (1 L to 2 L values ).
next button
select analysis_obs_no,fluid_type
into :ctrl1.OBS_NO,:ctrl1.fluid_type
from well_gas_anal
where analysis_obs_no =(select min(analysis_obs_no)
from well_gas_anal
[code].....
i tried in SQL*PLUS like this :
select rowid,analysis_obs_no,fluid_type,rownum
2 from well_gas_anal
3 where rowid=(select min(rowid)
4 from well_gas_anal
5 where analysis_obs_no > &a
6 and uwi ='1000' and base =2871.5 )
7 and uwi ='1000' and base =2871.5
8* and fluid_type is not null
SQL> /
View 1 Replies
View Related
May 17, 2013
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
View 15 Replies
View Related
Mar 11, 2013
How to find out which node is master node in oracle 9i RAC database.
View 7 Replies
View Related
Jun 29, 2011
In case of RAC the node which is up first is master node.
In case of 4 nodes RAC master node itself is down then how to identify which node is master node out of serving nodes
View 5 Replies
View Related
Oct 13, 2011
I want to delete Master / Detail Data through cursor between date 01-02-2010 till 10-02-2010. Problem is in Detail I dont have date column in detail. But I have to delete Master and Detail record with desire date. I have made a cursor but it delete only detail record i want to delete master record too.
Master Table
M_NO CHAR (12) NOT NULL,
REMARKS VARCHAR2 (200),
CANCEL_YN CHAR (1) NOT NULL,
M_DATE DATE NOT NULL,
PRIMARY KEY ( M_NO ) ) ;
Detail Structure
M_SNO NUMBER NOT NULL,
ACCOUNT_CODE CHAR (19) NOT NULL,
CANCEL_YN CHAR (1) NOT NULL,
M_DESC VARCHAR2 (200),
DB_AMT NUMBER,
CR_AMT NUMBER,
M_NO CHAR (12) NOT NULL,
PRIMARY KEY ( M_SNO, M_NO ) ) ;
create or replace procedure test as
cursor md_cur is
select m_No from master where m_Date
between '01-02-2010' and '10-02-2010';
[code]./....
View 9 Replies
View Related
Feb 20, 2012
I have face a problem with master detail block,
we have two tabular block first one is master and sencond one is detail when we insert detail item it proper work and when when move next record in master block then it ask for save, I want to user enter all record and when he save with button then it save other wise no. but when he move any record in master block all realted record show in detail block.
View 1 Replies
View Related
Feb 16, 2012
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
excel spreadsheet
col1 col2
9498 a784r
3645 a784r
4238 a784r
7091 a784r
0876 a784r
View 2 Replies
View Related
Aug 16, 2012
my problem is with cancel masterbutton, when i clear form through this cancel masterbutton it clear the form. but after this when i click on show button it is not working, although show button is working correctly before pressing cancel_query masterbutton. what should i consider for correct working.
View 3 Replies
View Related
Jun 9, 2011
Is there any way to design a form to act like Master Detail from one table which is not normalized?
View 2 Replies
View Related
Jan 25, 2012
I have a master detail form. I have one column in master, which is a free text field and one in detail block which is a LOV. When I enter data in the master block and select say A,B,C records in LOV in detail block and saved it. These A,B,C records in LOV shouldn't appear in the detail block when a new record is entered in the master Block.
View 3 Replies
View Related
Sep 29, 2010
what steps I am missing or provide a work-around?
1) cat sqlnet.ora
NAMES.DIRECTORY_PATH=(TNSNAMES)
WALLET_LOCATION =
(SOURCE =
[code]...
2) mkdir /u01/app/oracle/secure
ls -ld /u01/app/oracle/secure
drwxr-sr-x 2 oracle dba 512 Sep 28 17:04 /u01/app/oracle/secure
lsnrctl stop
lsnrctl start
3) sqlplus 'sys/sys as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 28 17:07:53 2010
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "DeciPher";
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "DeciPher"
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
View 8 Replies
View Related
Dec 9, 2008
I have a Oracle Form which has 2 blocks, master and Detail.I have a pre-Insert Trigger on master block which has a sequence that acts as the id of the primary key column. Now, when I populate the master block and dont save then go to detail block and save(after populating detail block's data) it gives me an error that can not insert null into detail block foreign key column.I understand that his is happening because in the above scenario pre-insert is not firing for the master block.Am I writing the the sequence generation code in a wrong trigger?
View 1 Replies
View Related
Feb 21, 2012
I have a form that contains a tab canvas. On the second canvas page I have two data blocks with a master detail relationship.The first block (master) is driven by a table (table_one) with the following fields:
sf_id (PK)
sf_code - VARCHAR2(40)
sf_desc - VARCHAR2(250)
The second block (detail) is a repeating block driven by a table (table_two) with the following fields:
error_code - VARCHAR2(40
error_code_desc - VARCHAR2(250)
is_major_error - VARCHAR2(1)
error_type - VARCHAR2(1)
error_is_active - VARCHAR2(1)
update_date - DATE
sf_id (FK - table_one.sf_id)
The second block only shows error_code and error_code_desc. The point of this canvas page is to add/remove master records and associate detail records with a master. I never want to actually insert or remove records from table two; merely updating the sf_id column in table two.What I am trying to achieve is the following:
1. Create a master record with detail records at the same time.
2. Delete a master record
3. Add and remove detail records
An insert into the detail block is nothing more than associating a master record with a detail record by updating the sf_id column in table two. A delete from the detail block is nothing more than setting the sf_id column to null in table two. I am not actually adding or removing records in table_two.
I have tried overriding the commit on the second block to perform updates instead of Oracle actually trying to insert NEW records into table_two.
View 2 Replies
View Related
Feb 20, 2012
I am trying to query a form having two data blocks. one is a master and the other is a detail block. the problem is when i insert data in the form, save it, and press enter query key, the data in the master block fields are still there. while the data in the detail block field are cleared and i can insert values. how to clear data from master block field so i can query
View 2 Replies
View Related
Oct 27, 2008
I have a multimaster replication between 2 sites (BASE1 and BASE3). BASE1 is the master definition site. I want to add a new master site without acquiescing the master group (named GM_ADMINISTRATEUR).
First, I have used the script provided by Oracle :
DBMS_REPCAT.SPECIFY_NEW_MASTER (
gname => 'GM_ADMINISTRATEUR',
master_list => 'BASE2.WORLD' );
and
DBMS_REPCAT.ADD_NEW_MASTERS( ....);
The first time, I had Errors, so I use the command
DBMS_REPCAT.UNDO_ADD_NEW_MASTERS_REQUEST
and I drop the master site BASE2 (connected as repadmin to BASE2 site).
The master site BASE2 is not present at BASE1 master definition site, The master repgroup is not present at BASE2 BUT, the master site BASE2 is always present at BASE3 master site. I think that the command UNDO_ADD_NEW_MASTERS_REQUEST undo the changes on BASE1 (the master definition site) but don't undo the changes on the site BASE3.
And now, when I want redo the add_new_masters, the command
DBMS_REPCAT.ADD_NEW_MASTER
return a error on the command ADD_NEW_MASTER in the DBA_REPCATALOG for the following reason:
ORA-00001: violation de contrainte unique (SYSTEM.REPCAT$_REPSCHEMA_PRIMARY)
ORA-06512: � "SYS.DBMS_REPCAT_UTL", ligne 4484
ORA-06512: � "SYS.DBMS_REPCAT_RPC", ligne 1758
And I can't continue because the new master site BASE2 does not appear at BASE1 master definition site. I think I have to drop the master site BASE2 at the master site BASE3, but I dont know how to do this.
**** Following information at BASE1 (master definition site : *****
prompt Replication schemas/ sites
Replication schemas/ sites
select
sname,
masterdef,
[code].....
View 14 Replies
View Related
May 20, 2011
I have a form which consist of three form
one is master and other are details
I take one value at master level in primary key at starting point at entry, which is passed to details table
i want to update value of primary key at the last save level
but erro fired child reocrd found
View 3 Replies
View Related
Mar 27, 2010
I'm designing a canvas which will open up on the click of a push-button from a form A. Now this form A has a data block(ie. single underlying table A).
I want to display data from two tables - Table B and Table C in this canvas in the form of a multi-record grid structure - one below the other i.e. table B data above and table C data below.
Now table B and table C share a PK-FK relnship between them. What I want is when a user clicks on a record in the table B above then the corresponding records in the table C should get displayed below(now there can be multiple detail records in table C for a single record in table B - PK-FK reln)
i'd written code something like this in the When-button-pressed trigger:-
c_where_clause := ....(query from Form A)
set_block_property('block B',default,c_where_clause);
go_block('block B');
go_block('block C');
execute_query;
first_record;
(in addition to the above trigger, I have also included queries in the post-query trigger of the two tables B and C to get some data populated in the non-database fields in this canvas)
do i have to do add a piece of code to loop thru the records...like checking for last record,if not going to the next_record and so on for both the blocks?
View 1 Replies
View Related
Oct 11, 2010
1.steps to create master-detail form using form builder 6i ,db oracle 9i.
2.Using dept and emp(database table)
3.one more thing difference between cascading/isolated while creating relation b/w dept and emp using join method
View 6 Replies
View Related
Oct 18, 2008
I get below error when i tried to add the master site to the master definition site.
SQL> BEGIN
2 DBMS_REPCAT.ADD_MASTER_DATABASE(
3 gname => 'TEST',
4 master=> 'testdb2',
5 use_existing_objects => FALSE,
6 copy_rows => FALSE,
[code].......
View 5 Replies
View Related
Nov 28, 2010
i want to ask, that in a master detail form, when a user enters few records in detail block against a master record, then when creating a new master record the form builder prompts to save the form first (with yes, no, cancel) option, if we hit no, the records in the detail block are washed away (while the master records remains)
is there any option that i can create several master records having associated detail records before saving them all at the end.
View 25 Replies
View Related
Dec 24, 2007
m trying to create replications of data from the M.V. site. to master site
/* AT MASTER SITE */
1. MASTER REPLICATE GROUP
2. MASTER REPLICATE OBJECT
3. REPLICATION SUPPORT
4. REPLICATION ACTIVITY
5. M.V. LOG
/* AT M.V. SITE */
1. MATERIALIZED VIEW (M.V)
2. DBMS_REFRESH.MAKE (REFRESH GROUP)
AND I AM NOT ABLE TO CREATE
1. CREATE_MVIEW_REPGROUP
2. CREATE_MVIEW_REPOBJECT
IN M.V. SITE , and i am getting following error while creating REPGROUP
CODE
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
3 gname => 'emp_repg',
4 master => 'orc1',
[code]..
View 1 Replies
View Related
