I'm usually developing with SQL Server, but our organisation is in the
process of implementing an ERP system that is Oracle-based (10g).

I'm required to write a small bolt-on application (web based), so I was
intending to create a separate 'database', where I could store my
application's stored procedures/views/etc, so that it would be independant
from our ERP system.

I have access to the web-based Enterprise Manager and Oracle SQL Developer,
though I'm sure I could gain access to or install other tools as required.

My first mission, I guess, is to find out a bit about the fundamentals or
Oracle. I understand Oracle doesnt have DB as such but has Schemas, but
which are a slightly different concept. So I'd like to get my head around
some of these fundamental concepts.

Secondly (and more practically), I need to know how to create my separate
'database', and how I can refer to tables & views in the ERP system. In SQL
this is fairly easy to do - if my application wants to manipulate a table in
a different DB I simply use the following type of reference: Select * from
OtherDB.dbo.Table1. I assume there is an Oracle equivalent.

Can anyone point me firstly in the direction of some good informations sites
to tutorial sites where I can find out more? Can anyone give me a simple
summary of what I need to do to create this separate 'database' and how I
get it to interact but its ERP peer DB.

I realise that Oracle is significant technology that won't be mastered in a
day, but I need to start somewhere.

Thanks in advance

CJM wrote:
> Can anyone point me firstly in the direction of some good informations sites
> to tutorial sites where I can find out more?

> I realise that Oracle is significant technology that won't be mastered in a
> day, but I need to start somewhere.
>

Best place to start is with
http://www.oracle.com/pls/db102/homepage?remark=tahiti
and start with the documentation on "Getting Started". This will truly
give you all the information you require

> I'm required to write a small bolt-on application (web based), so I was
> intending to create a separate 'database', where I could store my
> application's stored procedures/views/etc, so that it would be independant
> from our ERP system.

After reading the "Getting Started" docs I would suggest you store your
application stored procedures/views/etc in a different schema in the
SAME database as the ERP system (if the vendor will allow it). This
will improve performance and is easier to design and make secure. You
should however have a test and development environment FAR away from
the production environment. It is normally quite easy to create a copy
of a database for these purposes. Again this is all in the "Getting
Started" section of the documentation.

CJM wrote:
> I'm usually developing with SQL Server, but our organisation is in the
> process of implementing an ERP system that is Oracle-based (10g).
>
> I'm required to write a small bolt-on application (web based), so I was
> intending to create a separate 'database', where I could store my
> application's stored procedures/views/etc, so that it would be independant
> from our ERP system.
>
> I have access to the web-based Enterprise Manager and Oracle SQL Developer,
> though I'm sure I could gain access to or install other tools as required.
>
> My first mission, I guess, is to find out a bit about the fundamentals or
> Oracle. I understand Oracle doesnt have DB as such but has Schemas, but
> which are a slightly different concept. So I'd like to get my head around
> some of these fundamental concepts.
>
> Secondly (and more practically), I need to know how to create my separate
> 'database', and how I can refer to tables & views in the ERP system. In SQL
> this is fairly easy to do - if my application wants to manipulate a table in
> a different DB I simply use the following type of reference: Select * from
> OtherDB.dbo.Table1. I assume there is an Oracle equivalent.
>
> Can anyone point me firstly in the direction of some good informations sites
> to tutorial sites where I can find out more? Can anyone give me a simple
> summary of what I need to do to create this separate 'database' and how I
> get it to interact but its ERP peer DB.
>
> I realise that Oracle is significant technology that won't be mastered in a
> day, but I need to start somewhere.
>
> Thanks in advance


Doc homepage is at

http://www.oracle.com/pls/db102/homepage?remark=tahiti

You'll need a (free and instant) OTN account to read it.
Note the 'Getting started' manual located on the first page.
The Concepts manual is also a mandatory read.

--
Sybrand Bakker
Senior Oracle DBA

Thanks Guys... I already have an OTN account so I'll jump right in.

Bernard, yes, I was planning on a separate schema within the same database -
I just wasn't sure of the terminology! lol. And I'm working on the
development version of the test system, on the failover cluster. The live
system is but a speck on the horizon!

Thanks

Hi,
I also used fox database, ACCESS, SQL Server, and Oracle for more than
7 years.
In fact , they are similar in some way (RDBMS). Oracle is more
powerful.
for example:

Select * from DB_Link.username.Table1;

CJM 寫道：

> I'm usually developing with SQL Server, but our organisation is in the
>>
> Secondly (and more practically), I need to know how to create my separate
> 'database', and how I can refer to tables & views in the ERP system. In SQL
> this is fairly easy to do - if my application wants to manipulate a tablein
> a different DB I simply use the following type of reference: Select * from
> OtherDB.dbo.Table1. I assume there is an Oracle equivalent.
>>

dbsrvc wrote:

> Select * from DB_Link.username.Table1;

Rather than
SELECT * FROM username.table@db_link?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

I've create a new Data File (SNEData), and a new User (SNE), which in turn
creates the Schema. [I did this in OEM]

There is another Schema called IFSAPP within the IFSP database. How do I
structure a simple SQL statement in SNE that queries IFSAPP [via SQL
Developer]?

Eg.

Select * from IFSAPP.MyTable;

I'm getting a 'SQL command not properly ended' error...

Do I need to create a Database Link; I assume I dont since the two Schema
are within the same DB.

Any ideas?

Thanks

Problem solved.

I gave the user SELECT ANY TABLE privileges and the used the following
command:

Select * from IFSAPP.MyTable;

CJM wrote:
> Problem solved.
>
> I gave the user SELECT ANY TABLE privileges and the used the following
> command:
>
> Select * from IFSAPP.MyTable;

You solved a problem by gutting security? Why? Does your employer
understand the implications of what you did? Do you?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"DA Morgan" <damorgan@psoug.org> wrote in message
news:1155848818.606543@bubbleator.drizzle.com...
> CJM wrote:
>> Problem solved.
>>
>> I gave the user SELECT ANY TABLE privileges and the used the following
>> command:
>>
>> Select * from IFSAPP.MyTable;
>
> You solved a problem by gutting security? Why? Does your employer
> understand the implications of what you did? Do you?
>

I solved the problem. I may or may not have created another potential
problem.

If my employer understood the implications of anything, I wouldn't be
attempting this at short notice with no preparation or training.

Do I? Of course not. Surely if I did understand the implications (and they
were negative) then I wouldnt have done it. That much *must* be obvious.

Rather than demonstrating how pompous you can be, why dont you start again
from the beginning. Your post may go something like this:

'Chris, I can see that you have got past your problem, but I dont think your
solution is ideal because of A, B & C. A better way to do it would be this
way...because it avoids the disadvantages A, B & C and has the advantages of
D, E & F.'

To why I would reply 'Oh, I see. Thanks. That makes sense.'

It's pretty easy. Why don't you try it?

Chris