CODEWITHSUNDEEP
oracle-databaseplsql
user3203574
user3203574

Reputation:

MINUS operation not working as expected in Stored Procedure, Oracle

I have Oracle Stored procedure, which validates the data between two tables based on some manipulations. The Procedure has been working wrong since a recent change.(Added a new column) Not sure why is the reason. Tried to Analyses the data randomly, and we are sure there are no special characters in any of the columns.

I copied my table's spec and recreated the scenario.

Inserting Data

 BEGIN
 FOR I IN 1..4 LOOP
  INSERT INTO AR_TABLEA VALUES('Name'||I,21+I,'Address'||I);
  INSERT INTO AR_TABLEB VALUES('Name'||I,21+I,'Address'||I);
end loop;
 END;
 /

trying a MINUS operation

SELECT * FROM AR_TABLEB
MINUS
SELECT * FROM ar_tableA ;

Output:

NAME              AGE ADDRESS
---------- ---------- --------------------
Name1              22 Address1
Name2              23 Address2
Name3              24 Address3
Name4              25 Address4

But Expected Result shouldn't be "no rows selected" ?

This is just a test data, my original tables is big enough.

Thanks in advance, for any help!

Upvotes: 4

Views: 4601

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

try this please.

SELECT trim(name),age,trim(address) FROM AR_TABLEB
MINUS
SELECT trim(name),age,trim(address) FROM ar_tableA ;

If the Above Query worked for you, then one of the table has a column with CHAR data type, with the other having the same column has VARCHAR.

Please note that, A CHAR datatype always hold the contents with its maximum possible size. Say if you declare it as CHAR(10) and save a character 'A' in it, it gets saved as 'A ' (Appended with Spaces).

So either LPAD the VARCHAR2 column with spaces or TRIM the CHAR column when comparing or merging these data types.

Upvotes: 5

Related Questions