Forms :: Avoid Duplication In Three Tables

Jan 31, 2013

I'm creating oracle form to allow the user to register a new record in a table, I want to check the duplication in Four tables! which means, the user is able to save the record if it's NOT already registered in the other four tables.By checking P_ID (user parameter). this is my

Declare
v_count number;
begin
select count(*) into v_count ---Checking the 1st table
from 1_table
where p1_id=:p_id;
if (v_count>0) then
message ('Duplicate');
[code]....

View 4 Replies


ADVERTISEMENT

Forms :: New Record With Same ID Inserted Every Time - Duplication

Mar 4, 2013

I have a form that used to enter new employees , if the employee ID already registerd then the user can ONLY update the information and NOT adding the same ID with new info.

int the ID item in when-validat-item trigger I put this code

begin

IF :SOURCE_CODE IS NULL THEN MESSAGE('PLEASE ENTER SOURCE CODE');
ELSE
declare --chg#13-3169 nada

[Code].....

in the save button the code is : commit;

The problem Is : a new record with same ID is inserted every time !! Duplication is happining

View 6 Replies View Related

Forms :: How To Avoid FRM-40401 Message

Oct 16, 2008

While I am inserting and updating the values in the oracle database its showing a message 'FRM-40401 - No changes to save.' I don't want this message to be shown. How can I do that?

View 5 Replies View Related

Forms :: Avoid The User To Select Twice The Same Value From LOV?

Aug 21, 2010

i have created form. one field is there "Payment_terms" at line level. for that i created LOV containing 3 fixed Payment terms. my requirement is that Avoid the user to select twice the same value from LOV.

View 8 Replies View Related

Forms :: Avoid Duplicate At Entry Level

May 17, 2011

How can i avoid duplicate entry at entry level in form rather than when i pressed save button

View 2 Replies View Related

Database Duplication With Different DB ID And Oracle SID

Sep 3, 2010

I have the following question:

I try to duplicate a database on Host B with ORACLE_SID=hostb from an offline full backup plus archive log backup was taken from Host A with ORACLE_SID=hosta. Host A and Host B cannot communicate between them.

I don�t want to make a full restore of database, i want to make a duplicate database from that offline backup.

I need the duplicate database must be run with different ORACLE_SID and different DBID.

PD: I was reading the Oracle Documentation about that but i can�t find a step by step guide to follow.

Environment

Oracle 11gr2 with a Linux OS on x86_64 on host A and host B.

View 13 Replies View Related

Database Duplication Via RMAN

Nov 11, 2011

I have 2 identical databases set up as follows:

- Version: Oracle 11gR2 (64 bit)
- OS: Windows Server 2008
- File System: ASM (with GRID Infrastructure)

Both instances are virtually identical... Same directory structures, same SYS passwords. The only thing that differs is the instance name (one is ABC, the other is XYZ).

Now, both databases are up and running, both have users on them, and both can accept remote connections. What I want to do is set up a very simple 'copy' system so that everything can be extracted (via RMAN) from the first database, and replace the database on the second. (Then, the user can log into XYZ with the same usernames/passwords they used on ABC.)

Here's what I'm doing so far:

On the 'source' database (ABC) I run RMAN as follows:
rman target / nocatalog
configure controlfile autobackup format for device type disk to 'C:\temp\CF_%F';
configure channel device type disk format 'C:\temp\bkp_%U';
run {backup database; backup archivelog all; }

Now, I'm not completely sure of what to do on the 'destination' database. I've seen multiple web pages with instructions that require the creation of a new instance using 'oradim' and the creation of a new password with 'orapwd'. However, is that necessary if I have the same sys password on both databases, and I want to use the same XYZ instance name on the destination machine?

Would it work if I used 'delete database' on the destination, and then used the RMAN 'Duplicate database to XYZ backup location 'C:\temp\'? Or does it HAVE to be a new instance created with oradim?

And if I DO have to use oradim to create a new instance, will the data be accessible through XYZ too?

View 2 Replies View Related

SQL & PL/SQL :: Delete Duplication In A Table

Jul 7, 2011

I want to delete the duplicate rows in a table. there is no column which can be used to differentiate between the rows.

SELECT column_name1
FROM [table]
WHERE column_name2 = cond
GROUP BY column_name1
HAVING COUNT (column_name1) > 1

