Skip to Main Content
  • Questions

  • how to access data to and from foxpro and oracle simultaneously

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, karthik.

Asked: October 31, 2003 - 8:34 am UTC

Last updated: June 11, 2005 - 9:37 am UTC

Version: 9201

Viewed 1000+ times

You Asked

how to access data to and fro from foxpro,oracle,access,... simultaneously,
both in HTML-DB as-well in d2k(forms/reports).

please let me know which will be the feature development-tool.
HTML-DB, Jdeveloper, IDS, ... and why?

please also let me know th essential pkg's that a DBA/developer should Know like dbms_stat, dbms_pipe, dbms_aq,...

thanks in advance,
Karthik.R

and Tom said...


You can use generic connectivity to achieve that:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206 <code>

that'll let you use odbc to foxpro.

As for the essential packages -- no such list really. You need to be AWARE of them all, and use in depth the ones that make sense. DBMS_AQ for example -- do you have the need for message queuing and processing? if yes -> dbsm_aq is really important. if no -> dbms_aq is something that its nice to know exists but you don't need to know it in depth.

skim the entire supplied packages guide, realize they are there, and when a need arises say "ah hah, i remember reading something about that" and go back and get the details then.

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Problem with FoxPro logical fields in generic connectivity

Amid, March 15, 2004 - 3:16 am UTC

Sir,
When use generic connectivity from odbc to foxpro in Data Dictionary Translations not containing information about FoxPro logical fields, however this fields containing in Oracle ODBC Test answer as numeric field.
How i can have access to this fields without editing data dictionary tables.
thanks in advance

Tom Kyte
March 15, 2004 - 7:21 am UTC

please contact support for that one -- i don't really have any sort of access to foxpro (and the answer is most certainly NOT editing data dictionary tables!)

OK

Kumar, March 15, 2004 - 8:50 am UTC

Dear Tom,
Do you know the names of programming languages used by the
following relational databases?
1)Ingress
2)Informix
3)Sybase
4)Mysql
5)Borland dBASE,Paradox
6)ADABAS
Please let me know if you know.Any reader's input is appreciated


Tom Kyte
March 15, 2004 - 9:17 am UTC


they all are accessible by the programming language C.


so "C" is an answer to 1..6

OK

Kumar, March 15, 2004 - 10:28 am UTC

Dear Sir,
Not what you mean.I mean "Since we use PL/SQL for Oracle as
a procedural language,I wanted to know the procedural languages for the above listed RDBMSs."
Please do reply.

Tom Kyte
March 15, 2004 - 10:32 am UTC

2) spl or i4gl
3) transact sql (tsql)
5) dbase is in fact the language as is paradox
6) natural



using Hetrogenous conenctivity and AQ

Suhail, July 16, 2004 - 10:28 am UTC

Tom,

I would like to know your expert guidance on my requirement. Its related with the original thread ie "how to access data to and from foxpro and oracle simultaneously". I have to connect to Sybase database and maintain copy of some tables on the oracle side. There are soem applications they use these tables on Oracle and make changes/add data, when its chnaged on Oracle side I would like to propogate the changes on Sybase side, when these tables are chnaged on Oracle side then I would like to propagate the chnages on the Oracle side so that we can have the sam ecopy of these tables at a given point of time.

I have already setup the Hetrogeneous connectivity and we can retreive/update sybase tables from Oracle using dblink. How should we go for designing, I am thinking touse AQ, but donot know if AQ can be executed from Sybase side.

Whats your input?

Thanks

Tom Kyte
July 16, 2004 - 1:43 pm UTC

I cannot answer from the sybase side -- they have the equivalent of our heterogenous services -- but you would have to ask a sybase expert what would be the best practice to capture and then apply changes, they might recommend their replication server.

This link in 9i Stream

Suhail, July 16, 2004 - 4:04 pm UTC

Tom,

I was looking into this link </code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96571/repsingdemo.htm#54726 
for 9i  stream, it seems that using AQ/Stream I can propagate the message to Sybase side , here the sources are oracle database, in other example at
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96571/repmultdemo.htm <code>
its a multi source( all oracle) replication example, but it does not talk anything about if one of the source is Sybase then how to replicate the data. Does stream only work for Oracle sources?

