Jump to content


Photo
- - - - -

Oracle and BLINK


6 replies to this topic

#1 Marco.Bega

Marco.Bega

    Advanced

  • Members
  • PipPipPip
  • 77 posts
  • Gender:Male
  • Location:Milano, Italy

Posted 23 March 2006 - 02:10 PM

Hi,
I have two differents Oracle DB ("CM1" and "CM2").
My proiv 5.5 on Aix to connect on oracle DB "CM1".

I created a oracle DBLINK ( name is "DBL12" to connect from "CM1" to "CM2")

If i create a file definition ORDER with alternate "ORDER@DBL12"
I created a browser function in pro-iv and i don't see anything.
But if i use a sql statment in default logic i view all record:
example
1)
AZIENDA = '01'
SEL-ONLY(AZIENDA)
(I'dont see any record ??????? why????????)

2)
AZIENDA = '01'
SQL
SELECT FROM ORDER@DBL12 WHERE AZIENDA = '01'
ENDSQL
(I see all the record!! it's ok)

The oracle users have all grants in same environment.


thanks in advance
Marco

#2 mmeneses

mmeneses

    Member

  • Members
  • PipPip
  • 43 posts
  • Gender:Male

Posted 23 March 2006 - 03:10 PM

Marco,

It took me a lot of time to figure this out in the past and I want to share this one to you... Always use a SQL statement if you are trying to query a data view or a database that has a datalink.



-Mike

#3 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 23 March 2006 - 04:28 PM

If i create a file definition ORDER with alternate "ORDER@DBL12"


I created a browser function in pro-iv and i don't see anything.

As you found out, Pro-IV Alternate filedefs do not handle dblinks. You
could either

1. Define a view in your primary db which uses the dblink and point your P4
filedef at the view.

2. Define the secondary db as a Logical Database in your pro4.ini and use that in the P4 alternate
filename (I.E. %LOGICALDBNAME%TABLENAME )
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#4 Marco.Bega

Marco.Bega

    Advanced

  • Members
  • PipPipPip
  • 77 posts
  • Gender:Male
  • Location:Milano, Italy

Posted 23 March 2006 - 06:10 PM

Yes,
the first with views it's done but i don't want create 30 o more views for many db,
the best solution is the ALIAS command because i can create it dynamically.
Which is the sintax for the second solution, i remember you that i'm using AIX not windows.

Thanks
Marco

#5 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 24 March 2006 - 11:44 AM

Yes,
the first with views it's done but i don't want create 30 o more views for many db,
the best solution is the ALIAS command because i can create it dynamically.
Which is the sintax for the second solution, i remember you that i'm using AIX not windows.

Thanks
Marco

It's much like the alternate filename syntax
for aliasing to a file in a logical database:


ALIAS("P4FILENM","%DBNAME%FILENAME")

In pro4.ini you need for example:

[DATABASE - DBNAME]
FILETYPE=ORACLE
CONNECTION=ORA_USER/ORA_PASSWD

Edited by Chris Mackenzie, 24 March 2006 - 11:49 AM.

The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#6 Guest_Oracle User_*

Guest_Oracle User_*
  • Guests

Posted 24 March 2006 - 12:06 PM

I'm kind of surprised by the above.

Anyone know the following:
Why would ProIV do anything different just because the tablename involved a dblink?
If you define a DB view ProIV doesn't even know a dblink is involved right?
Why can you not just create a (public) synonym for the table-with-dblink instead of a view?

#7 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 24 March 2006 - 01:40 PM

Ah, good point - you don't even need the view.
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users