from the above query i can find the duplication in the table and can delete through it. But what i want one record of each duplication not to be deleted most probably the record added last to the table.

View 13 Replies View Related

Forms :: How To Avoid (no List Elements Defined For List Item) Error

Sep 27, 2011

I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".

I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.

View 1 Replies View Related

CHARSET ORACLE Database Duplication?

Sep 22, 2010

Can i duplicate a database that is in a different CHARACTER SET?

If i can't made the duplication in a direct way, how can i convert or transform to duplicate a database from one character set o another?

View 7 Replies View Related

RMAN ASM Duplication Orphan Files?

Jun 10, 2011

I use the following script to duplicate a target to an auxiliary instance. This works fine once.

However if I run it a second time it will create a new set of datafiles leaving the original files in place taking up space.

Is there something that I can put in the script that will stop this happening.

I know I could probably put a "set newname for datafile" clause for each datafile in the target database, but this makes the script over complicated and I would like to keep it as generic as possible.

DUPLICATE TARGET DATABASE TO dupdb
until time "TO_DATE('07/06/2011 07:25','DD/MM/YYYY HH24:MI')"
LOGFILE GROUP 1 ('+DATA') SIZE 50M,
GROUP 2 ('+DATA') SIZE 50M,
GROUP 3 ('+DATA') SIZE 50M,
GROUP 4 ('+DATA') SIZE 50M;

View 2 Replies View Related

Oracle 11g Active Database Duplication?

Nov 2, 2011

I'm trying to set up Active Database Duplication between 2 Oracle 11g servers. Each server is identical, with the following characteristics:

- OS: Windows Server 2008
- Version: Ora11R2 (64 bit)
- DB Name: orcl
- Filesystem: asm (using grid infrastructure)

In addition, each server has been configured with the same sys passwords. The databases themselves were configured during installation (so the setups are about as basic as you can get.)

Now, I've done all sorts of research into how to do this. I've found plenty of good sources, but they seem to all have differences in the details.Here's what I've done so far:

- The TNSNames.ora file on each server has an entry for 'src' and 'dest' (sourca and destination) respectively
- I created an entry named 'SID_LIST_LISTENER' in the listner.ora file on the source machine. However, should the sid_name in this entry refer to the source or destination machine? Or does this listener entry belong on the destination machine instead?
- A lot of the examples require the setup of a new password file and spfile. Are these entries necessary if I'm using the same file structures/user names/etc. on both servers?

View 2 Replies View Related

RMAN Duplication Of Active Database

Aug 5, 2011

I have issue with regards to duplication of an active (prod) database to a test database. This is my sql run by RMAN:

SPOOL MSGLOG to '/proddb/u01/app/dupe.log';

CONNECT target sys/oracle;
CONNECT auxiliary sys/oracle@MWTEST;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RUN {
duplicate target database to MWTEST from active database;
}
CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE;

It stops however in the middle and wouldn't continue. There is still huge space for the test database (3x the current size of prod database).

View 5 Replies View Related

Backup & Recovery :: RAC Database Duplication Using RMAN?

Jan 27, 2011

I have done many duplication before in sinlge instance databases using a backup tool and rman catalog. At the moment i was trying to do a RAC database duplication and facing some issues. For this i was doing a testing on my 1-node RAC. Primary and duplicated database is on the same Host and oracle version is 10.2.0.3. I perfomed following steps:

1.Created a DUPL database using DBCA.

2.Created a pfile using spfile.

3.Dropped all the datafiles, controlfiles and redo log files.

4.Started the database in nomount using pfile.

5.Disable cluster related parameters in pfile.

6.Took a full backup of the MAIN database with archivelog on disk.

6.Duplicate the database and it is done successfully.

7.Shutdown the instance and enabled cluster parameters.

8.Start instance using SRVCTL fails with error CRS-0215: Could not start resource 'ora.DUPL.DUPL1.inst'

But if start the instance using sqlplus it comes up nicely. There is not much information in alert log files.

View 13 Replies View Related

Backup & Recovery :: 11g Active Database Duplication?

Nov 2, 2011

I'm trying to set up active database duplication between 2 Oracle 11g servers.

Both servers were set up with identical configurations (same directory structures, file systems, SIDs, and sys passwords.) Both oracle instances were created at the time of software installation.

