This HTML5 document contains 51 embedded RDF statements represented using HTML+Microdata notation.

The embedded RDF content will be recognized by any processor of HTML5 Microdata.

Subject Item

n17:this

foaf:made

n2:VirtOracleLinkingObjects

Subject Item

n32:this

sioc:creator_of

n2:VirtOracleLinkingObjects

Subject Item

n13:item

n12:services_of

n2:VirtOracleLinkingObjects

Subject Item

n28:this

sioc:creator_of

n2:VirtOracleLinkingObjects

Subject Item

n5:VOS

sioc:container_of

n2:VirtOracleLinkingObjects

atom:entry

n2:VirtOracleLinkingObjects

atom:contains

n2:VirtOracleLinkingObjects

Subject Item

n2:VirtOracleLinkingObjects

rdf:type

atom:Entry sioct:Comment

dcterms:created

2017-06-13T05:43:33.827461

dcterms:modified

2017-06-29T07:39:16.525747

rdfs:label

VirtOracleLinkingObjects

foaf:maker

n11:this n17:this

dc:title

VirtOracleLinkingObjects

opl:isDescribedUsing

n4:rdf

sioc:has_creator

n28:this n32:this

sioc:attachment

n14:png n15:png n16:png n18:png n19:png n20:png n21:png n22:png n23:png n25:png n26:png n27:png n29:png n30:png n31:png

sioc:content

