OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Difference (9i )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference [message #638376]

Wed, 10 June 2015 07:01

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

I want to find difference column from one schema to another

SCHEMA A
CREATE TABLE A.IP_RECEIVABLE
(
RECEIVABLE_CODE CHAR(15 BYTE) NOT NULL,
RECEIVABLE_DATE DATE NOT NULL,
MR_NO CHAR(12 BYTE) NOT NULL,
NEW_MR_NO VARCHAR2(25 BYTE),
CASE_NO VARCHAR2(15 BYTE),
RECEIVABLE_AMOUNT NUMBER(13,2),
BALANCE_AMOUNT NUMBER(13,2),
IP_BILL_NO CHAR(15 BYTE),
DISCHARGE_YN CHAR(1 BYTE),
BILLED_YN CHAR(1 BYTE) DEFAULT 'N',
CANCEL_YN CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
CREATED_BY VARCHAR2(30 BYTE) DEFAULT USER NOT NULL,
DATE_CREATED DATE DEFAULT SYSDATE NOT NULL,
MODIFIED_BY VARCHAR2(30 BYTE),
DATE_MODIFIED DATE,
MACHINE_CREATED VARCHAR2(30 BYTE) DEFAULT UserEnv('Terminal') NOT NULL,
MACHINE_MODIFIED VARCHAR2(30 BYTE)
)

SCHEMA B

CREATE TABLE B.IP_RECIEVABLE
(
RECEIVABLE_CODE CHAR(15 BYTE) NOT NULL,
BILL_NO CHAR(15 BYTE),
MR_NO CHAR(12 BYTE) NOT NULL,
RECEIVABLE_DATE DATE NOT NULL,
NEW_MR_NO VARCHAR2(25 BYTE),
CASE_NO VARCHAR2(15 BYTE),
RECEIVABLE_AMOUNT NUMBER(13,2),
BALANCE_AMOUNT NUMBER(13,2),
IP_BILL_NO CHAR(15 BYTE),
DISCHARGE_YN CHAR(1 BYTE),
BILLED_YN CHAR(1 BYTE),
CANCEL_YN CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
CREATED_BY VARCHAR2(35 BYTE) DEFAULT USER NOT NULL,
CREATED_DATE DATE DEFAULT SYSDATE NOT NULL,
MODIFIED_BY VARCHAR2(15 BYTE),
DATE_MODIFIED DATE,
MACHINE_MODIFIED VARCHAR2(15 BYTE),
MACHINE_CREATED VARCHAR2(15 BYTE) DEFAULT UserEnv('Terminal') NOT NULL
)

I WANT TO FIND COLUMN DIFFERENCE In SCHEMA A THERE ARE 17 COLUMNS and SCHEMA B THERE ARE 18 COLUMNS Table NAME IS SAME BUT SCHEMA'S ARE DIFFERENT

I have tried query below

select
t1.column_name as t1_column_name
, t2.column_name as t2_column_name
from
(
select column_name
from
dba_tab_columns
where
table_name = 'IP_RECIEVABLE'
and
owner = 'A'
) t1
full outer join
(
select column_name
from
dba_tab_columns
where
table_name = 'IP_RECIEVABLE'
and
owner = 'B'
) t2
on (t1.column_name = t2.column_name)
where
t1.column_name is null
or
t2.column_name is null

I am not Getting the exact result.

RESULT
T1_COLUMN_NAME T2_COLUMN_NAME
------------------------------ ------------------------------
RECEIVABLE_CODE
DATE_MODIFIED
RECEIVABLE_DATE
MR_NO
NEW_MR_NO
CANCEL_YN
CASE_NO
MACHINE_MODIFIED
MODIFIED_BY
RECEIVABLE_AMOUNT
BILL_NO
IP_BILL_NO
MACHINE_CREATED
BILLED_YN
DISCHARGE_YN
BALANCE_AMOUNT
CREATED_BY
CREATED_DATE

18 rows selected.

BUT IT has to SHOW Only one COLUMN which is not in one schema or either.

Thanks in Advance

Report message to a moderator

Re: Difference [message #638379 is a reply to message #638376]

Wed, 10 June 2015 07:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

use MINUS instead

Report message to a moderator

Re: Difference [message #638380 is a reply to message #638376]

Wed, 10 June 2015 07:09

flyboy
Messages: 1903
Registered: November 2006

Senior Member

If this is real code, you should take care of spelling, as for the owner A

'IP_RECEIVABLE' != 'IP_RECIEVABLE'

(by the way, the correctly spelled names are in minority).

Report message to a moderator

Re: Difference [message #638390 is a reply to message #638380]

Wed, 10 June 2015 08:18

glmjoy
Messages: 187
Registered: September 2011
Location: KR

Senior Member

Thanks a Lot

Report message to a moderator

Re: Difference [message #638402 is a reply to message #638390]

Wed, 10 June 2015 13:59

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

You could try using MINUS:

SELECT Column_Name
     , Column_Id
     , Data_Type
     , Data_Length
     , Data_Precision
     , Data_Scale
  FROM Dba_Tab_Columns C
 WHERE Owner = 'A' AND Table_Name = 'IP_RECIEVABLE'
MINUS
SELECT Column_Name
     , Column_Id
     , Data_Type
     , Data_Length
     , Data_Precision
     , Data_Scale
  FROM Dba_Tab_Columns C
 WHERE Owner = 'B' AND Table_Name = 'IP_RECIEVABLE'
/

Report message to a moderator

Re: Difference [message #638406 is a reply to message #638402]

Wed, 10 June 2015 23:59

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

is this not:

BlackSwan wrote on Wed, 10 June 2015 14:04

...
use MINUS instead

Report message to a moderator

Previous Topic:	What we should use for better function or procedure
Next Topic:	Procedure or Function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 26 07:01:17 CDT 2026