We help IT Professionals succeed at work.

Need help on Unix ,oracle and sql server

753 Views
Last Modified: 2014-08-18
I have requirement to get spool data from sql server and oracle into 2 seperate files from linux server. both db has one column account number to extact in both files

I can connect oracle server and spool data into file like sqlplus user/pass @spool.sql

to connect sql server sqlcmd which path or client software or pacakge needs to install on linux server or can i run that command on sql server from this server so no

need to install anything locally.

1.I need to know which sqlcmd(envinemnt setting in linux ) needs to use to connect sql server db and run given sql and get output in textfile without header/feedback.

2. need to comapre both data files and get different accountno file only for another sql server query

Below is the data from one file SQLSERVERFILE

ACCOUNTNO
1111
2222
3333
4444
5555
6666

AND below is the data from Oracle ORACLEFILE
ACCOUNTNO
1111
2222
3333
4444


So compaire above 2 files and create new files with missing records.

Output of NEWFILE

ACCOUNTNO
5555
6666

BUT I need output like - this is critical for me now.. how to keep accountno one by one
5555,6666

as this will be pass to another sql server query like where accountno in(5555,6666);

i also need to know if there is error then it will stop at taht process and give me someoutput saying it failed on which step.

create script with logfile to append
run sql server extracr cmd
check status for above run . good then go for next
run oracle sql extract cmd
check status
find different accountno and create file with ,
check status
logfile
Comment
Watch Question

Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
Couple of things immediately come to mind.

You could create a heterogeneous services connection to the SQL server from linux and access the data on the sql server directly using sqlplus - there are some limitations to the odbc generic gateway but as long as you're not using certain complex data types or performing distributed transactions, it should work.

There's an article on this site explaining how to do that: https://www.experts-exchange.com/Database/Oracle/A_9850-Configuring-and-using-Oracle-Database-Gateway-for-ODBC.html

Did a bit of hunting for a SQL Server command line client for Linux and came across this project:  https://www.npmjs.org/package/sql-cli

It appears to be a SQL Server command line client for Linux.  Never used it, can't tell you how reliable it is, just found it as a part of my search.

Author

Commented:
I can get the information from sqlcmd from others system how they connect but my main concern is below
i have 2 files with account numbe if so need o create dufferent acctnumber file with" ," and last acctnoshould not have ",". provided above reuired format.
thank you
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
If you were to create the ODBC gateway to SQL Server you could treat your SQL Server table as an oracle table and do this:

select accountno from SQLSERVERFILE@dblinkname
minus
select accountno from ORACLEFILE

That would give you your list of different values.

There are multiple options for converting rows to columns in this article at the Burlseon site: http://www.dba-oracle.com/t_converting_rows_columns.htm

Using the first example, this should work for example

select
   rtrim (xmlagg (xmlelement (e, accountno || ',')).extract ('//text()'), ',') accts
from
  (select accountno from SQLSERVERFILE@dblinkname minus select accountno from ORACLEFILE)

Open in new window


I tested it on Oracle to Oracle (not SQL Server to Oracle) and it worked.

I set up data like this:
create table v1 (c1 char(5));
create table v2 (c1 char(5));
insert into v1 values ('1111');
insert into v1 values ('2222');
insert into v1 values ('3333');
insert into v2 values ('1111');

SQL> select
  2     rtrim (xmlagg (xmlelement (e, c1 || ',')).extract ('//text()'), ',') accts
  3  from
  4    (select c1 from v1 minus select c1 from v2)
  5  /

ACCTS
--------------------------------------------------------------------------------
2222 ,3333

SQL>

Open in new window


Should be something to get you started down the right path, at least.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
The comm command should give you the 'differences' then a simple sed or awk command should be able to convert the output to a CSV.

I don't have access to *nix any more to provide a working example but there are several examples out there if you search around.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
I like Steve Wale's suggestion to set up a database link from your Oracle database to the SQL Server database, then do the queries and comparisons in Oracle.  That looks a lot simpler to me than trying to extract text files from these two different databases, then asking an O/S utility to compare the ASCII files.  Doing data comparisons like this is something that databases are very good at.

You may be able to use the database link to SQL Server to even have your Oracle database do the inserts into SQL Server directly (assuming that SQL Server allows this via an ODBC connection).  Or, if you want to have Oracle generate a text file of commands you can run in SQL Server, that would also be simple.
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
Using the generic gateway you can perform updates in Oracle only or SQL Server only (assuming the login you're using has the requisite permissions).

You can't perform a distributed transaction (updates in both) with the Generic gateway.

The separately licensed Gateway specific for SQL Server does allow distributed transactions though.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
I am assuming that you have everything in 2 files.  One called sql.dat (the SQL Server data) and one called ora.dat (the Oracle data).  There has certainly been enough posts here to get you that far.  I don't see the taking of the 2 files and generating the output you want.

To do that, I would do this:

sort ora.dat > ora.sor
sort sql.dat > sql.sor
diff ora.sor sql.sor | grep '> ' | awk '{ printf("%s,", $2) }' | sed 's/,$//' > ora_missing.dat
diff ora.sor sql.sor | grep '< ' | awk '{ printf("%s,", $2) }' | sed 's/,$//' > sql_missing.dat

Open in new window


That should give you 2 files.  ora_missing.dat (the records in sql.dat and not in ora.dat) and sql_missing.dat (the ones in ora.dat and not in sql.dat).  It should be a single line formatted with commas as you asked.

Author

Commented:
need some examples
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
What examples do you need?

My *nix is rusty but the sample by johnsone seems like it should work.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
What I posted does work for me.  What is that missing?

Author

Commented:
I need below output ..
ACCOUNTNO
 1111
 2222
 3333
 4444
 5555
 6666

 AND below is the data from Oracle ORACLEFILE
 ACCOUNTNO
 1111
 2222
 3333
 4444


 So compaire above 2 files and create new files with missing records.

 Output of NEWFILE

 ACCOUNTNO
 5555
 6666
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
Did you try the commands that johnsone suggested?  I'm not an expert on *nix coding (I prefer to do this kind of work in Oracle SQL) but it looks to me like the Linux commands that johnsone gave you should do the job in Linux, if that is where you want to do this comparison.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Did you run the commands that I gave?  They give the exact output that you are asking for.

Author

Commented:
yes,
now i am using below command
awk 'FNR==NR{a[$1]++;next}!a[$1]'  test1.txt  test2.txt

th above command is working.
pls suggest
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
If you have an answer to the question asked, please close this one.

If you have a new question on how to get your awk code to run, please ask this as a new question.

Author

Commented:
Yes I will close this question...Just let me know above awk command is right or wrong ?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>.Just let me know above awk command is right or wrong ?

If it provides what you need then it is probably right.
If not, then it isn't.
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.