Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 728
  • Last Modified:

Need help on Unix ,oracle and sql server

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
0
digs developer
Asked:
digs developer
  • 5
  • 4
  • 4
  • +2
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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: http://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.
0
 
digs developerAuthor 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
0
 
Steve WalesSenior Database AdministratorCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) 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.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
Steve WalesSenior Database AdministratorCommented:
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.
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
digs developerAuthor Commented:
need some examples
0
 
slightwv (䄆 Netminder) Commented:
What examples do you need?

My *nix is rusty but the sample by johnsone seems like it should work.
0
 
johnsoneSenior Oracle DBACommented:
What I posted does work for me.  What is that missing?
0
 
digs developerAuthor 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
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
johnsoneSenior Oracle DBACommented:
Did you run the commands that I gave?  They give the exact output that you are asking for.
0
 
digs developerAuthor 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
0
 
slightwv (䄆 Netminder) 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.
0
 
digs developerAuthor Commented:
Yes I will close this question...Just let me know above awk command is right or wrong ?
0
 
slightwv (䄆 Netminder) 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.
0
 
johnsoneSenior Oracle DBACommented:
Sorry, awk is not my strong suit.  I can do pretty simple stuff with it.  You would have to thoroughly test it.

It doesn't appear that it will give you a comma separated list as you requested in your original post.  And it won't tell you which file it came from.  To me that is important if you have to insert something back into a database, you need to know which of the 2 it came from.

If you don't care which file it came from and don't want the comma separated list, this this does this should work just fine.  More steps, but it is understandable.

sort test1.txt > test1.sor ; sort test2.txt > test2.sor ; diff test1.sor test2.sor | egrep -e '> |< ' | cut -c 3-

Open in new window

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now