Home » SQL & PL/SQL » SQL & PL/SQL » Connect SQL and Oracle, possible?
Connect SQL and Oracle, possible? [message #559860] Fri, 06 July 2012 21:07 Go to next message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
Hello!

I am a student and for a project we should connect two different databases. One is based on SQL and the other one is based on Oracle.

So please experienced users and programmers, I have completely no idea about this stuff and could you please answer me some questions.
  • Is it possible to connect both databases / to get connection between them?
  • On what do I have to take attention when I connect them or which problems should I expect?
  • Is it easy to get communication between those platforms or is a huge amount of programming necessary?
Many Thanks!

Peter
[EDITED by LF: fixed awkward [list] tags]

[Updated on: Sat, 07 July 2012 04:24] by Moderator

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #559861 is a reply to message #559860] Fri, 06 July 2012 22:14 Go to previous messageGo to next message
BlackSwan
Messages: 20106
Registered: January 2009
Senior Member
>One is based on SQL and the other one is based on Oracle.
SQL is a language that can inter-operate against some, many, most RDBMS.
so which vendor makes & support the non-Oracle DB?

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

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #559899 is a reply to message #559861] Sat, 07 July 2012 04:26 Go to previous messageGo to next message
Littlefoot
Messages: 16993
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Have a look at this Maarten's message; follow his advices up to point 8 (where Forms come onto the stage; you probably don't need that).

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560122 is a reply to message #559899] Tue, 10 July 2012 12:52 Go to previous messageGo to next message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
Heya!

Many thanks for your posts and help. I try to follow your forum rules.

I have now to ask the guys which made the database about software/vendor version...etc.

like I said, I have no clue about databases's.



But still in general, are the any main issues?


Many thanks

Peter

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560123 is a reply to message #560122] Tue, 10 July 2012 13:03 Go to previous messageGo to next message
BlackSwan
Messages: 20106
Registered: January 2009
Senior Member
>But still in general, are the any main issues?
not for me.

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560350 is a reply to message #560123] Thu, 12 July 2012 05:36 Go to previous messageGo to next message
dws1
Messages: 14
Registered: July 2012
Junior Member
> Is it possible to connect both databases / to get connection between them?

Assuming "SQL" means "Microsoft SQL Server", yes. Note that SQL is a language, not a database, so if you mean "Microsoft SQL Server" then you need to say that to avoid confusion. You can make the connection in two ways; (a) create a linked server in SQL Server that accesses an Oracle backend, or (b) you can use Oracle Heterogeneous Services and setup SQL Server as a backend database. Which you need depends on your business requirements; do you want to connect to SQL Server and use Oracle as a backend, or the other way round?

> On what do I have to take attention when I connect them or which problems should I expect?

It's only possible to answer this with circular reasoning. If you're doing stuff that has issues associated with it, then yes you will hit issues. If you're not then you won't. Best bet is to try stuff out on a test system then post when you get some behaviour you don't understand. We can't guess what you'll be doing.

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560462 is a reply to message #560350] Fri, 13 July 2012 04:08 Go to previous messageGo to next message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
Thanks for your answer.

The thing is that one database is a Microsoft SQL database. The second database is based on Oracle.

For my data I have to build my own database. Don't know what I should use...

Over a web application the user should be able to search for stuff inside those databases without any problem, also when you have different systems. So you can access each database for the relevant data.

Now I don't know how I can build my databased. I have a huge amount of data and I have to start also to input this data somewhere. Should I use MS Access to input the data in there... Then maybe I use MS SQL Server for the Access database...

Would you have any suggestions please, have no clue where I should start? But what I know is that I have to start to input the data somewhere....


Thanks

Peter

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560464 is a reply to message #560462] Fri, 13 July 2012 04:18 Go to previous messageGo to next message
dws1
Messages: 14
Registered: July 2012
Junior Member
OK, it sounds like you don't need database links or linked servers (the Oracle and SQL Server terminology for essentially the same thing), but for your application to be able to connect to any database. So you will need to implement a database abstraction layer and database-specific low level functions. There are some already in existence that might fit your needs - ODBC for instance.

As for what you should use for your own data, there's not much I can suggest. What do you know, and out of what you know, what has the features you require? If you just need somewhere to bung some data, you could use Notepad to bung it into a text file; you only really need an RDBMS if you need all those extra layers of functionality. Notepad has insert, delete, overwrite and search functionality, although not sorting; but Notepad++ can sort. If not Notepad(++), why not, and the answer to that will lead to what you should use.

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560576 is a reply to message #559860] Sun, 15 July 2012 01:00 Go to previous messageGo to next message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
Hello!

It is not only to bung some data. I want to have filter options, sort options and also a search function.

I want to be able to search within all databases, sort the data accordingly.

So with the main application web based I want to get access to the already existing database and in my own.


So would be then Access a good option, regarding sorting data, searching, comparing data...