Thanks

Tom Kyte
July 16, 2004 - 10:59 pm UTC

please see above.

yes, a sybase application can certainly also connect to Oracle and queue a message into streams -- absolutely.

but tell me-- how will you do that from a trigger in sybase? -- ahh, ask sybase.

really -- ask sybase for the best practices here, I know about sybase, i programmed sybase, I'm not the sybase expert however.

i dont know sybase

Suhail, July 17, 2004 - 4:44 pm UTC

Tom,

Although, I donot know much about Sybase but I know that Sybase are way behind in their Database technology. I donot think they have similar product like AQ and Stream. Guess what, its not easy to do the nextval in Sybase too, I thought they must have something similar to SELECT ses_name.nextval from dual, but one of the sybase programmer said that I cannot get next value of sequence directly using simple SELECT, I was surprised. Anyway, I will see if I am able to get the database changes somehow using Sybase trigger and use stream to propagate them to Oracle.

Thank you Tom.



A reader, July 21, 2004 - 1:44 pm UTC

Why not create a trigger in Oracle side and propagate that to Sybase.



Tom Kyte
July 21, 2004 - 6:34 pm UTC

they've got it going from Oracle to Sybase -- it is the other way they were looking at.

besides, you'd never want to make an Oracle database dependent on a Sybase databases availablity (ok, ok, cheap shot but it was an easy one :)

Posting to my original question

Suhail Ahmad, September 24, 2004 - 9:38 am UTC

Hello,

I was posting to my original question about integrating between Sybase and Oracle. I ws able to do it. Its simple, I just created Oracle view on every Sybase table in my schema and now these views are just like any other Oracle view. I can insert/update/delete or do anything with this table and same time these tables are being accessed on Sybase side. So it works Ok. I am also thinking that I can also use Oracle security on these Sybase tables now. Is this a good idea to use Oracle VPD on Sybase tables via views:)?

Suhail

Tom Kyte
September 24, 2004 - 11:25 am UTC

you'll only be limiting data the Oracle connected users can see.

go after the data via sybase and it'll be as open as it currently is.

Thats right

Suhail, September 24, 2004 - 12:12 pm UTC

Thats right, only from Oracle we can implement VPD ,there is no VPD on Sybase so no security on database. But hey, my users would be happy to see there own set of data only, and we can build new application on Sybase withought writing any security code via this indirect way.

Suhail


Heterogenous Connectivity

EDRISI, January 04, 2005 - 4:05 am UTC

Dear Tom,
The Answer about the above Problems was very good, but my problem Remain until (I Have Error Ora-02063 & Ora-28545). My Operations was same as another Reviwers (Mr. Charanjiv from India). Please assist me as soon as possible.

Thank You in Advance

E-mail : Edrisim@yahoo.com
RohamFarahani@yahoo.com

Tom Kyte
January 04, 2005 - 8:32 am UTC

not that it is really relevant to this thread, for we never discussed any errors, but:

[tkyte@localhost tkyte]$ oerr ora 28545
28545, 0000, "error diagnosed by Net8 when connecting to an agent"
// *Cause: An attempt to call an external procedure or to issue SQL
// to a non-Oracle system on a Heterogeneous Services database link
// failed at connection initialization. The error diagnosed
// by Net8 NCR software is reported separately.
// *Action: Refer to the Net8 NCRO error message. If this isn't clear,
// check connection administrative setup in tnsnames.ora
// and listener.ora for the service associated with the
// Heterogeneous Services database link being used, or with
// 'extproc_connection_data' for an external procedure call.
//


What's OERR?

G. Bill, June 11, 2005 - 4:01 am UTC

Hi Tom,

I need to know about this utility 'oerr' you have used to show errors no. details. I was unable to find it on my pc. I'm using Oracle9ir2 on windows2000/xp -- plz. don't get panic for WINDOWZ :)

Tom Kyte
June 11, 2005 - 9:37 am UTC 

windows does not have this utility.


ops$tkyte@ORA10G> exec dbms_output.put_line( sqlerrm( -1 ) );
ORA-00001: unique constraint (.) violated
 
PL/SQL procedure successfully completed.


will show you the error text, but not the cause/action.  for that, you'd peek at:
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96525/toc.htm
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library