Here is how things are configured:
- Version: Ora11gR2
- Oracle service name: orcl
- File system: ASM
- OS: Windows Server 2008

I've done some research and found several sources giving instructions on how to set up active database duplication (including a blog engry by rajabaskar on this site), but each site seems to have different details.

So, here's what I have so far:

- Set up entries in each TNSNames file... each has an entry 'src' and 'dst' (for source and destination)

- A new listener has been added on the source machine

- on the destination machine, run the 'oradm' to create a new SID. However, is this really necessary? Since the service names are already the same

- I'm also supposed to create a password file on the destination machine using orapwd. Again, is this necessary, if I'm using the same passwords on both machines?

Am I on the right track? Should I have created an actual database on the destination machine or should I have started with just the software installed?

View 1 Replies View Related

Recovery Manager (RMAN) :: Perform Active Database Duplication?

Sep 11, 2012

I like to duplicate the database DATABASEA to DATABASEB and need to be run in same server. I tried to bounce the listener & databaseb instance multiple times but no lucky.

Step 1 : The instance DATABASEB is in Nomount stage

Step 2 :

==> tnsping DATABASEB
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 11-SEP-2012 07:36:54
Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = client )(PORT = 1521)) (CONNECT_DATA = ( SERVER = DEDICATED) (SERVICE_NAME = DATABASEB)(UR=A)))
OK (10 msec)

Step 3 :

