1.10 Using the SEM_APIS.GET_SQL Function and SEM_SQL SQL Macro to Query RDF Data
You can use the SEM_APIS.GET_SQL function as an alternative to the SEM_MATCH table function to query RDF data.
It can be used by application developers to obtain the SQL translation for a SPARQL query. The resulting SQL translation can then be executed using SEM_SQL SQL Macro. The SEM_APIS.GET_SQL has exactly the same signature as SEM_APIS.SPARQL_TO_SQL function.
The following PL/SQL fragment is an example of using the SEM_APIS.GET_SQL function and SEM_SQL SQL Macro:
SQL> EXECUTE SEM_APIS.GET_SQL('SELECT ?s ?o { ?s <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> ?o }',
sem_models('m1'),null,null,null,' ',null,null,network_owner=>'RDFUSER',network_name=>'MYNET');
PL/SQL procedure successfully completed.
SQL> SELECT count(s), count(o) FROM SEM_SQL();
COUNT(S) COUNT(O)
-------------------- --------------------
3 3
1 row selected.
SQL> SELECT * FROM SEM_SQL() ORDER BY s,o;
S S$RDFVID
------------------------------ --------------------
S$_PREFIX S$_SUFFIX S$RDFVTYP
------------------------------ ------------------------------ ----------
S$RDFCLOB S$RDFLTYP S$RDFLANG
-------------------- ---------------------------------------- ----------
O O$RDFVID
------------------------------ --------------------
O$_PREFIX O$_SUFFIX O$RDFVTYP
------------------------------ ------------------------------ ----------
O$RDFCLOB O$RDFLTYP O$RDFLANG
-------------------- ---------------------------------------- ----------
SEM$ROWNUM
--------------------
John 4802682235912431956
John URI
OracleHQEmployee 9022701012979055032
OracleHQEmployee URI
1
Matt 5972784495178428863
Matt URI
OracleHQEmployee 9022701012979055032
OracleHQEmployee URI
1
Sue 8947116472173989398
Sue URI
OracleHQEmployee 9022701012979055032
OracleHQEmployee URI
1
3 rows selected.Application developers can utilize SEM_SQL SQL Macro to run any translated query
stored in some other tables using RDF$S2S_SQL$ table and SEM_APIS.SEM_SQL_COMPILE to compile the SQL in the table as shown in the following example. This will
save query translation time from SPARQL to SQL. Note that before using SEM_SQL for the first
time, you must execute SEM_APIS.CREATE_SEM_SQL.
SQL> CREATE TABLE sql_tab(id int, s2s_sql clob);
Table created.
SQL> DECLARE
2 sql_stmt CLOB;
3 BEGIN
4 sql_stmt := sem_apis.SPARQL_TO_SQL('SELECT ?s ?o { ?s <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> ?o }',
sem_models('m1'),null,null,null,' ',null,null,network_owner=>'RDFUSER2',network_name=>'MYNET');
5 EXECUTE IMMEDIATE 'INSERT INTO sql_tab VALUES (1, :1)' USING sql_stmt;
6
7 sql_stmt := SEM_APIS.SPARQL_TO_SQL('SELECT ?s ?p ?o { ?s ?p ?o }',
sem_models('m1'),null,null,null,null,null,null,network_owner=>'RDFUSER2',network_name=>'MYNET');
8 EXECUTE IMMEDIATE 'INSERT INTO sql_tab VALUES (2, :1)' USING sql_stmt;
9 EXECUTE IMMEDIATE 'commit';
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> truncate table RDF$S2S_SQL$;
Table truncated.
SQL> INSERT INTO RDF$S2S_SQL$ SELECT s2s_sql FROM sql_tab WHERE id=1;
1 row created.
SQL> EXEC SEM_APIS.SEM_SQL_COMPILE;
PL/SQL procedure successfully completed.
SQL> SELECT count(s), count(o) FROM sem_sql();
COUNT(S) COUNT(O)
-------------------- --------------------
3 3
1 row selected.
SQL> SELECT * FROM sem_sql() ORDER BY s,o;
S S$RDFVID
------------------------------ --------------------
S$_PREFIX S$_SUFFIX S$RDFVTYP
------------------------------ ------------------------------ ----------
S$RDFCLOB S$RDFLTYP S$RDFLANG
-------------------- ---------------------------------------- ----------
O O$RDFVID
------------------------------ --------------------
O$_PREFIX O$_SUFFIX O$RDFVTYP
------------------------------ ------------------------------ ----------
O$RDFCLOB O$RDFLTYP O$RDFLANG
-------------------- ---------------------------------------- ----------
SEM$ROWNUM
--------------------
John 4802682235912431956
John URI
OracleHQEmployee 9022701012979055032
OracleHQEmployee URI
1
Matt 5972784495178428863
Matt URI
OracleHQEmployee 9022701012979055032
OracleHQEmployee URI
1
Sue 8947116472173989398
Sue URI
OracleHQEmployee 9022701012979055032
OracleHQEmployee URI
1
3 rows selected.
SQL> TRUNCATE TABLE RDF$S2S_SQL$;
Table truncated.
SQL> INSERT INTO RDF$S2S_SQL$ SELECT s2s_sql FROM sql_tab where id=2;
1 row created.
SQL> EXEC sem_apis.sem_sql_compile;
PL/SQL procedure successfully completed.
SQL> SELECT count(*) from sem_sql();
COUNT(*)
--------------------
26
1 row selected.
SQL> SELECT * FROM sem_sql() ORDER BY s,p,o;
S S$RDFVID
------------------------------ --------------------
S$_PREFIX S$_SUFFIX S$RDFVTYP
------------------------------ ------------------------------ ----------
S$RDFCLOB S$RDFLTYP S$RDFLANG
-------------------- ---------------------------------------- ----------
P P$RDFVID
------------------------------ --------------------
P$_PREFIX P$_SUFFIX P$RDFVTYP
------------------------------ ------------------------------ ----------
P$RDFCLOB P$RDFLTYP P$RDFLANG
-------------------- ---------------------------------------- ----------
O O$RDFVID
------------------------------ --------------------
O$_PREFIX O$_SUFFIX O$RDFVTYP
------------------------------ ------------------------------ ----------
O$RDFCLOB O$RDFLTYP O$RDFLANG
-------------------- ---------------------------------------- ----------
SEM$ROWNUM
--------------------
John 4802682235912431956
John URI
age 7369467453923552448
age URI
35 9085530268529116130
35 LIT
http://www.w3.org/2001/XMLSchema#decimal
1
John 4802682235912431956
John URI
email 6480734238761529200
email URI
john2@oracle.com 5315621098565335765
john2@oracle.com LIT
1
John 4802682235912431956
John URI
foaf 2289371774016051690
foaf URI
Matt 5972784495178428863
Matt URI
1
John 4802682235912431956
John URI
http://www.w3.org/1999/02/22-r 834132227519661324
df-syntax-ns#type
http://www.w3.org/1999/02/22-r type URI
df-syntax-ns#
OracleHQEmployee 9022701012979055032
OracleHQEmployee URI
1
John 4802682235912431956
John URI
mbox 5760688889368728142
mbox URI
john@oracle.com 1322012223731379319
john@oracle.com LIT
1
John 4802682235912431956
John URI
name 6027014909707307188
name URI
John Doe 3287391926372438447
John Doe LIT
1
John 4802682235912431956
John URI
nick 4608123542649301902
nick URI
JD 8942401707893765892
JD LIT
1
Matt 5972784495178428863
Matt URI
age 7369467453923552448
age URI
40 1809238195348668799
40 LIT
http://www.w3.org/2001/XMLSchema#decimal
1
Matt 5972784495178428863
Matt URI
email 6480734238761529200
email URI
matt2@oracle.com 5816699135852471804
matt2@oracle.com LIT
1
Matt 5972784495178428863
Matt URI
foaf 2289371774016051690
foaf URI
Su 7425194847458329079
Su URI
1
Matt 5972784495178428863
Matt URI
http://www.w3.org/1999/02/22-r 834132227519661324
df-syntax-ns#type
http://www.w3.org/1999/02/22-r type URI
df-syntax-ns#
OracleHQEmployee 9022701012979055032
OracleHQEmployee URI
1
Matt 5972784495178428863
Matt URI
mbox 5760688889368728142
mbox URI
matt@oracle.com 1674614553190527316
matt@oracle.com LIT
1
Matt 5972784495178428863
Matt URI
name 6027014909707307188
name URI
Matt Adams 1025319037763704306
Matt Adams LIT
1
Matt 5972784495178428863
Matt URI
teleCommFrom 493206824495339087
teleCommFrom URI
teleCommLoc1 4570292005318753230
teleCommLoc1 URI
1
Sue 8947116472173989398
Sue URI
age 7369467453923552448
age URI
26 4033985797457567386
26 LIT
http://www.w3.org/2001/XMLSchema#decimal
1
Sue 8947116472173989398
Sue URI
email 6480734238761529200
email URI
sue2@oracle.com 5229415107273694944
sue2@oracle.com LIT
1
Sue 8947116472173989398
Sue URI
http://www.w3.org/1999/02/22-r 834132227519661324
df-syntax-ns#type
http://www.w3.org/1999/02/22-r type URI
df-syntax-ns#
OracleHQEmployee 9022701012979055032
OracleHQEmployee URI
1
Sue 8947116472173989398
Sue URI
mbox 5760688889368728142
mbox URI
sue@oracle.com 31820890332196705
sue@oracle.com LIT
1
Sue 8947116472173989398
Sue URI
nick 4608123542649301902
nick URI
Su 4914588660956121377
Su LIT
1
Sue 8947116472173989398
Sue URI
teleCommFrom 493206824495339087
teleCommFrom URI
teleCommLoc2 1084777556269608129
teleCommLoc2 URI
1
email 6480734238761529200
email URI
http://www.w3.org/2002/07/owl# 1982040897380465245
equivalentProperty
http://www.w3.org/2002/07/owl# equivalentProperty URI
mbox 5760688889368728142
mbox URI
1
teleCommLoc1 4570292005318753230
teleCommLoc1 URI
city 3506365445274213635
city URI
NYC 6275600577248419523
NYC URI
1
teleCommLoc1 4570292005318753230
teleCommLoc1 URI
state 4843125665925023053
state URI
NY 917887745150543696
NY URI
1
teleCommLoc1 4570292005318753230
teleCommLoc1 URI
zip 627678011281517369
zip URI
10101 9180109679895673868
10101 LIT
1
teleCommLoc2 1084777556269608129
teleCommLoc2 URI
state 4843125665925023053
state URI
NH 3642103339971966862
NH URI
1
teleCommLoc2 1084777556269608129
teleCommLoc2 URI
zip 627678011281517369
zip URI
03060 2914451030353375942
03060 LIT
1
26 rows selected.Parent topic: RDF Graph Overview