Thanks

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560577 is a reply to message #560576] Sun, 15 July 2012 01:49 Go to previous messageGo to next message
Littlefoot
Messages: 16993
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If existing data is "static" (i.e. you won't update these records, insert new ones, delete them) and you'll insert new data into a brand new table (tables), and regarding the fact that you want to do all that stuff (sorting, searching, comparing, ...), MAYBE you should consider migrating all your data into a single database.

For example: you could use SQL Developer and migrate SQL Server's data into an Oracle database. Having it all under Oracle, things are much simpler: you could create a view as
create view all_my_data as
select id, name, date, whatever from my_oracle_table
union all
select id, name, date, whatever from migrated_sql_server_table
union all
select id, name, date, whatever from my_new_inserting_table

Your application would be based on that view; as you'd have everything in the same view, all operations you mentioned (searching, sorting) would be rather simple.

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560583 is a reply to message #560577] Sun, 15 July 2012 09:49 Go to previous messageGo to next message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
Heya Littlefood!


What I forgot to mention is that I want to update the information or to change some inputs. With the web based application you have only read/filter/sort/compare access.

So when some data should be changed or added, there should be a computer in a office with access to the database to input/change/copy/delete the data.

I want to have a good program with a GUI, where everyone can change/add/copy.... the data also without programming knowledge.

Therefore I thought maybe Access would be a good software to do all kind of this.

But what I don't know is if you can then use the software Access to build a database and to use a "smart" SQL program as an interface between the web-based application (read/compare/search/filter) and the Access database (write/read/change/delete....).

Thanks for feedback!

Peter

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560584 is a reply to message #560583] Sun, 15 July 2012 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 20106
Registered: January 2009
Senior Member
>With the web based application you have only read/filter/sort/compare access.
No, web based applications can do DML (INSERT, UPDATE & DELETE)

For example, consider this forum which is a web based application;
which allows you to post new data into the database with every post.

[Updated on: Sun, 15 July 2012 13:06]

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560600 is a reply to message #560584] Sun, 15 July 2012 22:25 Go to previous messageGo to next message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
Hello!

Yes I understand that you have here all the options.

But in my web based application you should have only limited access to the data. It means no write/delete options. Over web you should only be able to read the stuff, filter and sort.

Therefore there should be a local machine in town, where one specified user will always update the data in my own database.

Would it be now a good option to start to input the data into an MS Access database and then to use Oracle or SQL to link it to the webbased application?


Thanks.

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560603 is a reply to message #560600] Sun, 15 July 2012 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 20106
Registered: January 2009
Senior Member
>Would it be now a good option to start to input the data into an MS Access database and then to use Oracle or SQL to link it to the webbased application?

EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer
do you know how to implement any web based application?

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560604 is a reply to message #560603] Sun, 15 July 2012 22:41 Go to previous messageGo to next message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
Hi!

Like I said in the beginning. I have no idea. I did programming, C++, but that's it. Have no fu**ing idea how to start something like this.

ndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer

This would be the way I would have to build the interconnection?

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560605 is a reply to message #560604] Sun, 15 July 2012 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 20106
Registered: January 2009
Senior Member
>This would be the way I would have to build the interconnection?
most web based applications are 3-Tier as previously posted.

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560606 is a reply to message #560605] Sun, 15 July 2012 23:30 Go to previous messageGo to next message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
What means then 3-Tier?

So then I need this way: EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer

What would you suggest in terms of savin/inputing local data into a database?

Would be Access a good option or you know better software?

Thanks

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560612 is a reply to message #560606] Mon, 16 July 2012 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 16993
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Fisch123
What I forgot to mention is that I want to update the information or to change some inputs

Fisch123, a little bit later
But in my web based application you should have only limited access to the data. It means no write/delete options. Over web you should only be able to read the stuff, filter and sort.

Fisch123
there should be a local machine in town, where one specified user will always update the data in my own database.


I must admit: I don't understand what you really want. More important is: I don't think that YOU know what you really want.
  • There's that SQL Server database that contains some data. Do you want to have it read only or do you want to insert/update data?
  • There's another, Oracle database that also contains some data. Do you want to have it read only or do you want to insert/update data?
  • There's the third, "your own" MS Access database. It appears that you want to insert & update data in it. Is that correct?

Depending on the answers you provide to the above questions, someone might suggest an approach to the problem.

By the way, you might be interested in Oracle Application Express.

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560628 is a reply to message #560612] Mon, 16 July 2012 02:29 Go to previous messageGo to next message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
Hello!

Ok.

1st database: Oracle base: have only read access on this one.

2nd database: MS-SQL base: have only read access on this one.

3rd database: This should be my database, for my data (text, values, charts, maybe also pics), were I have read/write (full) access.


Webapplication: Only Read/Filter/Sort/Compare options. With the internetbrowers you have a application where you can search for data in all three of them.


The main question is now what I should use for my own, 3 drd database. Have to get the data , which is saved mainly in Excel sheets (values, text, charts, pics) , and then to safe it into a application which creates me the database...

Thanks for the information of Oracle Express. Will read through it...


Sorry for confusing so much...

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560630 is a reply to message #560628] Mon, 16 July 2012 02:41 Go to previous messageGo to next message
Littlefoot
Messages: 16993
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, the first two databases are read-only.

For the third one, my choice would be Oracle (most probably because I don't use any other database). You don't even need to buy one, 11g XE is free. Application Express is free as well, and - pay attention - it is great in converting your Excel sheets into a web application, so you don't even need to worry much about programming, data transfer, whatever - Apex does that for you.

So I'm back to my previous message - view created as union of all three of them.

Report message to a moderator

Re: Connect SQL and Oracle, possible? [message #560673 is a reply to message #559860] Mon, 16 July 2012 05:52 Go to previous message
Fisch123
Messages: 10
Registered: July 2012
Junior Member
So ok! Then very good, I will have now a look! Thanks for the info!

please give me some hints when you know something else...

Report message to a moderator

Previous Topic: create user
Next Topic: Table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 23 07:22:39 CDT 2013

Total time taken to generate the page: 0.14194 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.