utPLSQL: How can i compare two tables?
Lets say i have table 1 and table 2. Table 2 is the updated version of table one. It can have same or updated structure, columns and data. I want to compare those two tables.
oracle unit-testing plsql utplsql
edited Nov 21 at 16:33
APC
118k15116229
asked Nov 21 at 5:40
Success Shrestha
1408
add a comment |
Lets say i have table 1 and table 2. Table 2 is the updated version of table one. It can have same or updated structure, columns and data. I want to compare those two tables.
oracle unit-testing plsql utplsql
edited Nov 21 at 16:33
APC
118k15116229
asked Nov 21 at 5:40
Success Shrestha
1408
Are you really using the UTPLSQL framework? Some of your previous questions suggest you were hand-rolling your unit tests. If you are using UTPLSQL please clarify whether v2 or v3 - they have completely different APIs.
– APC
Nov 21 at 12:55
Yes, i'm using utPLSQL v3. I have already found the solution. Thank you.
– Success Shrestha
Nov 21 at 15:21
1
If you have found a solution then please post it as an answer, It may help other Seekers in the future.
– APC
Nov 21 at 16:33
add a comment |
Lets say i have table 1 and table 2. Table 2 is the updated version of table one. It can have same or updated structure, columns and data. I want to compare those two tables.
oracle unit-testing plsql utplsql
edited Nov 21 at 16:33
APC
118k15116229
asked Nov 21 at 5:40
Success Shrestha
1408
Lets say i have table 1 and table 2. Table 2 is the updated version of table one. It can have same or updated structure, columns and data. I want to compare those two tables.
oracle unit-testing plsql utplsql
oracle unit-testing plsql utplsql
edited Nov 21 at 16:33
APC
118k15116229
asked Nov 21 at 5:40
Success Shrestha
1408
edited Nov 21 at 16:33
APC
118k15116229
asked Nov 21 at 5:40
Success Shrestha
1408
edited Nov 21 at 16:33
APC
118k15116229
edited Nov 21 at 16:33
APC
118k15116229
edited Nov 21 at 16:33
APC
118k15116229
118k15116229
asked Nov 21 at 5:40
Success Shrestha
1408
asked Nov 21 at 5:40
Success Shrestha
1408
asked Nov 21 at 5:40
Success Shrestha
1408
1408
Are you really using the UTPLSQL framework? Some of your previous questions suggest you were hand-rolling your unit tests. If you are using UTPLSQL please clarify whether v2 or v3 - they have completely different APIs.
– APC
Nov 21 at 12:55
Yes, i'm using utPLSQL v3. I have already found the solution. Thank you.
– Success Shrestha
Nov 21 at 15:21
1
If you have found a solution then please post it as an answer, It may help other Seekers in the future.
– APC
Nov 21 at 16:33
add a comment |
Are you really using the UTPLSQL framework? Some of your previous questions suggest you were hand-rolling your unit tests. If you are using UTPLSQL please clarify whether v2 or v3 - they have completely different APIs.
– APC
Nov 21 at 12:55
Yes, i'm using utPLSQL v3. I have already found the solution. Thank you.
– Success Shrestha
Nov 21 at 15:21
1
If you have found a solution then please post it as an answer, It may help other Seekers in the future.
– APC
Nov 21 at 16:33
Are you really using the UTPLSQL framework? Some of your previous questions suggest you were hand-rolling your unit tests. If you are using UTPLSQL please clarify whether v2 or v3 - they have completely different APIs.
– APC
Nov 21 at 12:55
Are you really using the UTPLSQL framework? Some of your previous questions suggest you were hand-rolling your unit tests. If you are using UTPLSQL please clarify whether v2 or v3 - they have completely different APIs.
– APC
Nov 21 at 12:55
Yes, i'm using utPLSQL v3. I have already found the solution. Thank you.
– Success Shrestha
Nov 21 at 15:21
Yes, i'm using utPLSQL v3. I have already found the solution. Thank you.
– Success Shrestha
Nov 21 at 15:21
1
1
If you have found a solution then please post it as an answer, It may help other Seekers in the future.
– APC
Nov 21 at 16:33
If you have found a solution then please post it as an answer, It may help other Seekers in the future.
– APC
Nov 21 at 16:33
add a comment |
3 Answers
3
active
oldest
votes
If you want to compare all columns in 2 tables then try below query
`select * from table1 t1,table2 t2 where t1.id = t2.id`
But if you want compare some specified columns then try below query
`select t1.column,t2.column from table1 t1,table2 t2 where t1.id = t2.id
group by t1.column,t2.column`
It should be work for your requirement.
answered Nov 21 at 6:48
user3678149
1696
add a comment |
My requirement was to compare columns, data and constraints of two tables using utPLSQL.
i met my requirement by using native refcursors.
For data comparison:
OPEN p_store FOR SELECT * FROM customers@dblink2;
OPEN p_store2 FOR SELECT * FROM customers2@dblink2;
ut.expect(p_store).to_equal(p_store2);
For column comparison:
OPEN p_store FOR
SELECT
A.COLUMN_NAME,
A.DATA_TYPE,
A.DATA_LENGTH
FROM
(SELECT * FROM USER_TAB_COLUMNS@dblink2 WHERE TABLE_NAME = 'CUSTOMERS') A;
OPEN p_store2 FOR
SELECT
B.COLUMN_NAME,
B.DATA_TYPE,
B.DATA_LENGTH
FROM
(SELECT * FROM user_tab_columns@dblink2 WHERE table_name = 'CUSTOMERS') B;
ut.expect(p_store).to_equal(p_store2);
I using utPLSQL V3. If you're using v2 then utassert.eqtable can be used.
answered Nov 22 at 4:28
Success Shrestha
1408
add a comment |
=> Sample Query to compare the structure of tables (add columns like datatype and other comparison parameters as you need ) here using all_tab_cols:
SELECT
t1.table_name
,t2.table_name
,t1.column_name
,t2.column_name
FROM
(
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP1'
) t1
FULL OUTER JOIN (
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP2'
) t2 ON t1.owner = t2.owner
AND t1.column_name = t2.column_name;
To Compare data :
You can Use a similar query with join different types of join to compare like left and right join.
SELECT
*
FROM
temp1 t1
FULL JOIN temp2 t2 ON t1.id = t2.id;
You can Use Set operations like union ,unionall and intersect to compare further use distinct.
answered Nov 21 at 6:11
Bhanu Yadav
1295
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405858%2futplsql-how-can-i-compare-two-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you want to compare all columns in 2 tables then try below query
`select * from table1 t1,table2 t2 where t1.id = t2.id`
But if you want compare some specified columns then try below query
`select t1.column,t2.column from table1 t1,table2 t2 where t1.id = t2.id
group by t1.column,t2.column`
It should be work for your requirement.
answered Nov 21 at 6:48
user3678149
1696
add a comment |
If you want to compare all columns in 2 tables then try below query
`select * from table1 t1,table2 t2 where t1.id = t2.id`
But if you want compare some specified columns then try below query
`select t1.column,t2.column from table1 t1,table2 t2 where t1.id = t2.id
group by t1.column,t2.column`
It should be work for your requirement.
answered Nov 21 at 6:48
user3678149
1696
add a comment |
If you want to compare all columns in 2 tables then try below query
`select * from table1 t1,table2 t2 where t1.id = t2.id`
But if you want compare some specified columns then try below query
`select t1.column,t2.column from table1 t1,table2 t2 where t1.id = t2.id
group by t1.column,t2.column`
It should be work for your requirement.
answered Nov 21 at 6:48
user3678149
1696
If you want to compare all columns in 2 tables then try below query
`select * from table1 t1,table2 t2 where t1.id = t2.id`
But if you want compare some specified columns then try below query
`select t1.column,t2.column from table1 t1,table2 t2 where t1.id = t2.id
group by t1.column,t2.column`
It should be work for your requirement.
answered Nov 21 at 6:48
user3678149
1696
answered Nov 21 at 6:48
user3678149
1696
answered Nov 21 at 6:48
user3678149
1696
answered Nov 21 at 6:48
user3678149
1696
1696
add a comment |
add a comment |
My requirement was to compare columns, data and constraints of two tables using utPLSQL.
i met my requirement by using native refcursors.
For data comparison:
OPEN p_store FOR SELECT * FROM customers@dblink2;
OPEN p_store2 FOR SELECT * FROM customers2@dblink2;
ut.expect(p_store).to_equal(p_store2);
For column comparison:
OPEN p_store FOR
SELECT
A.COLUMN_NAME,
A.DATA_TYPE,
A.DATA_LENGTH
FROM
(SELECT * FROM USER_TAB_COLUMNS@dblink2 WHERE TABLE_NAME = 'CUSTOMERS') A;
OPEN p_store2 FOR
SELECT
B.COLUMN_NAME,
B.DATA_TYPE,
B.DATA_LENGTH
FROM
(SELECT * FROM user_tab_columns@dblink2 WHERE table_name = 'CUSTOMERS') B;
ut.expect(p_store).to_equal(p_store2);
I using utPLSQL V3. If you're using v2 then utassert.eqtable can be used.
answered Nov 22 at 4:28
Success Shrestha
1408
add a comment |
My requirement was to compare columns, data and constraints of two tables using utPLSQL.
i met my requirement by using native refcursors.
For data comparison:
OPEN p_store FOR SELECT * FROM customers@dblink2;
OPEN p_store2 FOR SELECT * FROM customers2@dblink2;
ut.expect(p_store).to_equal(p_store2);
For column comparison:
OPEN p_store FOR
SELECT
A.COLUMN_NAME,
A.DATA_TYPE,
A.DATA_LENGTH
FROM
(SELECT * FROM USER_TAB_COLUMNS@dblink2 WHERE TABLE_NAME = 'CUSTOMERS') A;
OPEN p_store2 FOR
SELECT
B.COLUMN_NAME,
B.DATA_TYPE,
B.DATA_LENGTH
FROM
(SELECT * FROM user_tab_columns@dblink2 WHERE table_name = 'CUSTOMERS') B;
ut.expect(p_store).to_equal(p_store2);
I using utPLSQL V3. If you're using v2 then utassert.eqtable can be used.
answered Nov 22 at 4:28
Success Shrestha
1408
add a comment |
My requirement was to compare columns, data and constraints of two tables using utPLSQL.
i met my requirement by using native refcursors.
For data comparison:
OPEN p_store FOR SELECT * FROM customers@dblink2;
OPEN p_store2 FOR SELECT * FROM customers2@dblink2;
ut.expect(p_store).to_equal(p_store2);
For column comparison:
OPEN p_store FOR
SELECT
A.COLUMN_NAME,
A.DATA_TYPE,
A.DATA_LENGTH
FROM
(SELECT * FROM USER_TAB_COLUMNS@dblink2 WHERE TABLE_NAME = 'CUSTOMERS') A;
OPEN p_store2 FOR
SELECT
B.COLUMN_NAME,
B.DATA_TYPE,
B.DATA_LENGTH
FROM
(SELECT * FROM user_tab_columns@dblink2 WHERE table_name = 'CUSTOMERS') B;
ut.expect(p_store).to_equal(p_store2);
I using utPLSQL V3. If you're using v2 then utassert.eqtable can be used.
answered Nov 22 at 4:28
Success Shrestha
1408
My requirement was to compare columns, data and constraints of two tables using utPLSQL.
i met my requirement by using native refcursors.
For data comparison:
OPEN p_store FOR SELECT * FROM customers@dblink2;
OPEN p_store2 FOR SELECT * FROM customers2@dblink2;
ut.expect(p_store).to_equal(p_store2);
For column comparison:
OPEN p_store FOR
SELECT
A.COLUMN_NAME,
A.DATA_TYPE,
A.DATA_LENGTH
FROM
(SELECT * FROM USER_TAB_COLUMNS@dblink2 WHERE TABLE_NAME = 'CUSTOMERS') A;
OPEN p_store2 FOR
SELECT
B.COLUMN_NAME,
B.DATA_TYPE,
B.DATA_LENGTH
FROM
(SELECT * FROM user_tab_columns@dblink2 WHERE table_name = 'CUSTOMERS') B;
ut.expect(p_store).to_equal(p_store2);
I using utPLSQL V3. If you're using v2 then utassert.eqtable can be used.
answered Nov 22 at 4:28
Success Shrestha
1408
answered Nov 22 at 4:28
Success Shrestha
1408
answered Nov 22 at 4:28
Success Shrestha
1408
answered Nov 22 at 4:28
Success Shrestha
1408
1408
add a comment |
add a comment |
=> Sample Query to compare the structure of tables (add columns like datatype and other comparison parameters as you need ) here using all_tab_cols:
SELECT
t1.table_name
,t2.table_name
,t1.column_name
,t2.column_name
FROM
(
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP1'
) t1
FULL OUTER JOIN (
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP2'
) t2 ON t1.owner = t2.owner
AND t1.column_name = t2.column_name;
To Compare data :
You can Use a similar query with join different types of join to compare like left and right join.
SELECT
*
FROM
temp1 t1
FULL JOIN temp2 t2 ON t1.id = t2.id;
You can Use Set operations like union ,unionall and intersect to compare further use distinct.
answered Nov 21 at 6:11
Bhanu Yadav
1295
add a comment |
=> Sample Query to compare the structure of tables (add columns like datatype and other comparison parameters as you need ) here using all_tab_cols:
SELECT
t1.table_name
,t2.table_name
,t1.column_name
,t2.column_name
FROM
(
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP1'
) t1
FULL OUTER JOIN (
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP2'
) t2 ON t1.owner = t2.owner
AND t1.column_name = t2.column_name;
To Compare data :
You can Use a similar query with join different types of join to compare like left and right join.
SELECT
*
FROM
temp1 t1
FULL JOIN temp2 t2 ON t1.id = t2.id;
You can Use Set operations like union ,unionall and intersect to compare further use distinct.
answered Nov 21 at 6:11
Bhanu Yadav
1295
add a comment |
=> Sample Query to compare the structure of tables (add columns like datatype and other comparison parameters as you need ) here using all_tab_cols:
SELECT
t1.table_name
,t2.table_name
,t1.column_name
,t2.column_name
FROM
(
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP1'
) t1
FULL OUTER JOIN (
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP2'
) t2 ON t1.owner = t2.owner
AND t1.column_name = t2.column_name;
To Compare data :
You can Use a similar query with join different types of join to compare like left and right join.
SELECT
*
FROM
temp1 t1
FULL JOIN temp2 t2 ON t1.id = t2.id;
You can Use Set operations like union ,unionall and intersect to compare further use distinct.
answered Nov 21 at 6:11
Bhanu Yadav
1295
=> Sample Query to compare the structure of tables (add columns like datatype and other comparison parameters as you need ) here using all_tab_cols:
SELECT
t1.table_name
,t2.table_name
,t1.column_name
,t2.column_name
FROM
(
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP1'
) t1
FULL OUTER JOIN (
SELECT
*
FROM
all_tab_cols
WHERE
table_name = 'TEMP2'
) t2 ON t1.owner = t2.owner
AND t1.column_name = t2.column_name;
To Compare data :
You can Use a similar query with join different types of join to compare like left and right join.
SELECT
*
FROM
temp1 t1
FULL JOIN temp2 t2 ON t1.id = t2.id;
You can Use Set operations like union ,unionall and intersect to compare further use distinct.
answered Nov 21 at 6:11
Bhanu Yadav
1295
answered Nov 21 at 6:11
Bhanu Yadav
1295
answered Nov 21 at 6:11
Bhanu Yadav
1295
answered Nov 21 at 6:11
Bhanu Yadav
1295
1295
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405858%2futplsql-how-can-i-compare-two-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are you really using the UTPLSQL framework? Some of your previous questions suggest you were hand-rolling your unit tests. If you are using UTPLSQL please clarify whether v2 or v3 - they have completely different APIs.
– APC
Nov 21 at 12:55
Yes, i'm using utPLSQL v3. I have already found the solution. Thank you.
– Success Shrestha
Nov 21 at 15:21
1
If you have found a solution then please post it as an answer, It may help other Seekers in the future.
– APC
Nov 21 at 16:33