6 Replies Latest reply on Nov 6, 2013 11:37 PM by user571093

    is it possible to reuse variables between sem_match queries?

    user571093

      Hi,

       

      I've seen examples of showing binding variables between sparql queries. I'm curious if it is possible to bind variables between sem_match queries. Please advice. Thanks a lot in advance.

       

      Hong

        • 1. Re: is it possible to reuse variables between sem_match queries?
          Matperry-Oracle

          Hi Hong,

           

          Can you please post an example of what you would like to do. That will help us answer your question.

           

          Thanks,
          Matt

          • 2. Re: is it possible to reuse variables between sem_match queries?
            user571093

            Hi Matt,

             

            Take the simple Sparql example from the developer guide:

              

            Query 1

            :

            SELECT ?x

            Where { ...<some complex query>...};

            The variable ?x can be bound in Query 2 so that it can be reused in query 2:

             

            Query 2

            :

            PREFIX ORACLE_SEM_FS_NS: <http://oracle.com/semtech#no_fall_back,s2s>

            PREFIX ORACLE_SEM_UEAP_NS: <http://oracle.com/semtech#x$RDFVID%20in(?,?,?)>

            PREFIX ORACLE_SEM_UEPJ_NS: <http://oracle.com/semtech#x$RDFVID>

            PREFIX ORACLE_SEM_UEBV_NS: <http://oracle.com/semtech#1,2,3>

             

            SELECT ?subject ?x

            Where { ?subject <urn:related> ?x.};

             

            I am wondering how it can be done in sem_match() queries? Any suggestions will be much appreciated. Thanks a lot.

             

            Hong

            • 3. Re: is it possible to reuse variables between sem_match queries?
              Matperry-Oracle

              Hong,

               

              In 12.1, we can use the session context and user-defined SPARQL functions to achieve bind variable functionality. The basic idea is that we create a user-defined function that returns a value from the session context. We can then use this function inside a SPARQL FILTER:

               

              exec MY_CTXT_PKG.set_attribute('name','<Fred>');

               

              select e, t

              from table(sem_match(

              'SELECT *

              WHERE

                { ?x <email>  ?e .

                  ?x rdf:type ?t

                  FILTER (sameTerm(?x,oraextf:myCtxFunc("name")))}'

              ,sem_models('m1')

              ,null,null,null,null

              ,' '));

               

              To get a new value for ?x, we can call MY_CTXT_PKG.set_attribute('name','<some_different_value>'). Then, if we re-run the same SEM_MATCH, we get the result for the new value.

               

              The script below illustrates how to do this.

               

              SQL> set lines 160 pages 10000
              SQL> set timing on;
              SQL> set echo on;
              SQL>
              SQL> conn / as sysdba;
              Connected.
              SQL>
              SQL> exec sem_apis.create_sem_network('tbs_3');
              
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:09.31
              SQL>
              SQL> grant connect,resource,unlimited tablespace,create any context to rdfuser identified by rdfuser;
              
              Grant succeeded.
              
              Elapsed: 00:00:00.80
              SQL>
              SQL> conn rdfuser/rdfuser;
              Connected.
              SQL>
              SQL> -- create context and associated pl/sql package
              SQL> create or replace package MY_CTXT_PKG as
                2    procedure set_attribute(name varchar2, value varchar2);
                3    function get_attribute(name varchar2) return varchar2;
                4  end MY_CTXT_PKG;
                5  /
              
              Package created.
              
              Elapsed: 00:00:00.03
              SQL> show errors;
              No errors.
              SQL>
              SQL> create or replace package body MY_CTXT_PKG as
                2 
                3    procedure set_attribute(
                4       name varchar2,
                5       value varchar2
                6    ) as
                7    begin
                8       dbms_session.set_context(namespace => 'MY_CTXT',
                9                    attribute => name,
              10                    value     => value
              11                   );
              12    end;
              13 
              14    function get_attribute(
              15       name varchar2
              16    ) return varchar2 as
              17    begin
              18       return sys_context('MY_CTXT', name);
              19    end;
              20 
              21  end MY_CTXT_PKG;
              22  /
              
              Package body created.
              
              Elapsed: 00:00:00.03
              SQL> show errors;
              No errors.
              SQL>
              SQL> CREATE OR REPLACE CONTEXT MY_CTXT using MY_CTXT_PKG;
              
              Context created.
              
              Elapsed: 00:00:00.01
              SQL> /
              
              Context created.
              
              Elapsed: 00:00:00.00
              SQL> show errors;
              No errors.
              SQL>
              SQL> -- create user-defined SPARQL function
              SQL> -- to read from context
              SQL> create or replace function myCtxFunc(
                2    params in MDSYS.SDO_RDF_TERM_LIST
                3  ) return MDSYS.SDO_RDF_TERM
                4  as
                5    name varchar2(4000);
                6    arg  MDSYS.SDO_RDF_TERM;
                7  begin
                8    arg := params(1);
                9    name := arg.value_name;
              10    return MDSYS.SDO_RDF_TERM(my_ctxt_pkg.get_attribute(name));
              11  end;
              12  /
              
              Function created.
              
              Elapsed: 00:00:00.02
              SQL> show errors;
              No errors.
              SQL>
              SQL> -- load some RDF data --
              SQL>
              SQL> create table atab (id int, tri sdo_rdf_triple_s);
              
              Table created.
              
              Elapsed: 00:00:00.29
              SQL> exec sem_apis.create_sem_model('m1','atab','tri');
              
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:02.86
              SQL>
              SQL> -- prepare the data in a heap table
              SQL> create table dtab(slex varchar2(4000),plex varchar2(4000),olex varchar2(4000));
              
              Table created.
              
              Elapsed: 00:00:00.02
              SQL>
              SQL> insert into dtab values ('<John>',   'rdf:type',        '<OracleHQEmployee>');
              
              1 row created.
              
              Elapsed: 00:00:00.00
              SQL> insert into dtab values ('<Fred>',   'rdf:type',        '<OracleHQEmployee>');
              
              1 row created.
              
              Elapsed: 00:00:00.00
              SQL> insert into dtab values ('<Sue>',      'rdf:type',        '<OracleHQEmployee>');
              
              1 row created.
              
              Elapsed: 00:00:00.00
              SQL> insert into dtab values ('<Sally>',  'rdf:type',        '<OracleHQEmployee>');
              
              1 row created.
              
              Elapsed: 00:00:00.00
              SQL> insert into dtab values ('<John>',   '<email>',        '"john@oracle.com"');
              
              1 row created.
              
              Elapsed: 00:00:00.00
              SQL> insert into dtab values ('<Fred>',   '<email>',        '"fred@oracle.com"');
              
              1 row created.
              
              Elapsed: 00:00:00.01
              SQL> insert into dtab values ('<Sue>',      '<email>',        '"sue@oracle.com"');
              
              1 row created.
              
              Elapsed: 00:00:00.00
              SQL> insert into dtab values ('<Sally>',  '<email>',        '"sally@oracle.com"');
              
              1 row created.
              
              Elapsed: 00:00:00.00
              SQL>
              SQL> commit;
              
              Commit complete.
              
              Elapsed: 00:00:00.00
              SQL>
              SQL> -- load the data into RDF store
              SQL> insert into atab select rownum,sdo_rdf_triple_s('m1',slex,plex,olex) from dtab;
              
              8 rows created.
              
              Elapsed: 00:00:00.51
              SQL> commit;
              
              Commit complete.
              
              Elapsed: 00:00:00.00
              SQL>
              SQL>
              SQL> -- Queries
              SQL> -- Find email and type for different employees
              SQL> column e format a30;
              SQL> column t format a30;
              SQL>
              SQL> -- Normal case: different query strings (each query must be parsed and compiled)
              SQL> select e, t
                2  from table(sem_match(
                3  'SELECT *
                4   WHERE
                5    { ?x <email>    ?e .
                6       ?x rdf:type ?t
                7       FILTER (sameTerm(?x,<Fred>))}'
                8  ,sem_models('m1')
                9  ,null,null,null,null
              10  ,' '));
              
              E                   T
              ------------------------------ ------------------------------
              fred@oracle.com            OracleHQEmployee
              
              Elapsed: 00:00:00.55
              SQL>
              SQL> select e, t
                2  from table(sem_match(
                3  'SELECT *
                4   WHERE
                5    { ?x <email> ?e .
                6       ?x rdf:type ?t
                7       FILTER (sameTerm(?x,<John>))}'
                8  ,sem_models('m1')
                9  ,null,null,null,null
              10  ,' '));
              
              E                   T
              ------------------------------ ------------------------------
              john@oracle.com            OracleHQEmployee
              
              Elapsed: 00:00:00.05
              SQL>
              SQL> select e, t
                2  from table(sem_match(
                3  'SELECT *
                4   WHERE
                5    { ?x <email> ?e .
                6       ?x rdf:type ?t
                7       FILTER (sameTerm(?x,<Sue>))}'
                8  ,sem_models('m1')
                9  ,null,null,null,null
              10  ,' '));
              
              E                   T
              ------------------------------ ------------------------------
              sue@oracle.com               OracleHQEmployee
              
              Elapsed: 00:00:00.06
              SQL>
              SQL> select e, t
                2  from table(sem_match(
                3  'SELECT *
                4   WHERE
                5    { ?x <email> ?e .
                6       ?x rdf:type ?t
                7       FILTER (sameTerm(?x,<Sally>))}'
                8  ,sem_models('m1')
                9  ,null,null,null,null
              10  ,' '));
              
              E                   T
              ------------------------------ ------------------------------
              sally@oracle.com           OracleHQEmployee
              
              Elapsed: 00:00:00.05
              SQL>
              SQL> -- Repeat with context function as bind variable
              SQL> -- (avoids parsing and compilation costs for subsequent queries)
              SQL> exec MY_CTXT_PKG.set_attribute('name','<Fred>');
              
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:00.01
              SQL>
              SQL> select e, t
                2  from table(sem_match(
                3  'SELECT *
                4   WHERE
                5    { ?x <email>    ?e .
                6       ?x rdf:type ?t
                7       FILTER (sameTerm(?x,oraextf:myCtxFunc("name")))}'
                8  ,sem_models('m1')
                9  ,null,null,null,null
              10  ,' '));
              
              E                   T
              ------------------------------ ------------------------------
              fred@oracle.com            OracleHQEmployee
              
              Elapsed: 00:00:00.12
              SQL>
              SQL> exec MY_CTXT_PKG.set_attribute('name','<John>');
              
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:00.00
              SQL>
              SQL> select e, t
                2  from table(sem_match(
                3  'SELECT *
                4   WHERE
                5    { ?x <email>    ?e .
                6       ?x rdf:type ?t
                7       FILTER (sameTerm(?x,oraextf:myCtxFunc("name")))}'
                8  ,sem_models('m1')
                9  ,null,null,null,null
              10  ,' '));
              
              E                   T
              ------------------------------ ------------------------------
              john@oracle.com            OracleHQEmployee
              
              Elapsed: 00:00:00.07
              SQL>
              SQL> exec MY_CTXT_PKG.set_attribute('name','<Sue>');
              
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:00.01
              SQL>
              SQL> select e, t
                2  from table(sem_match(
                3  'SELECT *
                4   WHERE
                5    { ?x <email>    ?e .
                6       ?x rdf:type ?t
                7       FILTER (sameTerm(?x,oraextf:myCtxFunc("name")))}'
                8  ,sem_models('m1')
                9  ,null,null,null,null
              10  ,' '));
              
              E                   T
              ------------------------------ ------------------------------
              sue@oracle.com               OracleHQEmployee
              
              Elapsed: 00:00:00.00
              SQL>
              SQL> exec MY_CTXT_PKG.set_attribute('name','<Sally>');
              
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:00.00
              SQL>
              SQL> select e, t
                2  from table(sem_match(
                3  'SELECT *
                4   WHERE
                5    { ?x <email>    ?e .
                6       ?x rdf:type ?t
                7       FILTER (sameTerm(?x,oraextf:myCtxFunc("name")))}'
                8  ,sem_models('m1')
                9  ,null,null,null,null
              10  ,' '));
              
              E                   T
              ------------------------------ ------------------------------
              sally@oracle.com           OracleHQEmployee
              
              Elapsed: 00:00:00.01
              SQL>
              SQL>
              SQL> -- Cleanup
              SQL> conn / as sysdba;
              Connected.
              SQL> exec sem_apis.drop_sem_network(cascade=>true);
              
              PL/SQL procedure successfully completed.
              
              Elapsed: 00:00:02.27
              SQL>
              SQL> drop user rdfuser cascade;
              
              User dropped.
              
              Elapsed: 00:00:01.44
              SQL>
              

               

              Hope this helps,

              - Matt

              1 person found this helpful
              • 4. Re: is it possible to reuse variables between sem_match queries?
                user571093

                Hi Matt,

                 

                That's very helpful. However, the value for the name variable must be explicitly specified in the session context, for example exec MY_CTXT_PKG.set_attribute('name','<Fred>'). So, it further raises another question: If we don't know the value of a variable in the first query like the example in the developer guide, how can we use the session context to bind variables in the second query? For example,

                Query 1:

                select x, t   from table(sem_match(

                'SELECT *  WHERE  {

                  ?x rdf:type ?t  } ORDER BY ASC (?x) LIMIT 2'  ,

                  sem_models('m1') , null,null,null,null  10  ,' ')); 

                 

                How to bind ?x in the session context so that it can pass the first two employees to Query 2?

                Query 2:

                select x, e  from table(sem_match(

                'SELECT *  WHERE  {

                  ?x <email> ?e  filter (how to use the function here to bind variable ?x occurred in query 1?) }' 

                  sem_models('m1') , null,null,null,null  10  ,' ')); 

                 

                Thank you very much.

                 

                Hong

                 

                • 5. Re: is it possible to reuse variables between sem_match queries?
                  Matperry-Oracle

                  Hong,

                   

                  For the scenario you describe, you would need to add some code to loop over the first query and then bind and execute the second query for each result. Similar Java code would be needed for Jena Adapter bind variables as well.

                   

                  SQL> set serverout on;
                  declare
                    type ctype is ref cursor;
                    c       ctype;
                    emp_uri varchar2(4000);
                    email   varchar2(4000);
                  begin
                  
                    dbms_output.put_line(chr(10));
                  
                    open c for
                      'select x
                       from table(sem_match(
                       ''SELECT *
                         WHERE
                         { ?x rdf:type <OracleHQEmployee> }''
                       ,sem_models(''m1'')
                       ,null,null,null,null
                       ,'' ''))';
                    loop
                      fetch c into emp_uri;
                      exit when c%notfound;
                  
                      MY_CTXT_PKG.set_attribute('name','<' || emp_uri || '>');
                  
                      execute immediate
                       'select e
                        from table(sem_match(
                        ''SELECT *
                          WHERE
                          { ?x <email>  ?e
                            FILTER (sameTerm(?x,oraextf:myCtxFunc("name")))}''
                        ,sem_models(''m1'')
                        ,null,null,null,null
                        ,'' ''))'
                      into email;
                  
                      dbms_output.put_line('email=['||email||']');
                  
                    end loop;
                    close c;
                  
                  end;
                  /
                  SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43 
                  
                  email=[sally@oracle.com]
                  email=[fred@oracle.com]
                  email=[john@oracle.com]
                  email=[sue@oracle.com]
                  
                  PL/SQL procedure successfully completed.
                  
                  Elapsed: 00:00:00.07
                  SQL>
                  

                   

                  To replicate the Jena Adapter example in the docs, you could use three different session values (name1, name2, name3) to bind in three values at once.

                   

                  SQL> exec MY_CTXT_PKG.set_attribute('name1','<Fred>');
                  
                  PL/SQL procedure successfully completed.
                  
                  Elapsed: 00:00:00.00
                  SQL> exec MY_CTXT_PKG.set_attribute('name2','<John>');
                  
                  PL/SQL procedure successfully completed.
                  
                  Elapsed: 00:00:00.01
                  SQL> exec MY_CTXT_PKG.set_attribute('name3','<Sue>');
                  
                  PL/SQL procedure successfully completed.
                  
                  Elapsed: 00:00:00.00
                  SQL>
                  SQL> select e, t
                    2  from table(sem_match(
                    3  'SELECT *
                    4   WHERE
                    5    { ?x <email>    ?e .
                    6       ?x rdf:type ?t
                    7       FILTER (sameTerm(?x,oraextf:myCtxFunc("name1")) ||
                    8           sameTerm(?x,oraextf:myCtxFunc("name2")) ||
                    9           sameTerm(?x,oraextf:myCtxFunc("name3")) )
                  10    }'
                  11  ,sem_models('m1')
                  12  ,null,null,null,null
                  13  ,' '));
                  
                  E                   T
                  ------------------------------ ------------------------------
                  sue@oracle.com               OracleHQEmployee
                  john@oracle.com            OracleHQEmployee
                  fred@oracle.com            OracleHQEmployee
                  
                  Elapsed: 00:00:00.14
                  

                   

                  Hope this helps,

                  Matt

                  • 6. Re: is it possible to reuse variables between sem_match queries?
                    user571093

                    Hi Matt,

                     

                    From your sample code and the sparql examples in the docs, I can see the value of the variable must be known firstly in order to pass the variable to next sparql or sem_match query. It seems that no straightforward method to bind variables between queries without knowing the value of the variable. Anyway, that already helped me lot. Thank you very much.

                     

                    Hong