%VOSWARNING% %META:TOPICPARENT{name="VirtLinkRemoteTables"}% ---+ Linking Oracle Database Objects into Virtuoso ---++ Prerequisites * <b>Virtuoso Universal Server, Commercial Edition</b>%BR% Virtuoso Universal Server Release 6.x or later is recommended, optimally the latest version available.%BR%%BR% * <b>ODBC Driver for Oracle</b>%BR% The Virtuoso Server uses an ODBC Driver for Oracle to link in the Oracle Schema Objects. The <b><nop>OpenLink Multi-Tier ODBC Driver for Oracle</b> will be used in this document, and a functional ODBC Data Source Name (DSN) of <b><code>ora10ma-hr</code></b> will be assumed to exist on the machine hosting the Virtuoso Server. Any ODBC compliant driver for Oracle should work.%BR%%BR% * <b>Oracle DBMS</b>%BR% An Oracle DBMS hosting the required Schema Objects needs to be available. This may be on any host which is TCP/IP accessible from the Virtuoso Server host. In this document, the Oracle <b>Human Resources</b> database will be used to demonstrate the process.%BR%%BR% ---++ Steps 1 Load the HTTP-based Virtuoso User Interface in your browser. %BR%%BR%<a href="%ATTACHURLPATH%/VirtWebHome.png"><img src="%ATTACHURLPATH%/VirtWebHome.png" style="wikiautogen" width="600px" alt="" /></a>%BR%%BR% 1 Click the <b>Conductor</b> link in the left-side navbar, and log in as a user with DBA privileges, such as the default <b><code>dba</code></b> user. %BR%%BR%<a href="%ATTACHURLPATH%/VirtConductor.png"><img src="%ATTACHURLPATH%/VirtConductor.png" style="wikiautogen" width="600px" alt="VirtConductor.png" /></a>%BR%%BR% 1 Drill down to the <b>Databases</b> -> <b>External Data Source</b> -> <b>Data Sources</b> tab. %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra01.png"><img src="%ATTACHURLPATH%/VirtLinkOra01.png" style="wikiautogen" width="600px" alt="VirtLinkOra01.png" /></a>%BR%%BR% 1 Locate the DSN for the desired remote data source (we're using <b><code>ora10ma-hr</code></b> for this example). If not yet connected to the DSN, click <b>Connect</b>, and input your user credentials for the remote data source. %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra02.png"><img src="%ATTACHURLPATH%/VirtLinkOra02.png" style="wikiautogen" width="600px" alt="VirtLinkOra02.png" /></a>%BR%%BR% 1 Once connected to the DSN, click the associated <b>Link objects</b> to get a list of available objects (tables, views, stored procedures). %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra03.png"><img src="%ATTACHURLPATH%/VirtLinkOra03.png" style="wikiautogen" width="600px" alt="VirtLinkOra03.png" /></a>%BR%%BR% 1 Locate and select the desired objects. %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra04.png"><img src="%ATTACHURLPATH%/VirtLinkOra04.png" style="wikiautogen" width="600px" alt="VirtLinkOra04.png" /></a>%BR%%BR% * You can use the Catalog and Schema drop-down selectors and the table name test-entry box to construct a filter to shorten the visible list. * For this example, we'll choose the main tables from Oracle's sample <b>HR</b> schema -- <b><code>HR.COUNTRIES</code>, <code>HR.DEPARTMENTS</code>, <code>HR.EMPLOYEES</code>, <code>HR.JOBS</code>, <code>HR.JOB_HISTORY</code>, <code>HR.LOCATIONS</code>, and <code>HR.REGIONS</code></b>. * When all desired objects are selected, click the <b>Link...</b> button. 1 Virtuoso will interrogate the remote data source to learn about primary keys, and present you with a default linkage setup. %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra05.png"><img src="%ATTACHURLPATH%/VirtLinkOra05.png" style="wikiautogen" width="600px" alt="VirtLinkOra05.png" /></a>%BR%%BR% 1 You can adjust the Virtuoso catalog, schema/owner, and object identifiers to suit your goals. The <b>Set To All</b> button can be used to speed the process. In this example, we'll put all linked objects into the <b><code>Oracle</code></b> catalog, and the <b><code>HR</code></b> schema. We'll leave the object identifiers unchanged. %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra06.png"><img src="%ATTACHURLPATH%/VirtLinkOra06.png" style="wikiautogen" width="600px" alt="VirtLinkOra06.png" /></a>%BR%%BR% * Sometimes a primary key is not reported properly, or you want to use a different key, either simple or compound. Click <b>Edit</b> next to the primary key you want to change, and select the columns that Virtuoso should use for the purpose -- %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra07.png"><img src="%ATTACHURLPATH%/VirtLinkOra07.png" style="wikiautogen" width="600px" alt="VirtLinkOra07.png" /></a>%BR%%BR% 1 When all are set as desired, click the <b>Link</b> button to link the objects into Virtuoso. <i>Virtuoso may take some time to complete this step,</i> depending on network infrastructure, DBMS load, etc. 1 The Link process will complete by listing the linked objects in the <b>Databases</b> -> <b>External Data Source</b> -> <b>External Linked Objects</b> tab. %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra08.png"><img src="%ATTACHURLPATH%/VirtLinkOra08.png" style="wikiautogen" width="600px" alt="VirtLinkOra08.png" /></a>%BR%%BR% ---++ Confirmation and Testing ---+++ Working with External Linked Objects 1 Linked object are shown in the <b>Databases</b> -> <b>External Data Source</b> -> <b>External Linked Objects</b> tab. %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra08.png"><img src="%ATTACHURLPATH%/VirtLinkOra08.png" style="wikiautogen" width="600px" alt="VirtLinkOra08.png" /></a>%BR%%BR% 1 Clicking the hyperlinked <b>Local Name</b> of the <b>External Linked Objects</b> will load the Virtuoso <b>Interactive SQL</b> (iSQL) HTTP interface (in a new browser tab or window) with a default <b><code>SELECT *</code><b> SQL query. For instance, clicking on <b><code>Oracle.HR.COUNTRIES</code></b> brings -- %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra09.png"><img src="%ATTACHURLPATH%/VirtLinkOra09.png" style="wikiautogen" width="600px" alt="VirtLinkOra09.png" /></a>%BR%%BR% 1 Click the <b>Execute</b> button to run the query. Virtuoso will retrieve the results from the remote table. <i><b>Note:</b> By default, iSQL will "<b>Show no more than 100 rows </b>." You can change this value as desired.</i> %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra10.png"><img src="%ATTACHURLPATH%/VirtLinkOra10.png" style="wikiautogen" width="600px" alt="VirtLinkOra10.png" /></a>%BR%%BR% ---+++ Working with SQL Database Objects 1 Linked objects are also visible through the <b>Database</b> -> <b>SQL Database Object</b> tab, just as if they were local Virtuoso data. %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra11.png"><img src="%ATTACHURLPATH%/VirtLinkOra11.png" style="wikiautogen" width="600px" alt="VirtLinkOra11.png" /></a>%BR%%BR% 1 Simply drill down to the Catalog you set while linking -- %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra12.png"><img src="%ATTACHURLPATH%/VirtLinkOra12.png" style="wikiautogen" width="600px" alt="VirtLinkOra12.png" /></a>%BR%%BR% 1 As you might expect, clicking on any of the <b>Action</b> links will reveal the table definition, indexes, triggers, constraints, or privileges. For example, clicking <b>Definition</b> for <b><code>Oracle.HR.EMPLOYEES</code></b> -- %BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkOra13.png"><img src="%ATTACHURLPATH%/VirtLinkOra13.png" style="wikiautogen" width="600px" alt="VirtLinkOra13.png" /></a>%BR%%BR% ---++ Related * [[VOSSQL2RDF][Mapping SQL Data to Linked Data Views]] * [[VirtOracleEntityFrameworkUsage][Using Microsoft Entity Frameworks to Access Oracle Schema Objects with Virtuoso]] CategoryDocumentation CategoryVirtuoso CategoryDotNET CategoryEntityFrameworks CategoryWebSite

sioc:id

031aec00a67608dfe02e27fa68cf6840

sioc:link

n2:VirtOracleLinkingObjects

sioc:has_container

n5:VOS

n12:has_services

n13:item

atom:title

VirtOracleLinkingObjects

sioc:links_to

n2:CategoryDotNET n2:CategoryWebSite n2:CategoryEntityFrameworks n2:VOSSQL2RDF n2:CategoryVirtuoso n2:VirtOracleEntityFrameworkUsage n2:CategoryDocumentation

atom:source

n5:VOS

atom:author

n17:this

atom:published

2017-06-13T05:43:33Z

atom:updated

2017-06-29T07:39:16Z

sioc:topic

n5:VOS