==> rman target sys/*****@DATABASEA auxiliary sys/********@DATABASEB
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Sep 11 07:37:50 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: DATABASEA (DBID=1723462779)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

[code]...

View 9 Replies View Related

Recovery Manager (RMAN) :: Backup-Based Duplication Without A Target And A Recovery Catalog Connection?

Sep 30, 2012

how to duplicate it on oracle 11g xe windows 2003

i have read from documentation but getting some error.

in last

E:Documents and SettingsAdministrator>rman
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Sep 30 03:03:09 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect auxiliary sys/12
connected to auxiliary database: XE (not mounted)
RMAN>
RMAN> DUPLICATE DATABASE TO xe

[code]...

Backup-Based Duplication Without a Target and a Recovery Catalog Connection?

View 6 Replies View Related

How To Avoid Ora-03134

Apr 19, 2011

We have Oracle 8i, 9i, 10g and 11g. I installed oracle client 11gR2 and when I tried connecting to the database, its erroring out saying "ORA-03134 connections to this server version are no longer supported". What client version should I install in order to avoid this error?

View 2 Replies View Related

SQL & PL/SQL :: How To Avoid Max Function

Feb 22, 2010

I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.

way where max can be avoided or a more tuned query .

create table tab1 (sapid number,
denid number);
create table tab2 (sap_pkid number ,sapid number,
denid number,

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Avoid Divisions By Zero?

Feb 25, 2013

I have a query that performs the sum of a field and divides by the number of occurrences, got me a problem because if there are no occurrences would cause a divide by zero error in Query.

The solution I found might not have been the best, but at the time was possible.

I put a Case, when the sum of the number of occurrence is zero then return zero, otherwise performs the division of the sum of the values by the number of occurrences.

SELECT CLIENT_ID,
TO_NUMBER(TO_CHAR(DTPROC, ''YYYYMM'')),
DTPROC, EXCHANG_VAL,

[Code]....

Now this query is very slow, because have CASE,SUM and GROUP BY, so I need to optimize it.

do this validation (if the divisor is zero) more performance?

View 5 Replies View Related

PL/SQL :: Way To Avoid (group By)

May 14, 2013

select T2.name,sum(T1.area)
from (select * from country) T1,T2i wrote very simple just to show you the problem, bu i think here we should add "group by".
but is there any way to avoid group by?because i don't want to group any thing!

oracle11g

View 4 Replies View Related

SQL & PL/SQL :: Avoid Duplicity In String

Mar 5, 2013

I have created a procedure which sends mail to the users by using the smtp operation. I am collecting the email id of the users and then concatenating the same using the loop.

Everything is working fine, but if some person is coming multiple times in the loop, then his/her name is shown multiple times in the CC/BCC part (Although mail is sent once), it should be restricted to one time.

what i am doing is:

I have declared id_collector and it is concetenating the email as per the condition loop

........ some code here....
id_collector:=id_collector||email_id||';';
end loop;

Now, I want to check before concatenating that email already exists or not.

Example:

suppose value of id_collector= 'abc@gmail.com;pqr@gmail.com;rst@gmail.com;xyz@gmail.com;'
and again if email id comes - 'abc@gmail.com', then it should get rejected.

I think it can be done using reg_exp but I have messed up with that.

View 2 Replies View Related

PL/SQL :: How To Avoid Error ORA-01843

Aug 10, 2012

i am facing problem with date column.when i am trying to do execute a query this works fine

select null as link,'S'||to_char(DATE_OF_JOIN,'YY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'YY')

and when i am trying the below query its showing the only this year dates its not referring the previous years

select null as link,'S'||to_char(DATE_OF_JOIN,'MMDDYY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('1220' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0320' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'MMDDYY')

when i am trying below query its showng the error ORA-01843: not a valid month

select null as link,'Fall'||to_char(to_char(DATE_OF_JOIN,'YY')-1),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') group by to_char(to_char(DATE_OF_JOIN,'YY')-1)

View 11 Replies View Related

SQL & PL/SQL :: How To Avoid Multiple Function Calls

Jul 23, 2013

I have a function that is being called three time using UNION and wanted to know if this can be improved to just one call while incorporating all the table joins.

select field1,fdate,fname,username,stepnum from (
SELECT M.FIELD1,
TO_CHAR (M.FIELD_DATE, 'MM/DD/YYYY HH24MISS') AS FDATE,
M.FIELDNAME AS FNAME,
M.USERNAME,

View 10 Replies View Related

SQL & PL/SQL :: Avoid Mutating Table Error?

May 16, 2011

Best solution to avoid mutating table error?

View 11 Replies View Related

How To Avoid Parallel Execution In Database

Dec 5, 2012

In my database is present undesirable parallelization of the query. All involved tables has degree=1,

if I right understood there is no method to avoid this except reducing of degree of indexes ? I'm using 11.2.0.3

View 3 Replies View Related

PL/SQL :: Avoid Cartesian Join In Query?

Oct 24, 2012

must generate a Cartesian join, but I do not know why it happens. dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey are primary keys of each table. The problem is: I got same tuple for 8 times.

select dt.debtorkey, cl.clientkey,  inv.invoicekey, ag.agingkey, dt.DebtorNo, dt.Name as "debtor Name", dt.State,  cl.ClientNo, cl.Name as "Client Name",  inv.InvNo, inv.PurchOrd, inv.Amt,
to_char(inv.InvDate, 'MM-DD-YY') invoice_date,  to_char(ag.DateLastBuy, 'MM-DD-YY') aging_lastbuy, to_char(ag.DateLastPmt, 'MM-DD-YY') aging_lastpmt

[code]...

View 14 Replies View Related

Performance Tuning :: Unable To Avoid Indexes

May 20, 2011

Below query is getting delayed becasue of BitMap Indexes on the table. I am trying to avoid indexes by using Hints in the query but unable to do so, Details are as follows.

explain plan for
SELECT cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
[code]....

I have tried with ALL_ROWS & PARALLEL.how to avoid above two indexes in a query.

View 28 Replies View Related

SQL & PL/SQL :: How To Avoid Duplicate Rows From Being Inserted In The Table

Dec 21, 2009

I have one table in which I want to restrict some records from being inserted. I don't want to put any checked constraints. e.g. consider following table

transaction(
id number primary key,
txn_date timestamp(7),
payee varchar2(40),
amount number,
memo varchar2(40),
ref_num number
)

I want to write SQL which should not inset duplicate record.

e.g.

I have written one as bellow:

insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
(select count(*) from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12)=0;

Can I use exists/not exists, which query will be more appropriate. (Please consider that fields which I am using to filter out the duplicate transactions does not contain primary key.)

Can I write such SQL. Or do i check for duplicate rows one by one and then filter the duplicate records.

View 21 Replies View Related

SQL & PL/SQL :: Avoid NVL Condition Suppressing Index Usage?

Feb 1, 2012

I have index on column column1 and because of the nvl, the optimizer is not picking up the index

How can I restructure the query to avoid nvl giving same result using index on column1?

now column1 and column2 are varchar2 fields here

where nvl(column1,'*')=(nvl(:param1,nvl(column2,'*'))

can I use case statement like

case when column>'' then .. stuckup here

View 7 Replies View Related







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